Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've got a web application that is exporting its data to a CSV file. Here's one example row of the CSV file in question:

28,"65154",02/21/2013 00:00,"false","0316295","8316012,8315844","MALE"

Since I can't post an image, I'll have to explain the results in Excel. The "0316295" field gets turned into a number and the leading 0 goes away. The "8316012,8315844" gets interpreted as one single number: 83,160,128,315,844. That is, most obviously, not the intended result.

I've seen people recommend a leading single quote for such cases, but that doesn't really work either.

28,"65154",02/21/2013 00:00,"false","'0316295","'8316012,8315844","MALE"

The single quote is visible at all times in the cell in Excel, though if I enter a number with a leading single quote myself, it shows just the intended string and not the single quote with the string.

Importing is not the same as typing, it seems.

Anybody have a solution here?

share|improve this question
    
Are you able to import it using LibreOffice or something of that ilk? I've just imported and got : 28 65154 02/21/2013 00:00 false 0316295 8316012,8315844 MALE (if you can read that...?) – FreudianSlip Mar 19 '13 at 16:37
    
This question is similar and offers some good solutions: superuser.com/questions/234997/… – Brad Patton Mar 19 '13 at 16:38
    
@FreudianSlip No, we can't use some 3rd party solution. Users click on the "download CSV" button and the file opens in Excel. That's the environment we're dealing with. Can't really complicate it. – MichaelOryl Mar 19 '13 at 18:07
    
@Brad I read that post earlier looking for solutions. That's where I got the single quote option from, which I really don't like since Excel shows the quote in the cell itself when imported from CSV (even if it doesn't show it if you type the same thing in yourself). – MichaelOryl Mar 19 '13 at 18:09
    
so do you control the web app at least? how are you going to implement the solution transparently from the users? I have updated my answer so it would properly handle values with commas – Alex P. Mar 19 '13 at 19:32
up vote 5 down vote accepted

This should work

28,"65154",02/21/2013 00:00,"false",="0316295","=""8316012,8315844""","MALE"
share|improve this answer
    
No, that mangles the value with two numbers in it. We get ="1234567 with no closing quote. Does work for the simpler numbers, though. – MichaelOryl Mar 19 '13 at 18:12
1  
if you control the web application you could implement export into the SYLK en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK) format. it has much better control of data representation. if not - you could easily make a converter script – Alex P. Mar 19 '13 at 18:46
2  
updated the answer so it would properly handle values with commas – Alex P. Mar 19 '13 at 18:59
    
That handled the comma separated values properly. Thanks. – MichaelOryl Mar 20 '13 at 12:00
    
thanks. This solution solved an issue with importing hexidecimal numbers as well. Excel replaces 00E3 with 0 (0 * 10^3) and displays it as sci notation (0.00E+00). It actually does the same for "00e3" without the =. But ="00e3" works perfectly. (opening a CSV with Excel 2010) – mpag Feb 23 at 20:01

To keep the leading zero(s), you can set the format of that specific column to Text in the Text Import Wizard.

  1. Start the Text Import Wizard by going to the Data tab & clicking From Text.
  2. Select the CSV file you want to import.
  3. Pick Delimited & hit Next.
  4. Uncheck Tab (default). Choose Comma. Make sure Text Qualifier is set to ". Hit Next.
  5. This is where you can specify the formats. Click the header of the column whose leading zero(s) you want to preserve. Select Text. (See image below)

    enter image description here

  6. Hit Finish & then select a suitable location for your data. Sample result:

    enter image description here

Edit: There is another way; have the web app export to a comma-delimited .TXT file instead of .CSV (or just change the file extension of the exported file after saving it). This forces Excel to go through the import wizard when they open the file. As an additional advantage, it reduces the chances that users modify or tamper with your original raw data.

share|improve this answer
1  
Yes, that's certainly true. It's just not really convenient for the end user when, by comparison, all they have to do is click on the link of the CSV generator on our web app and Excel loads it automatically - albeit with the above mentioned problems. CSV files are set to open in Excel, and Excel doesn't offer any options at that time. It just loads them. I appreciate you pointing that out, though. – MichaelOryl Mar 19 '13 at 18:06
    
There is another way, but I don't know if it's possible in your case: have the web app export to comma-delimited .TXT instead of .CSV (or just change the file extension of the exported file). This sort of "forces" users to go through the import wizard when they open the file. – Kaze Mar 19 '13 at 23:18
    
That's a very good point. Thanks for pointing that out. It might be useful in the future. – MichaelOryl Mar 20 '13 at 12:03

Excel treats numeric values starting with a tab character as text.

<tabchar>000000100000200000,ABC,DEF

Will appear as:

000000100000200000|ABC|DEF

| is the cell boundary(for illustration purpose). So, for example, if your system generates the CSV file using utf-8 charset, you can add the &#x0009; (Tab Char in Hex) before your numeric value to force excel to interpret it as text.

share|improve this answer
    
Remove the space before semi colon in the Hex representation. I don't know how to escape the Hex Tab character in the answer, so I have deliberately typed it wrong. – Madeyedexter Aug 29 '15 at 20:17
    
I did the escaping. And I learned something new, thank you. I have to add this to my favorite topic for work :) – nixda Aug 29 '15 at 20:29
    
Thank you for the edit! – Madeyedexter Aug 29 '15 at 20:36

You'd have to use the "Data -> Get External Data -> Import Text from file" functionality in Excel to get it displayed as text. This is the text import wizard @Kaze mentioned.

The only way to force excel to display numbers as text from the input, without the user having to do anything, is by outputting an excel file and specifying a "custom number format" in it. I recommend using a XLS export library for this, as it doesn't seem as simple as adding a line of config text into a CSV file.

share|improve this answer

try the clean function for values that are messed up. For example =clean("12345678901234567890"). It works for me for numbers, dates, times, etc. I would caution that since this is not at all the purpose of this function that this could stop working with new versions, etc.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .