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.
You can simply format your range as Text.
Also here is a nice article on the number formats and how you can program them.
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.
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.
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.
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:
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.
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.
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.
I have text in excell cells which look like numbers, and don't convert to numbers when I save the file as a CSV - because they are text. The MS help does not work (for me) would anyone advise me a usable way to get the text into numbers please? (clicking the d/down "convert to number" just messes it up, and it's still text.) Many thanks for your help.