[excel] Excel CSV - Number cell format

I produce a report as an CSV file. When I try to open the file in Excel, it makes an assumption about the data type based on the contents of the cell, and reformats it accordingly.

For example, if the CSV file contains

...,005,...

Then Excel shows it as 5. Is there a way to override this and display 005?

I would prefer to do something to the file itself, so that the user could just double-click on the CSV file to open it.

I use Excel 2003.

I had this issue when exporting CSV data from C# code, and resolved this by prepending the leading zero data with the tab character \t, so the data was interpreted as text rather than numeric in Excel (yet unlike prepending other characters, it wouldn't be seen).

I did like the ="001" approach, but this wouldn't allow exported CSV data to be re-imported again to my C# application without removing all this formatting from the import CSV file (instead I'll just trim the import data).

Well, excel never pops up the wizard for CSV files. If you rename it to .txt, you'll see the wizard when you do a File>Open in Excel the next time.

This works for Microsoft Office 2010, Excel Version 14

I misread the OP's preference "to do something to the file itself." I'm still keeping this for those who want a solution to format the import directly

1. Open a blank (new) file (File -> New from workbook)
   
2. Open the Import Wizard (Data -> From Text)
   
3. Select your .csv file and Import
   
4. In the dialogue box, choose 'Delimited', and click Next.
   
5. Choose your delimiters (uncheck everything but 'comma'), choose your Text qualifiers (likely {None}), click Next
   
6. In the Data preview field select the column you want to be text. It should highlight.
   
7. In the Column data format field, select 'Text'.
   
8. Click finished.
   

The Text Import Wizard method does NOT work when the CSV file being imported has line breaks within a cell. This method handles this scenario(at least with tab delimited data):

1. Create new Excel file
2. Ctrl+A to select all cells
3. In Number Format combobox, select Text
4. Open tab delimited file in text editor
5. Select all, copy and paste into Excel

Put a single quote before the field. Excel will treat it as text, even if it looks like a number.

...,`005,...

EDIT: This is wrong. The apostrophe trick only works when entering data directly into Excel. When you use it in a CSV file, the apostrophe appears in the field, which you don't want.

http://support.microsoft.com/kb/214233

You can simply format your range as Text.

Also here is a nice article on the number formats and how you can program them.

This has been driving me crazy all day (since indeed you can't control the Excel column types before opening the CSV file), and this worked for me, using VB.NET and Excel Interop:

        'Convert .csv file to .txt file.
        FileName = ConvertToText(FileName)

        Dim ColumnTypes(,) As Integer = New Integer(,) {{1, xlTextFormat}, _
                                                        {2, xlTextFormat}, _
                                                        {3, xlGeneralFormat}, _
                                                        {4, xlGeneralFormat}, _
                                                        {5, xlGeneralFormat}, _
                                                        {6, xlGeneralFormat}}

        'We are using OpenText() in order to specify the column types.
        mxlApp.Workbooks.OpenText(FileName, , , Excel.XlTextParsingType.xlDelimited, , , True, , True, , , , ColumnTypes)
        mxlWorkBook = mxlApp.ActiveWorkbook
        mxlWorkSheet = CType(mxlApp.ActiveSheet, Excel.Worksheet)


Private Function ConvertToText(ByVal FileName As String) As String
    'Convert the .csv file to a .txt file.
    'If the file is a text file, we can specify the column types.
    'Otherwise, the Codes are first converted to numbers, which loses trailing zeros.

    Try
        Dim MyReader As New StreamReader(FileName)
        Dim NewFileName As String = FileName.Replace(".CSV", ".TXT")
        Dim MyWriter As New StreamWriter(NewFileName, False)
        Dim strLine As String

        Do While Not MyReader.EndOfStream
            strLine = MyReader.ReadLine
            MyWriter.WriteLine(strLine)
        Loop

        MyReader.Close()
        MyReader.Dispose()
        MyWriter.Close()
        MyWriter.Dispose()

        Return NewFileName
    Catch ex As Exception
        MsgBox(ex.Message)
        Return ""
    End Try

End Function

When opening a CSV, you get the text import wizard. At the last step of the wizard, you should be able to import the specific column as text, thereby retaining the '00' prefix. After that you can then format the cell any way that you want.

I tried with with Excel 2007 and it appeared to work.

Don't use CSV, use SYLK.
http://en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK)

It gives much more control over formatting, and Excel won't try to guess the type of a field by examining the contents. It looks a bit complicated, but you can get away with using a very small subset.

Adding a non-breaking space in the cell could help. For instance: "firstvalue";"secondvalue";"005 ";"othervalue"

It forces Excel to treat it as a text and the space is not visible. On Windows you can add a non-breaking space by tiping alt+0160. See here for more info: http://en.wikipedia.org/wiki/Non-breaking_space

Tried on Excel 2010. Hope this can help people who still search a quite proper solution for this problem.

Just add ' before the number in the CSV doc.

Actually I discovered that, at least starting with Office 2003, you can save an Excel spreadsheet as an XML file. Thus, I can produce an XML file and when I double-click on it, it'll be opened in Excel. It provides the same level of control as SYLK, but XML syntax is more intuitive.

I know this is an old question, but I have a solution that isn't listed here.

When you produce the csv add a space after the comma but before your value e.g. , 005,.

This worked to prevent auto date formatting in excel 2007 anyway .

I believe when you import the file you can select the Column Type. Make it Text instead of Number. I don't have a copy in front of me at the moment to check though.

Load csv into oleDB and force all inferred datatypes to string

i asked the same question and then answerd it with code.

basically when the csv file is loaded the oledb driver makes assumptions, you can tell it what assumptions to make.

My code forces all datatypes to string though ... its very easy to change the schema. for my purposes i used an xslt to get ti the way i wanted - but i am parsing a wide variety of files.