How to Turn a Datatable to a CSV

How can I turn a DataTable to a CSV?

The following shorter version opens fine in Excel, maybe your issue was the trailing comma

.net = 3.5

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());

.net >= 4.0

And as Tim pointed out, if you are on .net>=4, you can make it even shorter:

StringBuilder sb = new StringBuilder(); 

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

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

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

As suggested by Christian, if you want to handle special characters escaping in fields, replace the loop block by:

foreach (DataRow row in dt.Rows)
{
    IEnumerable<string> fields = row.ItemArray.Select(field => 
      string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
    sb.AppendLine(string.Join(",", fields));
}

And last suggestion, you could write the csv content line by line instead of as a whole document, to avoid having a big document in memory.

Most efficient way of converting a DataTable to CSV

Use a System.Text.StringBuilder for huge strings - that's pretty fast.
I implemented this one:

public static string DataTableToCSV(this DataTable datatable, char seperator)
{
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < datatable.Columns.Count; i++)
    {
        sb.Append(datatable.Columns[i]);
        if (i < datatable.Columns.Count - 1)
            sb.Append(seperator);
    }
    sb.AppendLine();
    foreach (DataRow dr in datatable.Rows)
    {
        for (int i = 0; i < datatable.Columns.Count; i++)
        {
            sb.Append(dr[i].ToString());

            if (i < datatable.Columns.Count - 1)
                sb.Append(seperator);
        }
        sb.AppendLine();
    }
    return sb.ToString();
}

c# datatable to csv export

StringBuilder sb = new StringBuilder();

foreach (DataRow row in table.Rows)
{
    IEnumerable<object> fields = row.ItemArray.Skip(2);
    sb.AppendLine(string.Join(",", fields));
}
File.WriteAllText(@"C:\Desktop\test.csv", sb.ToString());

DataTable to csv file

I think the easiest way would be to create the csv files using JS on the client-side.

First of all, you will need to get the data from your DataTable, their documentation shows it could be done like this:

let table = $('#taulaMatriculaEstudiant').DataTable()
 
let data = table
    .rows()
    .data()

Now, csv format is basically a text format with separators. So you need to iterate over the rows in data and merge cells to separate text rows, something like this:

let text = '';
data.map( row => text += row.join( ';' ) + '\n' ) // replace ';' with your preferred CSV separator

Then you only need to create a document and download it. To do that I would suggest the usage of filesaver.js:

let blob = new Blob( [text], {type: "text/csv;charset=utf-8"} )
saveAs( blob, 'taulaMatriculaEstudiant.csv' ) // any name with CSV extension

Please note that I have never worked with DataTables, so I might be mistaken about the data structure in which it returns the data from table after .rows().data() (I assumed it returns an array of arrays that represent the cells in the rows). The general approach should be correct.

Edit

However I would not recommend sending data from the browser to the server - just create the correct file on the server side insted. E.g. like this:

@Controller // note it's not @RestController
public class CsvController {
    private CommonDataService dataService; // service with your table data logic. Don't forget to add @Service to it

    @GetMapping( "/csv/example" )
    public void exampleCsv( HttpServletResponse response ) throws IOException {
        byte[] processedFile = dataService.getTaulaMatriculaEstudiant(); // prepare your data the same way as your main data controller and create the csv table the way you like it

        ContentDisposition disposition = ContentDisposition.builder( "attachment" )
            .filename( "taulaMatriculaEstudiant.csv", StandardCharsets.UTF_8 )
            .build();
        response.setContentType( "text/csv" );
        response.setHeader( "Cache-Control", "no-cache" );
        response.setHeader( "Expires", "0" );
        response.setHeader( "Pragma", "no-cache" );

        IOUtils.copy( new ByteArrayInputStream( processedFile ), response.getOutputStream() ); // IOUtils from Apache Commons-IO
    } 

    @Autowired
    public void setDataService( CommonDataService dataService ) {
        this.dataService = dataService;
    }
}

Convert DataTable to CSV

You can export a DataTable object to CSV simply by piping the table into the Export-Csv cmdlet:

$table | Export-Csv C:\table.csv -NoType

However, by doing that you lose all type information of the table columns (the Export-Csv cmdlet can only save information about the type of the objects that represent the rows, not about the type of their properties).

A better way to save and restore a DataTable object is to save the table as XML:

$writer = New-Object IO.StreamWriter 'C:\path\to\data.xml'
$table.WriteXml($writer, [Data.XmlWriteMode]::WriteSchema)
$writer.Close()
$writer.Dispose()

and restore the XML into a DataSet:

$ds = New-Object Data.DataSet
$ds.ReadXml('C:\path\to\data.xml', [Data.XmlReadMode]::ReadSchema)
$table = $ds.Tables[0]

Make sure to export and import the schema along with the data, because that's where the type information is stored.

Convert DataTable to CSV stream

You can just write something quickly yourself:

public static class Extensions
{
    public static string ToCSV(this DataTable table)
    {
        var result = new StringBuilder();
        for (int i = 0; i < table.Columns.Count; i++)
        {
            result.Append(table.Columns[i].ColumnName);
            result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
        }

        foreach (DataRow row in table.Rows)
        {
            for (int i = 0; i < table.Columns.Count; i++)
            {
                result.Append(row[i].ToString());
                result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
            }
        }

        return result.ToString();
    }
}

And to test:

  public static void Main()
  {
        DataTable table = new DataTable();
        table.Columns.Add("Name");
        table.Columns.Add("Age");
        table.Rows.Add("John Doe", "45");
        table.Rows.Add("Jane Doe", "35");
        table.Rows.Add("Jack Doe", "27");
        var bytes = Encoding.GetEncoding("iso-8859-1").GetBytes(table.ToCSV());
        MemoryStream stream = new MemoryStream(bytes);

        StreamReader reader = new StreamReader(stream);
        Console.WriteLine(reader.ReadToEnd());
  }

EDIT: Re your comments:

It depends on how you want your csv formatted but generally if the text contains special characters, you want to enclose it in double quotes ie: "my,text". You can add checking in the code that creates the csv to check for special characters and encloses the text in double quotes if it is. As for the .NET 2.0 thing, just create it as a helper method in your class or remove the word this in the method declaration and call it like so : Extensions.ToCsv(table);

how to create CSV string from DataTable in VB.NET?

Create a generic method with DataTable, CSV Headers, DataTable Columns parameters:

    Private Function CSVBuilder(dt As DataTable, headers As List(Of String), columns As List(Of String)) As String
        Dim sCSV = New StringBuilder(String.Join(",", headers))
        sCSV.Append(Environment.NewLine)

        Dim view As New DataView(dt)
        Dim tDt As DataTable = view.ToTable(True, columns.ToArray)

        For Each row As DataRow In tDt.Rows
            '-- Handle comma
            sCSV.Append(String.Join(",", (From rw In row.ItemArray Select If(rw.ToString.Trim.Contains(","), String.Format("""{0}""", rw.ToString.Trim), rw.ToString.Trim))))
            sCSV.Append(Environment.NewLine)
        Next

        Return sCSV.ToString
    End Function

And then call in your code to get CSV string:

CSVBuilder(dataTable,
           New List(Of String) From {"Header Column 1", "Header Column 2", ...},
           New List(Of String) From {"DataTableColumn1", "DataTableColumn2", ...})

Export DataTable to CSV File with | Delimiter

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());

Converting DataSet/DataTable to CSV

There are several ways to do that.

One of the simplest (IMO) is using FileHelpers Library

FileHelpers.CsvEngine.DataTableToCsv(dataTable, filename);

---

Related Topics