Most efficient way of converting a DataTable to CSV

Question

I'm working with DataTable's and I need to convert them to a CSV file format. Most of the tables I am working with have over 50,000 records so I'm trying to minimize the time it takes to convert them.

Here is my current method:

    public static string table_to_csv(DataTable table)
    {
        string file = "";

        foreach (DataColumn col in table.Columns)
            file = string.Concat(file, col.ColumnName, ",");

        file = file.Remove(file.LastIndexOf(','), 1);
        file = string.Concat(file, "\r\n");

        foreach (DataRow row in table.Rows)
        {
            foreach (object item in row.ItemArray)
                file = string.Concat(file, item.ToString(), ",");

            file = file.Remove(file.LastIndexOf(','), 1);
            file = string.Concat(file, "\r\n");
        }

        return file;
    }

Is there any way I can improve the efficiency of this method? I'm welcome to any modifications and ideas that you have!

Highams

Since your code is working and you're not facing problems with it, maybe Code Review is better place for your question than StackOverflow. – Citizen

Why are you loading so many rows in the first place? It would be easier to use the database's tools (like SSIS in SQL Server) to export data to CSVs. Or use a DataReader to read data in a firehose mode and write them to a file. Creating the entire string in memory is not efficient either - use Write and WriteLine to write them out to a file. Moreover, you can replace repeated concatenations with String.Join, although using Write makes it a bit redundant as well – Toein

possible duplicate of Convert DataTable to CSV stream – Toein

I don't think the answers are a good fit for CodeReview, and I don't think this is off-topic here. trying to minimize the time it takes to convert them sounds like a problem statement to me, and that might just be on-topic here. a migration would be declined – Ryswick

Answer 1

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

Cateyed

This answer meets my requirements, a DataTable containing over 30k records used to take minutes, now takes under 1 second. Thanks. – Highams

how to convert dataset to csv ? – Niobe

@AkhilJain if you have multiple Tables within your DataSet - you can't. If you have one Table then string result = DataTableToCSV(myDataSet.Tables[0], ','); – Cateyed

This is a fantastic solution - just wanted to say thanks for helping me out a bunch! – Yugoslav

Answer 2

Here's a method I have in my Utility class. Works well for what I'm doing.

 public static void GenerateCSV(DataTable dt)
    {  
        StringBuilder sb = new StringBuilder();
        try
        {
            int count = 1;
            int totalColumns = dt.Columns.Count;
            foreach (DataColumn dr in dt.Columns)
            {
                sb.Append(dr.ColumnName);

                if (count != totalColumns)
                {
                    sb.Append(",");
                }

                count++;
            }

            sb.AppendLine();

            string value = String.Empty;
            foreach (DataRow dr in dt.Rows)
            {
                for (int x = 0; x < totalColumns; x++)
                {
                    value = dr[x].ToString();

                    if (value.Contains(",") || value.Contains("\""))
                    {
                        value = '"' + value.Replace("\"", "\"\"") + '"';
                    }

                    sb.Append(value);

                    if (x != (totalColumns - 1))
                    {
                        sb.Append(",");
                    }
                }

                sb.AppendLine();
            }
        }
        catch (Exception ex)
        {
            // Do something
        }
    }

Rodman

Up voted as this solution also checks and escapes the strings when required. – Peahen

Very useful answer. Up voted as this solution also considers escapes. – Servility

Answer 3

