Read and write CSV

The following example shows how to use GemBox.Spreadsheet to read, update, and write a CSV file in C# and VB.NET.

Upload your file (Drag file here)
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        // Read CSV file.
        var workbook = ExcelFile.Load("%InputFileName%", new CsvLoadOptions(CsvType.CommaDelimited));

        // Add new row.
        var worksheet = workbook.Worksheets[0];
        var row = worksheet.Rows[worksheet.Rows.Count];
        row.Cells[0].Value = "Jane Doe";
        row.Cells[1].Value = 3500;
        row.Cells[2].Value = 35;

        // Write CSV file.
        workbook.Save("Output.csv", new CsvSaveOptions(CsvType.CommaDelimited));
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        ' Read CSV file.
        Dim workbook = ExcelFile.Load("%InputFileName%", New CsvLoadOptions(CsvType.CommaDelimited))

        ' Add new row.
        Dim worksheet = workbook.Worksheets(0)
        Dim row = worksheet.Rows(worksheet.Rows.Count)
        row.Cells(0).Value = "Jane Doe"
        row.Cells(1).Value = 3500
        row.Cells(2).Value = 35

        ' Write CSV file.
        workbook.Save("Output.csv", New CsvSaveOptions(CsvType.CommaDelimited))

    End Sub
End Module

By default, GemBox.Spreadsheet will read CSV files using UTF8 encoding, iterate through records, take quoted and unquoted fields, and import their values as either a text or number to ExcelCell.Value.

You can use the CsvLoadOptions properties to change the encoding to another like Windows-1252, disable parsing number values, etc.

Note that apart from standard delimiters (comma, semicolon, tab), GemBox.Spreadsheet supports reading and writing CSV files with any custom char delimiter.

Read CSV files with formulas

CSV files usually only contain data, but they can contain formulas as well. To import CSV field values that start with the character '=' as ExcelCell.Formula, you'll need to set the CsvLoadOptions.HasFormulas property to 'true'.

The following example shows how to read a CSV file with formulas.

Upload your file (Drag file here)
using GemBox.Spreadsheet;
using System;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var csvOptions = new CsvLoadOptions(CsvType.CommaDelimited)
        {
            AllowNewLineInQuotes = true,
            HasQuotedValues = true,
            HasFormulas = true
        };

        // Read CSV file using specified CsvLoadOptions.
        var workbook = ExcelFile.Load("%InputFileName%", csvOptions);

        // Calculate Excel formulas from CSV data.
        var worksheet = workbook.Worksheets[0];
        worksheet.Calculate();

        // Iterate through read CSV records.
        foreach (var row in worksheet.Rows)
        {
            // Iterate through read CSV fields.
            foreach (var cell in row.AllocatedCells)
            {
                // Display just the first line of text from Excel cell.
                var value = cell.Value?.ToString() ?? string.Empty;
                Console.Write($"{value.Split('\n')[0],-25}");
            }

            Console.WriteLine();
        }
    }
}
Imports GemBox.Spreadsheet
Imports System

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim csvOptions As New CsvLoadOptions(CsvType.CommaDelimited) With
        {
            .AllowNewLineInQuotes = True,
            .HasQuotedValues = True,
            .HasFormulas = True
        }

        ' Read CSV file using specified CsvLoadOptions.
        Dim workbook = ExcelFile.Load("%InputFileName%", csvOptions)

        ' Calculate Excel formulas from CSV data.
        Dim worksheet = workbook.Worksheets(0)
        worksheet.Calculate()

        ' Iterate through read CSV records.
        For Each row In worksheet.Rows
            ' Iterate through read CSV fields.
            For Each cell In row.AllocatedCells
                ' Display just the first line of text from Excel cell.
                Dim value = If(cell.Value?.ToString(), String.Empty)
                Console.Write($"{value.Split(vbLf)(0),-25}")
            Next

            Console.WriteLine()
        Next

    End Sub
End Module
Opening and reading CSV file in C# and VB.NET
Screenshot of read data from a CSV file

Write CSV files with formatted values

When saving the ExcelFile to a CSV or TSV format, GemBox.Spreadsheet will write the cell's numeric and date values as textual data converted using the specified culture (CsvSaveOptions.FormatProvider).

However, you can specify the CsvSaveOptions.UseFormattedValues property instead to get the formatted cell values in the output CSV. For more information, see the Number Format example.

The following example shows how you can export formatted cell values and write them to a CSV file.

