How to pragmatically set the excel number format to text for a csv file?

Question

I am wring a csv file with some program.

I am facing a issue when I open it in the excel file and for some column if it exceeds the value more than 256 it automatically truncate it.

I learnt that by default the excel has number format TO 'GENERAL' if I could set it to 'TEXT' then it wouldn't truncate any part.

So is there any programmatic way which can set the number format from GENERAL to TEXT.

I think you have a different problem, Excel can certainly read CSV files with values exceeding 256 in them. Please show us a few lines of your CSV file. — Mark Setchell, Sep 24 '14 at 11:30
See http://stackoverflow.com/questions/137359/excel-csv-number-cell-format — L-Four, Sep 24 '14 at 11:30
@MarkSetchell: you mean code you want to see. How i am doing it in csv file. — NoviceToDotNetWithAngular, Sep 24 '14 at 11:33
I mean you should click `edit` underneath your question and paste in 2-3 lines of your actual CSV that Excel will not import correctly. — Mark Setchell, Sep 24 '14 at 11:34
Ben robinson: i understand csv files are independent of excel or .txt but it does truncate in excel so i want not it to truncate and want to fix it in csv file itself. — NoviceToDotNetWithAngular, Sep 24 '14 at 11:35
Oh!!!! You don't mean the VALUE exceeds 256 - you mean the LENGTH of a text string exceeds 256 characters!!! Don't you? — Mark Setchell, Sep 24 '14 at 13:44
@MarkSetchell: yes, I mean the same..can we achieve it with C# code. — NoviceToDotNetWithAngular, Sep 25 '14 at 04:27

Philip Pittle · Answer 1 · 2014-09-25T12:11:23.687

csv is a plain text format. You can't embed meta-data such as column handling into that file format.

If you don't want excel to truncate the column, then you'll need to truncate it yourself when you write out the csv file:

 var csvFileBuffer = new StringBuilder();

 var columns = new List<string>();

 csvFileBuffer.AppendLine(
     string.Join(
         ",",
         columns.Select(s => 
              //truncate column header (change logic as appropriate)
              s.Substring(0, 255))));

That said, there may be an option you can set in excel which will change the default column type for a csv file. However, that would be a question for SuperUser.com

The other option is to write out to a native excel file format, such as xlsx. There are a number of tools you can use to this, such as the Open Xml Sdk.

No, by default excel truncating the text ahead of 255 i want not it to truncate. I want all data . — NoviceToDotNetWithAngular, Sep 24 '14 at 12:05
You wont be able to achieve that by adding a magic string to a `.csv' file — Philip Pittle, Sep 24 '14 at 12:11

How to pragmatically set the excel number format to text for a csv file?

1 Answers1