Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
12  
can I give a +1 just for the name? –  tombull89 Jan 31 '11 at 18:57
6  
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 Feb 1 '11 at 10:27
    
Excel doesn't seem to respect all commas within double quotes. Consider "August 12, 2012" Excel turns that into garbage too. –  zundarz Jun 29 '12 at 16:02
    
I want to mention this SU question. It explains which options you have when dealing with CSV in Excel. –  nixda Jan 7 '13 at 8:50
    
@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 Jan 7 '13 at 14:19

4 Answers 4

up vote 24 down vote accepted

But Excel, without fail, converts these files into useless garbage.

Excel is useless garbage.

That's being a little harsh. Excel is reading the serial numbers as numbers and displaying them with scientific notation. So really Excel is doing what most people would want it to do. It's not really doing any conversion at all and the files are usable and represent the data as provided.

Enclosing the serial numbers in double quotes doesn't indicate that the data contained within is supposed to be interpreted as a string and not as a number. So Excel's behavior is expected.

You could argue that by default displaying enormous numbers in scientific notation is unexpected... except that scientific calculators have been doing that for at least 30 years.

edit: fixed the above

Solution

I would be a little surprised if any client wanting your data in an Excel format was unable to change the visible formatting on those three columns to "Number" with zero decimal places or to "text." But let's assume that a short how-to document is out of the question.

  1. Your options are to toss a non numeric, not whitespace character into your serial numbers.
  2. Write out an xls file or xlsx file with some default formatting.
  3. * Cheat and output those numbers as formulas ="268435459705526269","",="268435459705526269" (you can also do ="268435459705526269",,="268435459705526269" saving yourself 2 characters). This has the advantage of displaying correctly, and probably being generally useful, but subtly broken (as they are formulas)

edit: added option 3


* Be careful with option 3, because some programs (including Excel & Open Office Calc), will no longer treat commas inside ="" fields as escaped. That means ="abc,xyz" will span two columns and break the import.

Using the format of "=""abc,xy""" solves this problem, but this method still limits you to 255 characters because of Excel's formula length limit.

share|improve this answer
    
Actually, that's not being harsh. Copy and paste one of the numbers above into Excel, then change the number format as suggested. Excel changes the value, resulting in garbage. –  Joe Internet Jan 19 '11 at 2:03
1  
@Joe, I was too cursory on my initial overview. Excel is indeed producing garbage, and is itself garbage. I've updated my answer to reflect that. An option might be having an "Excel csv" and also having a "usable, worthwhile csv" –  Tyler Jan 19 '11 at 2:39
2  
@Tyler - I don't think Excel is garbage, just saying that OP was correct that it was producing garbage in this case. It's actually a very good question, with no seemingly elegant solution. –  Joe Internet Jan 19 '11 at 3:50
1  
The Format Cells... option has been suggested, and I have tried to use it. In this case, the moment you open the file, Excel seems to convert the serials to Scientific notation (agreed, not unexpected) and tosses the precision. When you change them to a number or to text, the string does not come back. That really is the essence of the problem. Outputting as formulas may do it though...I didn't think of that. –  atroon Jan 19 '11 at 15:17
4  
@DMA57361 The behavior is not expected, it is determinable. The numeric precision is well documented, how excel reads CSVs is not. The lack of warning and silently discarding data is absurd. The fact that you cant even tell Excel how to import the data is equally absurd. Is the negativity needed? No, but honesty is the best policy and that is how I feel. –  Tyler Feb 3 '11 at 5:27

We had a similar problem where we had CSV files with columns containing ranges such as 3-5 and Excel would always convert them to dates e.g. 3-5 would be 3 Mar, after which switching back to numeric gave us a useless date integer. We got around it by

  1. Renaming the CSV to TXT extension
  2. Then when we opened it in Excel, this would kick in the text import wizard
  3. In Step 3 of 3 in the wizard we told it the columns in question were text and they imported properly.

You could do the same here I would think.

text import wizard

Cheers

share|improve this answer
1  
+1 for being the correct way to do it. (edit: sorry had to edit a little to clarify solution) –  jay Feb 7 '12 at 22:41
1  
You don't have to rename your file. Just use the import wizard Shift-select all columns and choose as text. –  nixda Jan 7 '13 at 8:58

My solution: I've got the same issue with importing serial numbers. They don't have to be treated as numbers, ie no mathematical functions are performed on it, but we need the entire number in there. The simplest thing I have is to insert a space in the serial number. eg "12345678 90123456 1234". When Excel imports it, it will be treated as text instead of a numeric.

share|improve this answer

I had long account numbers being garbled.

This is how I fixed it:

Open your file.csv in Libre Office/Open Office (you may have to specify delimiters etc.) then save the file as an Excel XML file.

Then open this file in Excel and then you will see columns are no longer changed into scientific format or whatever. To be safe right-click the column and explicitly set the format as Text, then save as Excel file format.

Open the Excel format file and the column should still be ok!

share|improve this answer
    
While this would work, trying to explain to someone who speaks only broken English why he would need to use a different office suite creates just as many problems as it solves. Alternative software to M$ Office is all to the good in my own opinion, but I realize that I can't convert everyone. –  atroon May 17 '13 at 18:31

protected by Community Jan 23 at 18:15

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.