0

In my application, I need to exporting datatable to CSV format file. I can do this with the following code except if the column is the string with leading zero suach as: 00254. After exporting to csv, I can see it display correct in Notepad but when open it by Excel, it lose all the leading zero: 00254 to 254 only.

Because datatable with some column is number such as : quantity and amount so we cannot convert all columns to string before export to CSV. 

var dsx = new DataSet();
dsx = ProcessData_Export(dtc);

IEnumerable<string> columnNames = dsx.Tables[0].Columns
    .Cast<DataColumn>().Select(column => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dsx.Tables[0].Rows)
{
    IEnumerable<string> fields = row.ItemArray
        .Select(field => Regex.Replace(field.ToString(), "\r\n", String.Empty));
    sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText(pathDesktop + "\\" + exFileName, sb.ToString(), Encoding.UTF8);
Sir Rufo
  • 18,395
  • 2
  • 39
  • 73
Cát Tường Vy
  • 398
  • 6
  • 32
  • 1
    Read [RFC 4180](https://tools.ietf.org/html/rfc4180) as it describes the CSV-Format. A field can include CR and LF but you have to quote the content. Rule of thumb: Quote all fields which content include the quote char, separator char, CR char or LF char. – Sir Rufo Oct 27 '18 at 06:30
  • *but when open it by Excel, it lose all the leading zero: 00254 to 254* do check whether Excel automatically converts your string into numbers.. last time i got ip address converted into numbers and they lose their separator :(. or try [this old solution](https://stackoverflow.com/a/165052/4648586), but it only specific for Excel.. for OpenOffice or LibreOffice, i set the column to text before importing to them. – Bagus Tesa Oct 27 '18 at 06:40
  • Possible duplicate of [C# convert int to string with padding zeros?](https://stackoverflow.com/questions/4325267/c-sharp-convert-int-to-string-with-padding-zeros) – derHugo Oct 27 '18 at 06:46
  • @derHugo *if the column is the **string** with **leading zero** such as: **00254*** - we are not talking about int columns to be exported as string with leading zeros. OP has a string column and the value contains only digits and the first digits are 0. When import by Excel this leading digits are removed – Sir Rufo Oct 27 '18 at 09:07

0 Answers0