129
25
I have a database which tracks sales of widgets by serial number. Users enter purchaser data and quantity, and scan each widget into a custom client program. They then finalize the order. This all works flawlessly.
Some customers want an Excel-compatible spreadsheet of the widgets they have purchased. We generate this with a PHP script which queries the database and outputs the result as a CSV with the store name and associated data. This works perfectly well too.
When opened in a text editor such as Notepad or vi, the file looks like this:
"Account Number","Store Name","S1","S2","S3","Widget Type","Date"
"4173","SpeedyCorp","268435459705526269","","268435459705526269","848 Model Widget","2011-01-17"
As you can see, the serial numbers are present (in this case twice, not all secondary serials are the same) and are long strings of numbers. When this file is opened in Excel, the result becomes:
Account Number Store Name S1 S2 S3 Widget Type Date
4173 SpeedyCorp 2.68435E+17 2.68435E+17 848 Model Widget 2011-01-17
As you may have observed, the serial numbers are enclosed by double quotes. Excel does not seem to respect text qualifiers in .csv files. When importing these files into Access, we have zero difficulty. When opening them as text, no trouble at all. But Excel, without fail, converts these files into useless garbage. Trying to instruct end users in the art of opening a CSV file with a non-default application is becoming, shall we say, tiresome. Is there hope? Is there a setting I've been unable to find? This seems to be the case with Excel 2003, 2007, and 2010.
1
Beware that if Excel automatically converts your data into numbers, it will drop any data after 15 digits of precision. I was pulling my hair out over this exact issue, trying to figure out why there were trailing zeroes on every one of my IDs even after setting the correct display and data type. More info: http://superuser.com/a/437794/236877
– Matt Vukas – 2016-08-02T22:13:54.43046can I give a +1 just for the name? – tombull89 – 2011-01-31T18:57:25.187
11
Excel does not seem to respect text qualifiers in .csv files
- the double quotes are not text qualifiers, they simply allow commas in your data, if you don't use commas in your data then they are meaningless. All data in a CSV file is untyped, so Excel can only guess that your large serial number is a number, and that's when you run in to Excel's maximum precision of 15 digits, which is what is truncating your numbers. – DMA57361 – 2011-02-01T10:27:13.9601Excel doesn't seem to respect all commas within double quotes. Consider "August 12, 2012" Excel turns that into garbage too. – zundarz – 2012-06-29T16:02:58.303
5
I want to mention this SU question. It explains which options you have when dealing with CSV in Excel.
– nixda – 2013-01-07T08:50:15.1271@nixda Thanks! Those are useful suggestions, especially for the more experienced users. My problem is almost more of a human problem, in that Excel associates itself with .csv files, and people see the icon, and double click (because that's how you open things), and then usually hit Save (because we're always telling them to Save!), and all is lost. But I will definitely use your methods when possible. – atroon – 2013-01-07T14:19:17.190