using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Sheet1");

        // Tabular sample data for exporting into a CSV file.
        var skyscrapers = new object[,]
        {
            { "Rank", "Building", "City", "Country", "Height (m)", "Height (ft)", "Floors", "Built" },
            { 1, "Burj Khalifa", "Dubai", "United Arab Emirates", 829.8, 2722, 163, 2010 },
            { 2, "Shanghai Tower", "Shanghai", "China", 632, 2073, 128, 2015 },
            { 3, "Abraj Al-Bait Towers", "Mecca", "Saudi Arabia", 601, 1971, 120, 2012 },
            { 4, "Ping An Finance Center", "Shenzhen", "China", 599, 1965, 115, 2016 },
            { 5, "Lotte World Tower", "Seoul", "South Korea", 555.7, 1823, 123, 2016 },
            { 6, "One World Trade Center", "New York City", "United States", 546.2, 1792, 104, 2014 },
            { 7, "Guangzhou CTF Finance Centre", "Guangzhou", "China", 530, 1739, 111, 2016 },
            { 7, "Tianjin CTF Finance Centre", "Tianjin", "China", 530, 1739, 98, 2018 },
            { 9, "China Zun", "Beijing", "China", 528, 1732, 108, 2018 },
            { 10, "Willis Tower", "Chicago", "United States", 527, 1729, 108, 1974 },
            { 11, "Taipei 101", "Taipei", "Taiwan", 508, 1667, 101, 2004 },
            { 12, "Shanghai World Financial Center", "Shanghai", "China", 494.3, 1622, 101, 2008 },
            { 13, "International Commerce Centre", "Hong Kong", "China", 484, 1588, 118, 2010 },
            { 15, "Central Park Tower", "New York City", "United States", 472.4, 1550, 103, 2020 },
            { 16, "Landmark 81", "Ho Chi Minh City", "Vietnam", 469.5, 1540, 81, 2018 },
            { 17, "Lakhta Center", "St. Petersburg", "Russia", 462, 1516, 86, 2018 },
            { 18, "John Hancock Center", "Chicago", "United States", 456.9, 1499, 100, 1969 },
            { 19, "Changsha IFS Tower T1", "Changsha", "China", 452, 1483, 94, 2017 },
            { 20, "Petronas Tower 1", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 },
            { 20, "Petronas Tower 2", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 },
            { 22, "Zifeng Tower", "Nanjing", "China", 450, 1476, 89, 2009 },
            { 22, "Suzhou IFS", "Suzhou", "China", 450, 1476, 98, 2017 },
            { 24, "The Exchange 106", "Kuala Lumpur", "Malaysia", 445.1, 1460, 95, 2018 },
            { 25, "Empire State Building", "New York City", "United States", 443.2, 1454, 102, 1931 },
            { 26, "Kingkey 100", "Shenzhen", "China", 442, 1449, 100, 2011 },
            { 27, "Guangzhou International Finance Center", "Guangzhou", "China", 438.6, 1445, 103, 2009 },
            { 28, "Wuhan Center", "Wuhan", "China", 438, 1437, 88, 2017 },
            { 29, "111 West 57th Street", "New York City", "United States", 435.3, 1428, 82, 2019 },
            { 30, "Dongguan International Trade Center 1", "Dongguan", "China", 426.9, 1401, 88, 2019 },
            { 31, "One Vanderbilt", "New York City", "United States", 427, 1401, 58, 2019 },
            { 32, "432 Park Avenue", "New York City", "United States", 425.5, 1396, 85, 2015 },
            { 33, "Marina 101", "Dubai", "United Arab Emirates", 425, 1394, 101, 2017 },
            { 34, "Trump International Hotel and Tower", "Chicago", "United States", 423.2, 1388, 96, 2009 },
            { 35, "Jin Mao Tower", "Shanghai", "China", 421, 1381, 88, 1998 },
            { 36, "Princess Tower", "Dubai", "United Arab Emirates", 414, 1358, 101, 2012 },
            { 37, "Al Hamra Tower", "Kuwait City", "Kuwait", 412.6, 1354, 80, 2010 },
            { 38, "Two International Finance Centre", "Hong Kong", "China", 412, 1352, 88, 2003 },
            { 39, "Haeundae LCT The Sharp Landmark Tower", "Busan", "South Korea", 411.6, 1350, 101, 2019 },
            { 40, "Guangxi China Resources Tower", "Nanning", "China", 402.7, 1321, 85, 2018 },
            { 41, "Guiyang Financial Center Tower 1", "Guiyang", "China", 401, 1316, 79, 2020 }
        };

        // Write data into Excel cells.
        int rowCount = skyscrapers.GetLength(0);
        int columnCount = skyscrapers.GetLength(1);
        for (int row = 0; row < rowCount; row++)
            for (int column = 0; column < columnCount; column++)
                worksheet.Cells[row, column].Value = skyscrapers[row, column];

        // Format Excel columns.
        worksheet.Columns["E"].Style.NumberFormat = @"0.0 \m";
        worksheet.Columns["F"].Style.NumberFormat = @"0,000 \f\t";

        var csvOptions = new CsvSaveOptions(CsvType.CommaDelimited)
        {
            UseFormattedValues = true
        };

        // Write CSV file using specified CsvSaveOptions.
        workbook.Save("Skyscrapers.csv", csvOptions);
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Sheet1")

        ' Tabular sample data for exporting into a CSV file.
        Dim skyscrapers = New Object(,) _
        {
            {"Rank", "Building", "City", "Country", "Height (m)", "Height (ft)", "Floors", "Built"},
            {1, "Burj Khalifa", "Dubai", "United Arab Emirates", 829.8, 2722, 163, 2010},
            {2, "Shanghai Tower", "Shanghai", "China", 632, 2073, 128, 2015},
            {3, "Abraj Al-Bait Towers", "Mecca", "Saudi Arabia", 601, 1971, 120, 2012},
            {4, "Ping An Finance Center", "Shenzhen", "China", 599, 1965, 115, 2016},
            {5, "Lotte World Tower", "Seoul", "South Korea", 555.7, 1823, 123, 2016},
            {6, "One World Trade Center", "New York City", "United States", 546.2, 1792, 104, 2014},
            {7, "Guangzhou CTF Finance Centre", "Guangzhou", "China", 530, 1739, 111, 2016},
            {7, "Tianjin CTF Finance Centre", "Tianjin", "China", 530, 1739, 98, 2018},
            {9, "China Zun", "Beijing", "China", 528, 1732, 108, 2018},
            {10, "Willis Tower", "Chicago", "United States", 527, 1729, 108, 1974},
            {11, "Taipei 101", "Taipei", "Taiwan", 508, 1667, 101, 2004},
            {12, "Shanghai World Financial Center", "Shanghai", "China", 494.3, 1622, 101, 2008},
            {13, "International Commerce Centre", "Hong Kong", "China", 484, 1588, 118, 2010},
            {15, "Central Park Tower", "New York City", "United States", 472.4, 1550, 103, 2020},
            {16, "Landmark 81", "Ho Chi Minh City", "Vietnam", 469.5, 1540, 81, 2018},
            {17, "Lakhta Center", "St. Petersburg", "Russia", 462, 1516, 86, 2018},
            {18, "John Hancock Center", "Chicago", "United States", 456.9, 1499, 100, 1969},
            {19, "Changsha IFS Tower T1", "Changsha", "China", 452, 1483, 94, 2017},
            {20, "Petronas Tower 1", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998},
            {20, "Petronas Tower 2", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998},
            {22, "Zifeng Tower", "Nanjing", "China", 450, 1476, 89, 2009},
            {22, "Suzhou IFS", "Suzhou", "China", 450, 1476, 98, 2017},
            {24, "The Exchange 106", "Kuala Lumpur", "Malaysia", 445.1, 1460, 95, 2018},
            {25, "Empire State Building", "New York City", "United States", 443.2, 1454, 102, 1931},
            {26, "Kingkey 100", "Shenzhen", "China", 442, 1449, 100, 2011},
            {27, "Guangzhou International Finance Center", "Guangzhou", "China", 438.6, 1445, 103, 2009},
            {28, "Wuhan Center", "Wuhan", "China", 438, 1437, 88, 2017},
            {29, "111 West 57th Street", "New York City", "United States", 435.3, 1428, 82, 2019},
            {30, "Dongguan International Trade Center 1", "Dongguan", "China", 426.9, 1401, 88, 2019},
            {31, "One Vanderbilt", "New York City", "United States", 427, 1401, 58, 2019},
            {32, "432 Park Avenue", "New York City", "United States", 425.5, 1396, 85, 2015},
            {33, "Marina 101", "Dubai", "United Arab Emirates", 425, 1394, 101, 2017},
            {34, "Trump International Hotel and Tower", "Chicago", "United States", 423.2, 1388, 96, 2009},
            {35, "Jin Mao Tower", "Shanghai", "China", 421, 1381, 88, 1998},
            {36, "Princess Tower", "Dubai", "United Arab Emirates", 414, 1358, 101, 2012},
            {37, "Al Hamra Tower", "Kuwait City", "Kuwait", 412.6, 1354, 80, 2010},
            {38, "Two International Finance Centre", "Hong Kong", "China", 412, 1352, 88, 2003},
            {39, "Haeundae LCT The Sharp Landmark Tower", "Busan", "South Korea", 411.6, 1350, 101, 2019},
            {40, "Guangxi China Resources Tower", "Nanning", "China", 402.7, 1321, 85, 2018},
            {41, "Guiyang Financial Center Tower 1", "Guiyang", "China", 401, 1316, 79, 2020}
        }

        ' Write data into Excel cells.
        Dim rowCount As Integer = skyscrapers.GetLength(0)
        Dim columnCount As Integer = skyscrapers.GetLength(1)
        For row As Integer = 0 To rowCount - 1
            For column As Integer = 0 To columnCount - 1
                worksheet.Cells(row, column).Value = skyscrapers(row, column)
            Next
        Next

        ' Format Excel columns.
        worksheet.Columns("E").Style.NumberFormat = "0.0 \m"
        worksheet.Columns("F").Style.NumberFormat = "0,000 \f\t"

        Dim csvOptions As New CsvSaveOptions(CsvType.CommaDelimited) With
        {
            .UseFormattedValues = True
        }

        ' Write CSV file using specified CsvSaveOptions.
        workbook.Save("Skyscrapers.csv", csvOptions)

    End Sub
