[Solved] CSV not opening correctly

Discuss setup / installation issues - Add a spell checker, Language pack?
Post Reply
HazelGlass
Posts: 9
Joined: Sat Jan 30, 2021 12:55 am

[Solved] CSV not opening correctly

Post by HazelGlass »

Greetings.
I have exported my audience list from Mailchimp as a csv file. After much trial and error, it seems that when opening in Calc, instead of the standard 'comma' separator, I have to select 'comma', 'semicolon' and 'merge delimiters' with the 'text delimiter' set to an apostrophe. This is as close as I can get to it looking correct in the Fields preview. I scrolled down in the preview to make sure that all 465 subscribers were in there, and they were. It appeared like success at last.
But when I click 'OK' and the file actually opens, suddenly I only have 241 listed, because all the rest are jumbled up in other rows. So one row might have one contact, but then the next row might have 6-10 contacts all stacked. I did notice that when this is happening, before the next email begins there is a symbol like the old 'return' key. So I feel like that is supposed to be telling Calc to begin on the next row, but it's failing to do so.
Any idea how to correct this?
Cheers,
Hazel
Last edited by Hagar Delest on Sat Jan 30, 2021 10:57 am, edited 1 time in total.
Reason: tagged solved.
OpenOffice 4.1.3 Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: CSV not opening correctly

Post by FJCC »

My guess is that you have some unmatched text delimiters. That is, you have an apostrophe that marks the beginning a some text but not one to mark the end. Calc thinks the next line feed is still part of the text section, so it does not start a new record. Try opening the file in Writer (you might have to change the file extension to .txt to do that) or even in Notepad and inspect the misbehaving lines. You might also learn while doing that why you have to use the strange combinations of delimiters.
You are welcome to post the file here except I suspect it is full of confidential information.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
HazelGlass
Posts: 9
Joined: Sat Jan 30, 2021 12:55 am

Re: CSV not opening correctly

Post by HazelGlass »

Thank you, I will try that and see what I can figure out. I would indeed prefer to just share the file so that someone who knows what they're doing could decipher the issue, however you're correct that it's filled with emails and such, so I probably shouldn't do that ;)
OpenOffice 4.1.3 Windows 10
HazelGlass
Posts: 9
Joined: Sat Jan 30, 2021 12:55 am

Re: CSV not opening correctly

Post by HazelGlass »

Okay new problem... I can't seem to change the extension to .txt. I've tried renaming it, but then the extension is .txt.csv. So I can't open it in Writer or anything other than Calc.
OpenOffice 4.1.3 Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CSV not opening correctly

Post by Villeroy »

Make a copy of the file.
Open the copy with a plain text editor and remove everything exept the first few lines.
Overwrite confidential text with X or something. Leave numbers, dates, times unchanged.
Paste the remaining lines of plain text showing the structure without context here on the forum.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: CSV not opening correctly

Post by FJCC »

Windows is hiding the file extension because Microsoft thinks it will scare you or something. Open the folder containing the file. Near the top of the window there should be tabs for File, Home, Share, View. Click View. You should see a bunch of options for changing what is displayed in the folder. Select the one that says "File Name Extensions". You should then be able to make the change. Windows will bark at you about the filing becoming unusable. Ignore that.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
HazelGlass
Posts: 9
Joined: Sat Jan 30, 2021 12:55 am

Re: CSV not opening correctly

Post by HazelGlass »

Okay I tried to highlight some trouble spots and black out the sensitive info, but apparently that doesn't save in a .txt file :?
So here it is with a few trouble spots, but names and phone numbers removed.
Attachments
subscribed_segment_export_a6ade0341b.txt
(1.93 KiB) Downloaded 161 times
OpenOffice 4.1.3 Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: CSV not opening correctly

Post by robleyd »

That file, renamed as .csv, opens just fine for me using the defaults on the import filter. However, the data doesn't seem to match what the headers suggest should be there. For example, MEMBER_RATING and OPTIN_IP are date/time. Attached is the .ods version I created.

I'd be willing to look at the original exported file if you wish; if so, PM me for my email address.
Attachments
zzz.ods
(9.08 KiB) Downloaded 149 times
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
HazelGlass
Posts: 9
Joined: Sat Jan 30, 2021 12:55 am

