17

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?

Carl B
  • 6,660
Michael Oryl
  • 297

6 Answers6

13

This should work

28,"65154",02/21/2013 00:00,"false",="0316295","=""8316012,8315844""","MALE"
Alex P.
  • 2,763
13

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.

Ellesa
  • 11,185
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

Chris Halcrow
  • 597
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.

nixda
  • 27,634
Madeyedexter
  • 141
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.

Mark Prather
  • 1
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.

Magne
  • 816