Format a column to text format in CSV file using C#

Question

I have an application, on which i export data from a datagrid to a csv file. I do this with the following steps:

Create a file:

var myFile = File.Create("test.csv"); myfile.Close();
write the data to a string builder(data)
write the data to the created file.

File.WriteAllText(filepath, data);

This works fine. The resulting csv file is opened in excel. I have a column of numbers which may have preceeding zeros, when those data is exported to csv file the preceeding 0's are lost. Is it possible to format the column as text column so the zeros are not lost.

One thing you could try is saving as text file then renaming to .csv — Maxqueue
– Maxqueue, Commented Jun 4, 2015 at 23:40
Your code should not trim leading zeros. Are you by any chance opening the resulting .csv file in Excel? Excel tends to do this. — HaukurHaf
– HaukurHaf, Commented Jun 4, 2015 at 23:40

HaukurHaf · Accepted Answer · 2015-06-04 23:45:53Z

2

View your file in notepad. The leading zeros are there, intact :-)

You need to tell Excel which format to use when you open the file. Change the file to .txt and use File -> Open in Excel and you should be presented with an import wizard. There you can explicitly tell Excel to treat your column as "text" which will prevent it from stripping leading zeroes.

More info here: http://www.howtogeek.com/howto/microsoft-office/how-to-import-a-csv-file-containing-a-column-with-a-leading-0-into-excel/

answered Jun 4, 2015 at 23:45

HaukurHaf

13.9k6 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

perplexedDev Over a year ago

Is there a way to automatically do this in C#, the users would see this as extra work and would expect this to be done on its own.

HaukurHaf Over a year ago

Keep in mind that your file is not really the problem, Excel is :-) However, I believe that if you prefix the leading zeros with a single quote character, Excel will no longer strip the leading zeros.

perplexedDev Over a year ago

Yes, I thought of that. But then again the users need to remove the quotes. I couldnt find any other solution and hence this post :)

HaukurHaf Over a year ago

No, that's the thing. Excel will not display the single quote :-) Try it!

perplexedDev Over a year ago

I tried it and excel displays the numbers with quotes. I did data.Append("'").Append(000123).Append("'");

|

Collectives™ on Stack Overflow

Format a column to text format in CSV file using C#

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related