Missing commas in CSV file
February 14, 2007 1:31 PM Subscribe
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.
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.
Response by poster: 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
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
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
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
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
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
posted by bcwinters at 1:57 PM on February 14, 2007
Response by poster: 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.
you@somewhere.com,,,,,,,,,,,,,
you2@somewhere.com,,,,,,,,,,,,,
you3@
you4@
...
posted by fijiwriter at 2:04 PM on February 14, 2007
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.
you@somewhere.com,,,,,,,,,,,,,
you2@somewhere.com,,,,,,,,,,,,,
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
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
Best answer: 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
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
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
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
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
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
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
posted by twine42 at 12:07 AM on February 15, 2007
« Older One-way mirrored birdfeeder reflective window... | Won't someone think of (shooting) the children? Newer »
This thread is closed to new comments.
posted by Aidan Kehoe at 1:38 PM on February 14, 2007