Excel CSV - Number cell format

Question

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.

See [this question](http://stackoverflow.com/q/2285552/3650944) for a macro solution to this problem. — Adam Jensen, Mar 16 '15 at 03:55
Nowadays there are a lot of Excel-writing libraries out there. It should be pretty easy to create .xlsx files in the language of your choice. If your users are still using Excel 2003 for some reason (common when this question was originally asked, but hopefully not very likely today), there are also some .xls-producing options. For people stumbling across this question today, it's very likely you don't have to roll your own. — John Y, Apr 04 '17 at 15:40

score 132 · Accepted Answer · answered Sep 26 '08 at 21:23

132

There isn’t an easy way to control the formatting Excel applies when opening a .csv file. However listed below are three approaches that might help.

My preference is the first option.

Option 1 – Change the data in the file

You could change the data in the .csv file as follows ...,=”005”,... This will be displayed in Excel as ...,005,...

Excel will have kept the data as a formula, but copying the column and using paste special values will get rid of the formula but retain the formatting

Option 2 – Format the data

If it is simply a format issue and all your data in that column has a three digits length. Then open the data in Excel and then format the column containing the data with this custom format 000

Option 3 – Change the file extension to .dif (Data interchange format)

Change the file extension and use the file import wizard to control the formats. Files with a .dif extension are automatically opened by Excel when double clicked on.

Step by step:

Change the file extension from .csv to .dif
Double click on the file to open it in Excel.
The 'File Import Wizard' will be launched.
Set the 'File type' to 'Delimited' and click on the 'Next' button.
Under Delimiters, tick 'Comma' and click on the 'Next' button.
Click on each column of your data that is displayed and select a 'Column data format'. The column with the value '005' should be formatted as 'Text'.
Click on the finish button, the file will be opened by Excel with the formats that you have specified.

answered Sep 26 '08 at 21:23

Robert Mearns

11,796
3
38
42

8

.dif didn't work on my machine; manually opening with Excel 2007 didn't work (warned me that the file type was different than the extension) and it looks like .DIF is associated with Quicktime on a lot of computers. Just FYI :) Otherwise, great tips! – Nicholas Head Oct 30 '08 at 18:55
17

i think option 1 is the best, if you want a hassle-free way to generate a file that "just works"--i.e. it opens on the user's computer in excel without requiring them to do any additional work. – Kip Aug 13 '09 at 13:53
I've got the same issue, and none of the above are appropriate sorry. THinking I might export as XML and use XSLT to go to CSV. I need the CSV file to be correct so that it can be imported into another application – hookenz Nov 18 '09 at 00:23
Will changing the number to a formula work in every encoding? – Rob Fox Feb 15 '12 at 14:37
1

Please note that option 1 is not always good, if you have a cell of let say "1E02, whatever text" and then you put an equal in front then Excel will imagine you entered a formula. First the formula will be wrong but second and most importantly it will split your text over 2 cells. – ghiscoding Jan 15 '14 at 15:26
None of these solve my problem. I need to send a CSV file to a client and I cannot teach him how to import data in Excel or formatting columns. Also by introducing a formula or a character it will cause an error while processing the file as a number is expected. – derloopkat Feb 24 '14 at 17:17
1

Thanks for Option 1. Fixed my scientific notation problem without any extra work. – NaN Mar 07 '14 at 05:03
Option 1 didn't work for me with Excel 2010 -- it imported the literal text ="012345" (so the equals sign and all appeared in the column) – JMarsch Nov 13 '14 at 21:46
@JMarsch This will happen if the Text Import Wizard is used and the column format is set to Text instead of General in the third step of the import. – Robert Mearns Nov 14 '14 at 15:38
@RobertMearns In my case, I wasn't using the Text Import wizard. I just saved the file as a csv, and double-clicked it from Windows Explorer. It opened in Excel, and had the literal ="12345" in the cell. – JMarsch Nov 14 '14 at 19:40
Option1 does wonders if Text import wizard is used or it is directly opened. Thanks. – logeekal Jun 01 '17 at 14:31

score 30 · Answer 2 · answered Sep 26 '08 at 02:49

30

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.

answered Sep 26 '08 at 02:49

Mark Ransom

299,747
42
398
622

2

+1 Writing to SYLK is simple to implement, I did it as novice. – Matt H Jul 08 '10 at 17:09
1

