15

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?

5
  • 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...?) Mar 19, 2013 at 16:37
  • This question is similar and offers some good solutions: superuser.com/questions/234997/… Mar 19, 2013 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. Mar 19, 2013 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). Mar 19, 2013 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, 2013 at 19:32

6 Answers 6

12

This should work

28,"65154",02/21/2013 00:00,"false",="0316295","=""8316012,8315844""","MALE"
7
  • No, that mangles the value with two numbers in it. We get ="1234567 with no closing quote. Does work for the simpler numbers, though. Mar 19, 2013 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, 2013 at 18:46
  • 2
    updated the answer so it would properly handle values with commas
    – Alex P.
    Mar 19, 2013 at 18:59
  • That handled the comma separated values properly. Thanks. Mar 20, 2013 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, 2016 at 20:01
12

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.

4
  • 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. Mar 19, 2013 at 18:06
  • 1
    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, 2013 at 23:18
  • That's a very good point. Thanks for pointing that out. It might be useful in the future. Mar 20, 2013 at 12:03
  • Text import wizard seems to no longer exist. I was able to import a CSV interpreting everything as text using this method: (1) open a blank worksheet, (2) go to Data > Get Data > From File > From Text/CSV > select csv file, (3) click import (4) change Data Type Detection to Do Not Detect Data Types.
    – Alcamtar
    Oct 15, 2020 at 17:18
6

Excel will auto-detect the format of a CSV field unless the CSV column is in this format:

"=""Data Here"""

This also works for Google sheets

4

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.

2
  • 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. Aug 29, 2015 at 20:17
  • 1
    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, 2015 at 20:29
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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