Re: CSV not opening correctly

Post by HazelGlass »

Indeed there's no stacking in yours. Hmm.
Well, I've thrown in the towel and begun just reformatting the entire thing as a .txt doc, which I can then save as a CSV when it's done. I'm cutting out all the info I don't need, and making sure the header info matches the actual columns, because yes, as you said, they were incorrect. Half of that is junk I don't need anyway, happy to delete the clutter.
Thank you for all the help, it lead me down a path towards finally getting this sorted.
OpenOffice 4.1.3 Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: CSV not opening correctly

Post by robleyd »

You may be creating a world of pain for yourself :-)

A couple of tips; first is that you can scroll through the Fields display in the Import Filter to try and get an idea of where any errors are, or to see the effect of varying filter options. The second is to use the column header display to hide columns you don't want to import.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
HazelGlass
Posts: 9
Joined: Sat Jan 30, 2021 12:55 am

Re: CSV not opening correctly

Post by HazelGlass »

Yes, I did scroll through the Fields display and everything looked great. But when I opened the file it was all wrong. Anyway, I've given up solving the issue, and just devoted to clearing out the junk so that it functions. I've already wasted a couple days trying to figure this out, what's another few hours to get it right??
OpenOffice 4.1.3 Windows 10
Bill
Volunteer
Posts: 8932
Joined: Sat Nov 24, 2007 6:48 am

Re: CSV not opening correctly

Post by Bill »

HazelGlass wrote:I have to select 'comma', 'semicolon' and 'merge delimiters'
Your sample file has no semicolons. You should select only 'comma' as a separator. Also, do not select 'merge delimiters. Ticking "Merge delimiters" causes blank cells to be merged. Your data will not line up properly if there are blank cells.
HazelGlass wrote:... with the 'text delimiter' set to an apostrophe.
Your file uses double quotes as text delimiters. There are some apostrophes (single quotes?) in the file but they are actual text, not delimiters. Setting the apostrophe as a text delimiter means that you have unmatched text delimiters (as stated by FJCC). The unmatched text delimiters is what is causing some of your data to be "jumbled up in other rows".

On the Text Import dialog, the "Comma" setting is the only one that should be ticked. "Merge delimiters" should not be ticked and the Text delimiter should be set to "double quote" instead of 'apostrophe'.
AOO 4.1.14 on Ubuntu MATE 22.04
HazelGlass
Posts: 9
Joined: Sat Jan 30, 2021 12:55 am

Re: CSV not opening correctly

Post by HazelGlass »

That all makes sense, I'm just telling you that that was the only way I could get it to look right in the preview. Ticking only comma was where I started and it was a mess. The double quote was also where I started. I only deviated because it wasn't working. Anyway, I've reformatted the entire thing now, so no more issues! Yay!
OpenOffice 4.1.3 Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] CSV not opening correctly

Post by Villeroy »

These are the options that work for me.
The encoding is not important as long as text looks right in the preview.
Language is any flavour of English because of the decimal points.
In order to get the time stamps "2020-12-28 16:07:34" imported as correct numeric values, you have to untick "quoted fields as text" but tick "special numbers".
Attachments
t_104343_csv-options.png
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
HazelGlass
Posts: 9
Joined: Sat Jan 30, 2021 12:55 am

Re: [Solved] CSV not opening correctly

Post by HazelGlass »

Ah! That must be why when I tried opening it, it did not look right in the preview, which is why I began trying other options. It doesn't matter now, I've reformatted and everything is how I need it. I would've had to reformat anyway, as Mailchimp doesn't export the way Facebook wants me to import. Thank you for this insight, it will help in the future!
OpenOffice 4.1.3 Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] CSV not opening correctly

Post by Villeroy »

Do you upload confidential data to facebook servers? Strange.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
HazelGlass
Posts: 9
Joined: Sat Jan 30, 2021 12:55 am

Re: [Solved] CSV not opening correctly

Post by HazelGlass »

It's common practice for businesses. They are subscribers. It's called an audience list. It's one of the ways that ads target their customers on social media.
OpenOffice 4.1.3 Windows 10
Post Reply