I did not know about this format. It looks like something that I could use. +1 for this! – Buggabill Apr 12 '11 at 14:01
+1 Took me five minutes to switch from CSV to SYLK. My goto output format from now on! – TheFish Mar 06 '12 at 19:04
@xamde, you're right. It's strange that they never extended the format to support it, as they did with RTF. – Mark Ransom Mar 29 '12 at 18:37
WTF, or what would anyone recommend it I cite from Wikipedia: "Microsoft does not publish a SYLK specification." – sorin Nov 27 '13 at 19:11
@sorin, I thought that I had seen some Microsoft documentation on it in the past. As long as Excel itself continues to support the format I think it's viable. – Mark Ransom Nov 27 '13 at 19:17
A fairly recent article (from 2019) shows how SYLK files are a security risk: https://outflank.nl/blog/2019/10/30/abusing-the-sylk-file-format/ The Wikipedia article mentions this, but it is surprising how easy it is to embed executable code in a SYLK file. If it is all kept in-house, these files might be a good solution, but it might not be worth the risk. Also, nowadays solutions that blend Excel, R and/or Python are common, and csv files are still a lingua franca. – John Coleman Feb 18 '21 at 19:07
1

@JohnColeman I won't argue that CSV is not popular, and its simplicity is undoubtedly a huge factor. But that simplicity keeps it from being robust. See [Scientists rename human genes to stop Microsoft Excel from misreading them as dates](https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates) for an eye-opening example. I wasn't aware of SYLK exploits. – Mark Ransom Feb 18 '21 at 19:17
@MarkRansom Thanks for the link. I am currently teaching a course in bionformatics programming. I'll show that to my students for comic relief. The problem here is of course more with how Excel reads CSV files than with the format itself. – John Coleman Feb 18 '21 at 19:24
@JohnColeman no doubt Excel is at fault here. Unfortunately they decided to use the same heuristics for interpreting CSV input as they use for hand-typed input. Common sense would dictate that if 1000 rows didn't have a date in a particular column you wouldn't make an exception for the one that looked kinda like a date, but evidently common sense is in short supply. – Mark Ransom Feb 23 '21 at 05:46

score 14 · Answer 3 · 2013-05-14T20:20:19.210

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

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

explained with images: https://support.insight.ly/hc/en-us/articles/212277188-How-to-open-a-CSV-file-in-Excel-to-fix-date-and-other-formatting-issues — Yo Ludke, Apr 25 '18 at 13:51

score 2 · Answer 4 · answered Sep 26 '08 at 02:10

2

You can simply format your range as Text.

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

answered Sep 26 '08 at 02:10

Ólafur Waage

68,817
22
142
198

That will only affect how the value is displayed. You need to convert it to text, either with a formula, or during import, with one of the many methods goven in this thread – leo Oct 23 '17 at 12:09

score 2 · Answer 5 · answered Sep 26 '08 at 04:29

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.

score 2 · Answer 6 · answered Oct 09 '13 at 14:01

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.

score 2 · Answer 7 · answered Jan 24 '14 at 11:20

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).

score 1 · Answer 8 · answered Sep 26 '08 at 02:10

1

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.

answered Sep 26 '08 at 02:10

Harley Holcombe

175,848
15
70
63

score 1 · Answer 9 · edited May 23 '17 at 11:54

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.

score 1 · Answer 10 · answered Nov 19 '13 at 11:17

1

Just add ' before the number in the CSV doc.

answered Nov 19 '13 at 11:17

MNassar

367
5
11

This did not work in Excel 2010 (' becomes part of the cell contents). – sancho.s ReinstateMonicaCellio Nov 20 '13 at 13:19

score 1 · Answer 11 · edited Aug 27 '14 at 18:03

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

score 1 · Answer 12 · answered Jan 04 '11 at 19:28

1

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 .

answered Jan 04 '11 at 19:28

personaelit

1,633
3
31
59

2

Just tested, doesn't seem to work with Excel 2007 and a number column. :-/ – Norman H Jan 19 '11 at 15:02

score 1 · Answer 13 · answered Apr 06 '11 at 15:18

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):

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

score 0 · Answer 14 · answered Sep 26 '08 at 02:12

0

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.

answered Sep 26 '08 at 02:12

Mark

10,022
2
38
41

1

Excel 2003 doesn't pop up the wizard. – Sergey Stadnik Sep 26 '08 at 02:32

score 0 · Answer 15 · answered Nov 30 '09 at 19:14

0

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.

answered Nov 30 '09 at 19:14

Narayanan

1

JW. · Answer 16 · 2010-10-14T20:14:04.103

0

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

edited Oct 14 '10 at 20:14

answered Jul 15 '10 at 19:32

JW.

50,691
36
115
143

5

This is something that people are likely to try. So knowing it won't work is useful and saves time. – AndyM Dec 03 '13 at 21:25

Excel CSV - Number cell format

16 Answers16

Linked

Related