End Module
Creating and writing CSV file in C# and VB.NET
Screenshot of written data to a CSV file

Read large CSV files

GemBox.Spreadsheet's content model has the same size limitation as Microsoft Excel, which is 1,048,576 rows and 16,384 columns per sheet.

Because of this, you cannot load a huge CSV file (with more than 1,048,576 records) into a single ExcelWorksheet. However, you can load every 1,048,576 records into a separate ExcelWorksheet by using a custom TextReader.

The following example shows how you can read a large CSV file with 5 million records in multiple sheets.

using GemBox.Spreadsheet;
using System;
using System.IO;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        // Create large CSV file.
        using (var csv = File.CreateText("large-file.csv"))
            for (int i = 0; i < 5_000_000; i++)
                csv.WriteLine(i);

        // Import all CSV data into multiple sheets.
        var workbook = LargeCsvReader.ReadFile("large-file.csv", LoadOptions.CsvDefault);

        // Display name and rows count of generated sheets.
        foreach (var worksheet in workbook.Worksheets)
            Console.WriteLine($"Name: {worksheet.Name} | Rows: {worksheet.Rows.Count:#,###}");
    }
}

public sealed class LargeCsvReader : TextReader
{
    private const int MaxRow = 1_048_576;
    private readonly TextReader reader;
    private readonly CsvLoadOptions options;

