85

I've been searching around for a while trying to figure out how to output a CSV file in such a way to force Excel to interpret the values as a string and not try to convert them to numbers or dates.

e.g.:

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

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


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

1
  • Stumbled on this one, too new to answer properly. However Excel scans the first 15 rows or so to try to guess what the data types/format should be. I used to stuff those rows with dummy data that was in the types I wanted which would then do the trick at the expense of having some lines to skip. Something like "TEXT", "TEXT", "TEXT", "TEXT" on each of the first 15 rows (after your header row if you have one) should do it. Hope that helps someone. – Pete Duncanson Oct 8 '20 at 12:02
93

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

It also works in Google Spreadsheet, but not sure if other spreadsheet apps support this notation.

If you suspect any the data may contain quotes itself, you need to double-escape them, like this...

"=""She said """"Hello"""" to him"""



(EDIT: Updated with corrections, thanks DMA57361!)

11
  • Awesome, we just need to change the data.. sigh – Pricey Aug 3 '11 at 9:05
  • 2
    @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
  • 2
    @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 East Aug 8 '11 at 11:28
  • 3
    Not sure how this detail has not been noticed by others and it's a significant piece of information: Excel will not preserve this format when saving the csv. So this only works if you're read-only, or if you never need to export to csv again. – parity3 May 10 '19 at 20:25
  • 1
    A note about this (and other similar tricks): Excel itself won't use this encoding when saving to CSV, so this won't work if you need it to be able to survive someone editing it and re-saving it in Excel. If you create a text value in Excel containing only a number, Excel will simply save it as-is when writing to CSV leading it to interpret it as a number when re-opening - so there is no way to get Excel to preserve this past an edit. – thomasrutter Jul 2 '19 at 0:27
47

Like many, I have been struggling with the same decisions that Microsoft makes and tried various suggested solutions.

For Excel 2007 the following goes:

  • Putting all values in double quotes does NOT help
  • Putting an = before all values after putting them in double quutes DOES help, BUT makes the csv file useless for most other applications
  • Putting parentheses around the double quotes around all values is rubbish
  • Putting a space before all values before putting double quotes around them DOES prevent conversions to dates, but DOES NOT prevent trimming of leading or trailing zeroes.
  • Putting a single quote in front of a value only works when entering data within Excel.

However:

Putting a tab before all values before putting double quotes around them DOES prevent conversions to dates AND DOES prevent trimming of leading or trailing zeroes and the sheet does not even show nasty warning markers in the upper left corner of each cell.

E.g.:

"<tab character><some value>","<tab character><some other value>"

Note that the tab character has to be within the double quotes. Edit: it turns out that the double quotes are not even necessary.

Double clicking the csv file can open the file as a spreadsheet in Excel showing all values that are treated as just above, like text data. Make sure to set Excel to use the '.' as the decimal point and not the ',' or every line of the csv file will end up as one text in the first cell of each row. Apparently Microsoft thinks that CSV means "Not the decimal point" Separated Value.

3
  • 1
    This tab trick saved my day, Excel was converting really long numeric values into numbers, and lost all digits after the 15th. Managed to get Excel to treat them as text with the tab prefix. So works for numbers as well, not just dates. – Markus Yrjölä Feb 3 '17 at 8:37
  • This is really useful, thank you! – Flavio Oct 24 '19 at 8:05
  • But while coping the the cell includes double quotes, ex. - " data" The accepted answer still help to some extent, but fails in case you copy the cell value after double clicking the cell. – prabhatojha Sep 23 '20 at 9:39
23

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 DataGet External DataFrom Text in Excel 2007/2010.
Or DataImport External DataImport 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

5
  • 1
    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 East 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 East 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
  • thats very helpful, after reading quite a few ansers – greg121 Sep 5 '14 at 20:32
2

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.

1
  • 1
    I also used this format and found that with Excel for Office 365 (2019) supports ="Value" and not the "=""VALUE""" in the accepted answer. – Chuck Conway Dec 10 '19 at 17:47
0

I had issues with Simon's answer if the text in question was too long (due, I think, to a bug in Excel: https://superuser.com/a/776614/1084793). Shane's answer didn't work for me if there was a comma in the string.

I also had issues using the = if the test contains a newline character.

In either instance (if it's long, or has a newline), I'm hoping that Excel will correctly interpret it anyway.

So currently, this is the C# code I'm using:

private string content;
public String Content
{
    get
    {
        // escape double-quotes first, to get the right length of the output
        string output = content.Replace("\"", "\"\"\"\"");
        if (output.Length > 255 || content.Contains("\n"))
        {
            // just output with double-quotes ("hello")
            return "\"" + content.Replace("\"", "\"\"") + "\"";
        }
        else
        {
            // output with the equals ("=""hello""")
            return "\"=\"\"" +
                output +
                "\"\"\"";
        }
    }
    set
    {
        content = value;
    }
}

That is so far working for all my scenarios. If anyone has a way around the newline or 255 character issue that still uses the "=" method, I'd be very interested.

-1

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.

2
  • 5
    Downvoted due to this NOT working in CSV files being imported into Excel. Excel interprets the quote as a literal quote and therefore shows it in the cell. – psynnott Apr 14 '14 at 15:33
  • Worked for me. Thanks! – Roberto Rodriguez Sep 18 '20 at 20:57

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