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.

john c. j.
  • 286
  • 3
  • 25
  • 2,626
  • 4
  • 21
  • 21
  • 50
    can I give a +1 just for the name? – tombull89 Jan 31 '11 at 18:57
  • 12
    `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](http://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel) of 15 digits, which is what is truncating your numbers. – DMA57361 Feb 01 '11 at 10:27
  • 1
    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
  • 6
    I want to mention this [SU question](http://superuser.com/questions/307496/how-can-i-set-excel-to-always-import-all-columns-of-csv-files-as-text/527894#527894). It explains which options you have when dealing with CSV in Excel. – nixda Jan 07 '13 at 08:50
  • 1
    @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 07 '13 at 14:19
  • 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 Aug 02 '16 at 22:13

7 Answers7


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

Excel is useless garbage.


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.

Your options are:

  1. 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).

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.

  • 1,783
  • 8
  • 24
  • 49
  • 4,271
  • 21
  • 22
  • 2
    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 02: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 02: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 03:50
  • 2
    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
  • Do we need the negativity? Excel has a *well documented* (See [Wikipedia](http://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel) and "Specifications and Limits" for [2003](http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx), [2007](http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx), [2010](http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx)) precision of 15 digits, the serials are 18 digits long and CSV data is untyped. This behaviour is expected. – DMA57361 Feb 01 '11 at 10:34
  • 11
    @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 03 '11 at 05:27
  • 2
    This also worked for an id number that had a dash. If the value was a valid date, then Excel would convert it to a date field. For example, 5-1 would convert to May-1. Adding double quotes and a space, i.e. " 5-1", showed up in Excel as a text field of 5-1. – Sam Tyson Jun 25 '12 at 17:42
  • Dates are a different matter. Excel turns both "August 12, 2012" and ="August 12, 2012" into 12-Aug-12 or some other predetermined format even though I'm using double quotes to delimit a text value with a comma. – zundarz Jun 29 '12 at 15:57
  • Note that Apple Numbers does exactly the same thing. Either they're both useless garbage or it's expected behavior. – Jamon Holmgren Oct 01 '14 at 20:57
  • I could not make much sense out of all these solutions provided here. I have a csv file with this sample date - `User id,mobile,email 5868127854175771341,,test@gmail.com` and I want to convert it to xlsx. I tried several ways - like #1 - opening a new formatting column to text before pasting the data. #2 converting csv to xlsx. In case #1, data shows ok when viewing xlsx file but when I feed it my application, it reads as 5868127854175770000. In case #2, it shows in xlsx also like that 5868127854175770000. – Sandeepan Nath Jun 25 '18 at 12:07

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


Jay Wick
  • 6,759
  • 5
  • 49
  • 68
  • 441
  • 3
  • 2
  • 2
    +1 for being the correct way to do it. *(edit: sorry had to edit a little to clarify solution)* – Jay Wick Feb 07 '12 at 22:41
  • 2
    You don't have to rename your file. Just use the import wizard Shift-select all columns and choose as text. – nixda Jan 07 '13 at 08:58
  • 2
    Text Import Wizard is THE answer. All other solutions are needless hackery resulting from not understanding how to use Excel to view and edit CSVs. – Excellll Oct 28 '14 at 22:27
  • 1
    @Excellll, doing it one file at a time sure. When automating this process, 'needless hackery' often saves the day. – Parrish Husband Mar 24 '15 at 18:07
  • 2
    this is completely useless when excel is used by standard users to display CSV files. before i try to explain how to use the text-import-wizard to ~15 beginner-skill office users, i'd rather type the excel document source code by myself. – northkildonan Nov 30 '15 at 08:21

Better solution is to generate XML Workbook. Like this:

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">

  <ss:Worksheet ss:Name="Sheet 1">
    <Column ss:Width="100"/>
    <Column ss:Width="100"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="80"/>

      <Cell><Data ss:Type="String">Account Number</Data></Cell>
      <Cell><Data ss:Type="String">Store Name</Data></Cell>
      <Cell><Data ss:Type="String">S1</Data></Cell>
      <Cell><Data ss:Type="String">S2</Data></Cell>
      <Cell><Data ss:Type="String">S3</Data></Cell>
      <Cell><Data ss:Type="String">Widget Type</Data></Cell>
      <Cell><Data ss:Type="String">Date</Data></Cell>

      <Cell><Data ss:Type="String">4173</Data></Cell>
      <Cell><Data ss:Type="String">SpeedyCorp</Data></Cell>
      <Cell><Data ss:Type="String">268435459705526269</Data></Cell>
      <Cell><Data ss:Type="String">x</Data></Cell>
      <Cell><Data ss:Type="String">268435459705526269</Data></Cell>
      <Cell><Data ss:Type="String">848 Model Widget</Data></Cell>
      <Cell><Data ss:Type="String">2011-01-17</Data></Cell>


The file must have .xml extension. Excel and OpenOffice open it correctly.

  • Do you mean that the OP should use PHP script to convert the database into XML format ? – Prasanna Sep 30 '15 at 07:23
  • 2
    Very much cleaner than having users open .csv in Excel or messing up your CSV so that only Excel can understand your CSV. It’s not even that complex of a schema. – binki May 10 '16 at 15:06
  • Where is this standard documented? I would like to know more about the data types available. – John Doherty Oct 07 '17 at 11:49

The Import Wizard is the best solution for casual users and one-off situations. If you need a programmatic solution, you can use the QueryTables.Add method (which is what the Import Wizard is using behind the scenes).

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & "C:\myfile.csv", Destination:=Range("$A$1"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 2, 2) 'Edit this line. Add a number for each column, 1 is general, 2 is text. Search the internet for other formats.
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
  • 338
  • 1
  • 3
  • 10

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.

  • 19
  • 1

One way is to open the CSV in a text editor, select all, copy.

Then open Excel, select all cells, change Number format from General to Text and then Paste.

Numbers will have little green triangles on them and formulas won't work (unless you change the format back to General for the columns where you need them).

But your delicious CSV will stay tasty and SEPT2 will continue to be a gene name instead of 2nd September of an arbitrary year

  • 141
  • 2

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!

  • 199
  • 1
  • 5
  • 1
    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