CSV files can't be formatted. If this was possible Microsoft would have supplied the option to store formatting information.
Personally I think this is good. CSV files are there to exchange data, not formatting. Unfortunately Microsoft opens CSV files by default in Excel as soon Office is installed on your computer. You don't know how often users called me with a remark that date value didn't have the proper date format because the opened it in Excel.
However there might a workaround, although
I personnaly would not use it. I prefer using things for the purpose they are intended for. Disadvantages are:
- not language independent
- data in the CSV is changed quite a lot
- saving would change the CSV
It seems to be possible to supply text in your CSV file that Excel interprets as formulas. For instance the following CSV would show 001, 002 and 003
in my Dutch Excel version (which shows the first problem, because in your English version it won't work).
"ID";"Test"
1;"=TEKST(1;""000"")"
2;"=TEKST(2;""000"")"
3;"=TEKST(3;""000"")"
If however you are very sure that all your users use an English Excel, it might work to use the following (I assume the default seperator in the english version is a comma - you might need to try some different field and text seperators):
"ID";"Test"
1,"=TEXT(1,""000"")"
2,"=TEXT(2,""000"")"
3,"=TEXT(3,""000"")"
The other problem would be that reading your CSV as plain data is only possible with some advanced parsing of the data.
A last problem would be that once the CSV is opened in Excel and it is being saved again, the CSV would be changed to
ID;Test
1;001
2;002
3;003
I my honest opinion you should use CSV as it is intended: to transfer data. Actually Excel is the problem here. Excel does the formating of the data in the CSV file.
There are ways to influence the formating within Excel. In Excel you could open a CSV through the menu
Data. If you choose
From Text (the button for opening textfiles), the wizard lets you indicate that a column is a text column. You could record a Macro to see how it is coded in VBA. Use this code to write a macro that your users could start or use it to program VB.NET to control Excel. Ofcourse this only works if you use code to control the opening of the CSV. It won't work if the user opens the CSV file by double clicking.