    private int currentRow;
    private bool finished;

    public static ExcelFile ReadFile(string path, CsvLoadOptions options)
    {
        var workbook = new ExcelFile();
        int sheetIndex = 0;

        using (var reader = new LargeCsvReader(path, options))
            while (reader.CanReadNextSheet())
                reader.ReadSheet(workbook, $"Sheet{++sheetIndex}");

        return workbook;
    }

    private LargeCsvReader(string path, CsvLoadOptions options)
    {
        this.reader = File.OpenText(path);
        this.options = options;
    }

    public override string ReadLine()
    {
        if (this.currentRow == MaxRow)
            return null;

        ++this.currentRow;
        string line = this.reader.ReadLine();
        if (line == null)
            this.finished = true;

        return line;
    }

    private void ReadSheet(ExcelFile workbook, string name)
    {
        var worksheet = ExcelFile.Load(this, this.options).Worksheets.ActiveWorksheet;
        workbook.Worksheets.AddCopy(name, worksheet);
    }

    private bool CanReadNextSheet()
    {
        if (this.finished)
            return false;

        this.currentRow = 0;
        return true;
    }

    protected override void Dispose(bool disposing) => this.reader.Dispose();
}
Imports GemBox.Spreadsheet
Imports System
Imports System.IO

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        ' Create large CSV file.
        Using csv = File.CreateText("large-file.csv")
            For i As Integer = 0 To 5_000_000 - 1
                csv.WriteLine(i)
            Next
        End Using

        ' Import all CSV data into multiple sheets.
        Dim workbook = LargeCsvReader.ReadFile("large-file.csv", LoadOptions.CsvDefault)

        ' Display name and rows count of generated sheets.
        For Each worksheet In workbook.Worksheets
            Console.WriteLine($"Name: {worksheet.Name} | Rows: {worksheet.Rows.Count:#,###}")
        Next

    End Sub
