How to Format CSV File

Question

1.00/5 (1 vote)

See more:

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

Darkness_07

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Martijn Kok · Answer 1 · 2012-09-28T05:44:00

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.