I have used this method which copies object array to an Excel cell range rather than copying row by row and then column by column & it proved to be quite efficient way

    public void ExportToExcel(DataTable dataTable, String pathToSave)
    {
        // Create the Excel Application object
        var excelApp = new ApplicationClass();

        // Create a new Excel Workbook
        Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

        int sheetIndex = 0;

        // Copy the DataTable to an object array
        var rawData = new object[dataTable.Rows.Count + 1, dataTable.Columns.Count];

        // Copy the column names to the first row of the object array
        for (var col = 0; col < dataTable.Columns.Count; col++)
        {
            rawData[0, col] = dataTable.Columns[col].ColumnName;
        }

        // Copy the values to the object array
        for (var col = 0; col < dataTable.Columns.Count; col++)
        {
            for (int row = 0; row < dataTable.Rows.Count; row++)
            {
                rawData[row + 1, col] = dataTable.Rows[row].ItemArray[col];
            }
        }

        // Calculate the final column letter
        string finalColLetter = string.Empty;
        const string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        int colCharsetLen = colCharset.Length;

        if (dataTable.Columns.Count > colCharsetLen)
        {
            finalColLetter = colCharset.Substring(
                (dataTable.Columns.Count - 1) / colCharsetLen - 1, 1);
        }

        finalColLetter += colCharset.Substring((dataTable.Columns.Count - 1) % colCharsetLen, 1);

        // Create a new Sheet
        var excelSheet = (Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.Item[++sheetIndex], Type.Missing, 1, XlSheetType.xlWorksheet);
        excelSheet.Name = dataTable.TableName;

        // Fast data export to Excel
        var excelRange = string.Format("A1:{0}{1}", finalColLetter, dataTable.Rows.Count + 1);
        excelSheet.Range[excelRange, Type.Missing].Value2 = rawData;

        // Mark the first row as BOLD and BLUE
        var headerColumnRange = (Range)excelSheet.Rows[1, Type.Missing];
        headerColumnRange.Font.Bold = true;
        headerColumnRange.Font.Color = 0xFF0000;
        headerColumnRange.EntireColumn.AutoFit();

        // Save and Close the Workbook
        excelWorkbook.SaveAs(pathToSave, XlFileFormat.xlWorkbookNormal, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        excelWorkbook.Close(true, Type.Missing, Type.Missing);
        excelWorkbook = null;

        // Release the Application object
        excelApp.Quit();
        excelApp = null;

        // Collect the unreferenced objects
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }

Mitzi

Answer 4

I found this code which uses Regex does a good job of covering all of the situations for special characters embedded in the data. Once you're comfortable with the actions, feel free to remove the temp variable.

        public static BaseResponseModel<string> DataTableToCsv(bool writeHeaders, DataTable dtIn)
    {
        BaseResponseModel<string> response = new();
        StringBuilder sbData = new StringBuilder();
        try
        {
            if (writeHeaders)
            {
                foreach (var col in dtIn.Columns)
                {
                    if (col == null)
                        sbData.Append(",");
                    else
                        sbData.Append(QuoteValue(col.ToString()!) + ",");
                }

                sbData.Replace(",", System.Environment.NewLine, sbData.Length - 1, 1);
            }

            foreach (DataRow dr in dtIn.Rows)
            {
                foreach (var column in dr.ItemArray)
                {
                    if (column == null )
                    {
                        sbData.Append(",");
                    }
                    else
                    {
                        string? temp = column!.ToString();
                        if (temp == null)
                        {
                            sbData.Append(",");
                        }
                        else
                        {
                            if (column.GetType() == typeof(string))
                            {
                                    sbData.Append(QuoteValue(temp) + ",");
                            }
                            else
                            {
                                sbData.Append(temp + ",");
                            }
                        }
                    }
                        
                }
                sbData.Replace(",", System.Environment.NewLine, sbData.Length - 1, 1);
            }

            response.Data = sbData.ToString();
        }
        catch (Exception ex)
        {
            response.AddException(ex);
        }
        
    Done:
        return response;
    }

    public static string QuoteValue(string value)
    {
        // https://datatracker.ietf.org/doc/html/rfc4180
        if (string.IsNullOrEmpty(value)) { return value; }
        // remove any enclosing quotes
        value = Regex.Replace(value, "^\"(.*)\"$", "$1", RegexOptions.IgnoreCase | RegexOptions.Multiline);
        // double any single internal quotes
        value = Regex.Replace(value, "([^\"])\"([^\"])|([^\"])(\"$)", "$1\"\"$2", RegexOptions.IgnoreCase | RegexOptions.Multiline);
        // add enclosing quotes if internal comma or linefeed
        if (Regex.Count(value, ",|(\r?\n)") > 0)
        {
            value = '"' + value + "\"";
        }
        return value;
    }

Justis