End Module

Public NotInheritable Class LargeCsvReader
    Inherits TextReader

    Private Const MaxRow As Integer = 1_048_576
    Private ReadOnly reader As TextReader
    Private ReadOnly options As CsvLoadOptions

    Private currentRow As Integer
    Private finished As Boolean

    Public Shared Function ReadFile(path As String, options As CsvLoadOptions) As ExcelFile
        Dim workbook As New ExcelFile()
        Dim sheetIndex As Integer = 0

        Using reader = New LargeCsvReader(path, options)
            While reader.CanReadNextSheet()
                sheetIndex += 1
                reader.ReadSheet(workbook, $"Sheet{sheetIndex}")
            End While
        End Using

        Return workbook
    End Function

    Private Sub New(path As String, options As CsvLoadOptions)
        Me.reader = File.OpenText(path)
        Me.options = options
    End Sub

    Public Overrides Function ReadLine() As String
        If Me.currentRow = MaxRow Then Return Nothing

        Me.currentRow += 1
        Dim line As String = Me.reader.ReadLine()
        If line Is Nothing Then Me.finished = True

        Return line
    End Function

    Private Sub ReadSheet(ByVal workbook As ExcelFile, ByVal name As String)
        Dim worksheet = ExcelFile.Load(Me, Me.options).Worksheets.ActiveWorksheet
        workbook.Worksheets.AddCopy(name, worksheet)
    End Sub

    Private Function CanReadNextSheet() As Boolean
        If Me.finished Then Return False

        Me.currentRow = 0
        Return True
    End Function

    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Me.reader.Dispose()
    End Sub
End Class
Opening and reading large CSV file in C# and VB.NET
Screenshot of resulting Excel sheets from imported CSV data

Write large CSV files

Similarly, you can save every ExcelWorksheet into one huge CSV file by using the same TextWriter object.

The following example shows how you can write multiple sheets with 5 million rows in total to a single large CSV file.

using GemBox.Spreadsheet;
using System;
using System.IO;
using System.Linq;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        // Create large ExcelFile.
        ExcelFile workbook = new ExcelFile();
        ExcelWorksheet worksheet = null;

        int max = 1_048_576;
        for (int index = 0; index < 5_000_000; index++)
        {
            int current = index % max;
            if (current == 0)
                worksheet = workbook.Worksheets.Add($"Sheet{index / max}");
            worksheet.Cells[current, 0].SetValue(index);
        }

        // Export multiple sheets into single CSV file.
        var options = SaveOptions.CsvDefault;
        using (var writer = File.CreateText("large-file.csv"))
            foreach (var sheet in workbook.Worksheets)
            {
                workbook.Worksheets.ActiveWorksheet = sheet;
                workbook.Save(writer, options);
            }

        // Display number of lines, or records, in generated CSV file.
        int csvLinesCount = File.ReadLines("large-file.csv").Count();
        Console.WriteLine($"Records: {csvLinesCount:#,###}");
    }
}
Imports GemBox.Spreadsheet
Imports System
Imports System.IO
Imports System.Linq

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        ' Create large ExcelFile.
        Dim workbook As New ExcelFile()
        Dim worksheet As ExcelWorksheet = Nothing

        Dim max As Integer = 1_048_576
        For index As Integer = 0 To 5_000_000 - 1
            Dim current As Integer = index Mod max
            If current = 0 Then worksheet = workbook.Worksheets.Add($"Sheet{index / max}")
            worksheet.Cells(current, 0).SetValue(index)
        Next

        ' Export multiple sheets into single CSV file.
        Dim options = SaveOptions.CsvDefault
        Using writer = File.CreateText("large-file.csv")
            For Each sheet In workbook.Worksheets
                workbook.Worksheets.ActiveWorksheet = sheet
                workbook.Save(writer, options)
            Next
        End Using

        ' Display number of lines, or records, in generated CSV file.
        Dim csvLinesCount As Integer = File.ReadLines("large-file.csv").Count()
        Console.WriteLine($"Records: {csvLinesCount}")

    End Sub
End Module
Creating and writing large CSV file in C# and VB.NET
Screenshot of the resulting CSV file from exported Excel sheets

See also


Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy