Missing commas in CSV file

February 14, 2007 1:31 PM

Why is my CSV export missing commas, which is foiling my upload?

I have an Excel document, with 14 columns. The first column is the only one that has the important data I need (email addresses, if that matters). The other 13 columns are without data, BUT the upload system I need to use needs those column headings and will accept empty data.
When I Save As a CSV file, the first 15 rows (15 email addresses) have all the necessary commas after them...but row 16 onward only show email address and no following commas.

posted by fijiwriter to computers & internet (16 comments total)

Were I you, I would open the exported document in a text editor and replace any line ending not preceded by a comma with twelve commas and a line ending. I don't know what text editors you're familiar with, but something in that direction would be the thing to do, especially if this is a one-off requirement.
posted by Aidan Kehoe at 1:38 PM on February 14, 2007

Thought of that idea, but this is a list of 20,000 odd addresses.
posted by fijiwriter at 1:42 PM on February 14, 2007

Something that’s not a problem for some text editors. What platform are you on? If it’s a Mac,

perl -e 's/[^,]$/,,,,,,,,,,,,/g' < name-of-csv-file.csv > name-of-modified-csv-file.csv

should work from a terminal.
posted by Aidan Kehoe at 1:52 PM on February 14, 2007

PC, sorry.
posted by fijiwriter at 1:55 PM on February 14, 2007

Maybe there is a space or something in the 14th column of the first 15 rows.
posted by smackfu at 1:56 PM on February 14, 2007

It's a bug in Excel (not something you did wrong.)
posted by lullabyofbirdland at 1:56 PM on February 14, 2007

If you don't have a GREP-y text editor that can handle syntax like Aidan wrote, you could just search and replace the end of line character with a-bunch-of-commas-and-then-an-end-of-line in your normal text editor and then delete the excess from the first 15 rows by hand.
posted by bcwinters at 1:57 PM on February 14, 2007

Notepad is the best I can offer.
Let's say my CSV file looks like the following, what is the best way to add all those commas to 20K lines?
I don't see how I can determine what the end of line command is in Notepad.

[e-mail],,,,,,,,,,,,,
[e-mail],,,,,,,,,,,,,
you3@
you4@
...
posted by fijiwriter at 2:04 PM on February 14, 2007

Do you need to know where the end of the line is? I just opened Notepad, typed
asdf (< -- six spaces)br> and did a find-and-replace where I entered six spaces in the Find box and six commas in the Replace box. That should work fine for you, unless I'm misunderstanding what your file looks like.
posted by lullabyofbirdland at 2:09 PM on February 14, 2007

download edit+ (a great text editor for pc with free trial time) load the file, click search/replace/regular expressions

Find : /(\n)/
Replace : ,,,,,,,,,,,,\1
posted by twine42 at 2:10 PM on February 14, 2007

sorry about the messed-up HTML -- maybe I shouldn't have use angle brackets.
posted by lullabyofbirdland at 2:11 PM on February 14, 2007

I lied... Find should just be (\n) .

Why can't we all be happy and just have one regex system?
posted by twine42 at 2:13 PM on February 14, 2007

twine42, that you didn't have to quote the search items should have been a tip.

Perl will eat your brane! Making regex a fundamental part of the syntax breaks everyone.

And, agreed -- a standard format would be nice. Between vim, sed, and the various languages, it's amazing I can use them at all without a manual. (And sometimes I can't.)
posted by cmiller at 2:41 PM on February 14, 2007

cmiller: RegEx is wonderful, dunno what you're talking about.

If you're having trouble getting a match right you can use The Regex Coach (Windows) for replace functions. Not sure how well it'll cope with really that many lines, but it lets you see interactively what's being matched/replaced and you can then copy the expression to where ever you need it.
posted by anaelith at 9:15 PM on February 14, 2007

Open up the Excel file in Excel.

Select cell B2 (an empty cell, if I'm reading your description right).

Drag the autofill handle from B2 to N999 (or N-whatever-your-last-real-row-is).

Save the file.

Export it again as a CSV.

I'd bet a small amount of money that the missing commas will appear.
posted by flabdablet at 11:27 PM on February 14, 2007

cmiller : perl? Oh gods no. I have enough problems switching back and forth between the PHP and JS versions of things. I spent about 20 minutes fighting the regex in edit+ yesterday (for a problem of my own) before I discovered that it doesn't use ? to signify a non-greedy match.
posted by twine42 at 12:07 AM on February 15, 2007