Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 8 down vote favorite

Searching around for a while trying to figure out how to format a CSV file in such a way to force Excel to interpret the values as a string and not try and convert them to numbers or dates.

e.g...

"141", "10/11/2002", "350.00", "1311742251"

Excel tries to "intelligently" convert all these to it's native date/number formats. Is there a way around that?

EDIT: Clarified the intent of my question, sorry for confusion.

share|improve this question

4 Answers

up vote 9 down vote

Using Excel's import functionality allows you to specify the format (auto, text or date) each column should be interpreted as and does not require any modification to the data files.

You can find it as Data → Get External Data → From Text in Excel 2007/2010.
Or Data → Import External Data → Import Data in Excel 2003.

Here's an image of the Excel 2003 Text Import Wizard in action on the example data given, showing me importing the latter two columns as text:

Excel 2003: Text Import Wizard on Step 3 - data types

share|improve this answer
Excellent answer DMA57361, thanks for all the detail. What I didn't really mention in my question was that I'm writing a script that exports data to Excel, so I was trying to prevent users from having to jump through confusing options like this. But voted you up anyway. :-) – Simon Aug 8 '11 at 11:26
@Simon, what're you writing the script in? Any way you can get it to produce actual Excel files directly, instead of going via an intermediate format? – DMA57361♦ Aug 8 '11 at 11:27
it's a PHP script that exports a database table. CSV is probably the easiest to work with, but you're correct, I could probably produce an XLS with the help of some open-source code, or even just an HTML table which I think from past experience produces reasonable results in Excel (allows colours & formatting etc., but not sure about data-types). – Simon Aug 8 '11 at 11:34
1  
There's a few questions over on SO about PHP→Excel, the first few I've tried all have an answer pointing to PHP Excel, so that might be worth a look. – DMA57361♦ Aug 8 '11 at 11:38
up vote 7 down vote accepted

For those that have control over the source data, apparently Excel will auto-detect the format of a CSV field unless the CSV column is in this format:

"=""Data Here"""

eg...

20,       5.5%,      "0404 123 351", "3-6",  "=""123"""
[number]  [percent]  [number]        [date]  [string]  <-- how Excel interprets

Not sure whether many other apps would support this notation, however.

EDIT: Updated with corrections, thanks DMA57361!

share|improve this answer
Awesome, we just need to change the data.. sigh – PriceChild Aug 3 '11 at 9:05
3  
That last column should be "=""123""" otherwise it's badly formed. Fields containing a " must be delimited and the "s in the field escaped with other "s. – DMA57361♦ Aug 3 '11 at 9:18
@DMA57361 actually the way he has it is fine, it's the other two fields beside it that are missing the prepended equal sign. What he put there is setting that cell's formula to return a string. To also avoid this, you can set the cell's data type to "Text". – Breakthrough Aug 3 '11 at 10:43
1  
@Breakthrough that table there represents a CSV file, not Excel fields. The last value ="123" is not a valid CSV field because it contains the field delimiter character " without correctly delimiting it or the field. The fact Excel happens to read it as a formula is purely up to Excel and nothing to do with the CSV file. – DMA57361♦ Aug 3 '11 at 10:47
1  
@PriceChild, the point of my original question (that I didn't really explain very well) was actually how to format the CSV to make it as easy as possible for users. And so this is the answer I found myself and wanted to post. DMA57361 actually brought a helpful correction too, thanks! – Simon Aug 8 '11 at 11:28
show 1 more comment
up vote 1 down vote

The example from Simon did not work for me, and I suspect it is a language difference. In C# here is what my working format string looks like:

var linebreak = (i++ == list.Count) ? "" : "\r\n";

csv += String.Format("=\"{0}\",{1},{2},{3},=\"{4}\"{5}",
    item.Value, item.Status, item.NewStatus, item.Carrier, c.Status, linebreak);

and this is what the output file looks like:

="abababababab",INVALID,INVALID,USPS,="",
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9400110200793482982812",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9400110200793482982812",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9400110200793000216184",UNKNOWNSTATUS,INVALID,USPS,=""

As can be seen, the format in the output file is ="VALUE", not "=""VALUE""", which I believe may be a Visual Basic convention.

I am using Excel 2010. Incidentally, Google Sheets will not open/convert a file formatted this way. It will work if you remove the equal sign thus "VALUE", - Excel will still open the file but ignore the fact that you want your columns to be strings.

share|improve this answer
up vote 0 down vote

A simple way to force Excel to interpret the date as text is to put a single quote in front of the date, instead of using full quotes, as in:

'10/11/2002

If you can import the CSV instead of opening it, you can tell Excel what format each column should be. Have a look at this question I asked.

share|improve this answer
Adding a single quotation mark will also add this to the value. See this picture to proof. After looking to your question, maybe you are interested in this Add-In for CSV Import. It asks the user and is automatically available in every opened workbook – nixda Jan 13 at 23:40

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.