How do I get a CSV file to display properly?

April 13, 2010 7:35 AM

I suspect I have a problem using OS X to upload a CSV file to a webform, and I suspect I’ll need an XLS guru to help me figure it out.

Here’s the problem: I have to upload a CSV file to a webform. When I do this at the office (more on this in a moment) I have no problems, but when I do it from home, the file does not display properly. The file is supposed to display as a typical table with seven columns and any number of rows. Instead of displaying a table, I get one long row with as many columns as there were supposed to be rows + columns x rows.

I have two sources for downloading the CSV file in question: from the webform itself and from a website where I download a regular XLS file then convert it to CSV. It doesn’t matter from which source I originally get the file or how I edit it: it never displays properly when upload it from home. Also, it doesn’t appear to matter which browser I use to download or upload the file (FF, Safari, IE).

So, I suspect this is an OS X vs. Windows problem. At work I use a machine running XP SP2. At home, I use a machine running OS X 10.6.3. I’ve read a couple of places that the OS X version of Excel handles cell ends differently for CSV files, but that’s about as far as I’ve gotten. If it makes any difference (I don’t think it does at this point), the webform to which I upload the CSV file can only be accessed from IE.

Any advice welcome. I’m about to call an exorcist.

posted by digitalprimate to Computers & Internet (7 answers total)

Your problem is the different way OS X, Windows, and UNIX handle line breaks. I'm not sure if Mac Excel offers switching to Windows-style line breaks when you export, but you can open up the CSV file and change the line breaks in most text editors.
posted by mkultra at 7:41 AM on April 13, 2010

You probably need to do a newline conversion. OS X and other Unix like systems only have an LF character at the end of a line. Windows uses CR+LF. I think CSV technically needs to have CR+LFs. Is there any option when you convert the file to CSV for how it handles newlines? If not, there should be utilities within OS X to do the conversion (something like unix2dos).

I don't know much about OS X repositories, but it looks like this is a GUI tool for doing the conversion.
posted by kmz at 7:45 AM on April 13, 2010

This may have to do with the different way that Windows, and OS X handle line endings. Use a good free OS X text editor like TextWrangler to convert the line endings of your file.
posted by wheat at 7:45 AM on April 13, 2010

Best answer: I'm not sure if you're saving the file to csv format in excel, but in my version of excel on OS X, the drop-down list on the save dialog offers two more types of csv files near the bottom of the list.
posted by :-) at 8:49 AM on April 13, 2010

Excel on OSX never handles .csv files properly for me; I assume because of newline related issues as others point out. I have never bothered to figure out why because Open Office works great; I'm uploading an open office .csv file AS WE SPEAK!!!!!
posted by Kwine at 9:05 AM on April 13, 2010

Best answer: like :-) mentions, Excel 2008 on the Mac has a generic "CSV", a "Windows CSV" and a "MS-DOS CSV" format. Not sure if you've tried all three, but I found when I save stuff in Office specifically listing Windows or DOS, non-MacOSX apps are much happier.
posted by birdherder at 9:45 AM on April 13, 2010

Response by poster: Thanks to :-) and birdherder for the obvious, "is-it-plugged-in?" answer, which worked like, um, things that work.

I'll be over here, in the corner, weeping over my humiliation and lost geek creds....
posted by digitalprimate at 6:35 AM on April 14, 2010