Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB
Hello guys!
Is there a way, that you can format CSV file???. For ex. 021 original data...viewing in excel 21 ...viewing in notepad 021 . I want is.. in viewing excel the leading zero will show 021 and same data in notepad 021 .Is there a function or any code to format csv file?
Posted 28-Sep-12 0:03am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Comments
Maciej Los at 28-Sep-12 12:57pm
   
Good answer, my 5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 6,805
1 OriginalGriff 6,696
2 CPallini 5,350
3 George Jonsson 3,609
4 Gihan Liyanage 2,792


Advertise | Privacy | Mobile
Web03 | 2.8.140922.1 | Last Updated 28 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100