C# - format column when writing to CSV file(cell formatting)

Question

Have written the method for the exporting the data to csv files. If the value of the cell has DEC20 in csv is getting as 20-Dec which is not correct.

My code is like this:

for (int i = 0; i < myData.Count(); i++)
{
    sb.AppendLine(string.Join(delimiter, 
                              Common.FormatExportString(myData[i].Code),
                              Common.FormatExportString(myData[i].Name),
                              Common.FormatExportString(myData[i].Description)));
}
//returns the file after writing the stream to the csv file.
return File(new System.Text.UTF8Encoding().GetBytes(sb.ToString()), "text/csv", fileName);

Kindly help me to get the exact string format to be displayed in the excel (csv) file using C#.

Ex: myData[i].Name data will be

DEC20, or 2-5

like this,

but the output in csv(excel) is getting displayed as

20-Dec and 5-May

instead of the original one.

Answer 1

The problem is not concerning the csv exportation, if you open the csv file with notepad it is well formed. It is Excel that auto-detects the cell type as a date and shows it as a date.

To avoid this behavior wrap the text between quotes and put a = before it, as shown below:

= "DEC20"

The file should became as

= "field1", = "field2", = "field...", = "DEC20", ...

Your method should become:

for (int i = 0; i < myData.Count(); i++)
{
    sb.AppendLine(string.Join(delimiter, 
                              " = \"",
                              Common.FormatExportString(myData[i].Code),
                              Common.FormatExportString(myData[i].Name),
                              Common.FormatExportString(myData[i].Description)
                              "\""));
}

//returns the file after writing the stream to the csv file.
return File(new System.Text.UTF8Encoding().GetBytes(sb.ToString()), "text/csv", fileName);