Export DataTable to CSV File with "|" Delimiter

Question

I am building an ASP.NET C# project that has a functionality to export DataTables to CSV files with a | delimiter for each cell. I want it to look like this:

100001|06/19/1861|Jose|Rizal|Filipino|Calamba, Laguna| 100002|08/30/1850|Marcelo|Del Pilar|Filipino|| 100003||||||

As you can see, each 'cell' is delimited by |. Furthermore, null cells should also have the delimiter. All rows should have the same number of | delimiter.

So far I have this code:

StringBuilder sb = new StringBuilder();
DataTable dtFile1 = file1BLO.SelectAllFile1();
foreach (DataRow dr in dtFile1.Rows)
{
      //build text file
}

Answer 1

You can try:

StringBuilder sb = new StringBuilder(); 

string[] columnNames = dt.Columns.Cast<DataColumn>().
                              Select(column => column.ColumnName).
                              ToArray();
sb.AppendLine(string.Join("|", columnNames));

foreach (DataRow row in dt.Rows)
{
    string[] fields = row.ItemArray.Select(field => field.ToString()).
                                ToArray();
    sb.AppendLine(string.Join("|", fields));
}

File.WriteAllText("test.csv", sb.ToString());

Answer 2

Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using System.IO;

namespace ConsoleApplication33
{
    class Program
    {
        const stating FILENAME = @"c:\temp\test.txt";
        static void Main(string[] args)
        {
            DataTable dtFile1 = new DataTable();
            StreamWriter writer = new StreamWriter(FILENAME);

            foreach (DataRow row in dtFile1.AsEnumerable())
            {
                writer.WriteLine(string.Join("|", row.ItemArray.Select(x => x.ToString())) + "|");
            }

        }

    }
}

Answer 3

I used to use this solution.

I exported 200k records and it took about 20 seconds.