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'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
add comment

3 Answers 3

up vote 1 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
    
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
add comment

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
    
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. –  Ellesa 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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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