Read and write CSV (or TSV) in C# and VB.NET
CSV and TSV are perhaps the simplest spreadsheet formats that can enable you to easily import and export tabular data or generate a lightweight report.
With GemBox.Spreadsheet, you can quickly and efficiently process delimited text files, with values that are separated by commas, tabs, pipes, or any other custom separator.
The following example shows how you can read, update, and write a CSV file in C# and VB.NET.
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
Read CSV files with formulas
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.
CSV files usually only contain data, but they can contain formulas as well. To import CSV field values that start with the letter '='
as ExcelCell.Formula
, you'll need to specify the CsvLoadOptions.HasFormulas
property.
The following example shows how you can read a CSV file and load its text, number, and formula values.

using System;
using GemBox.Spreadsheet;
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 System
Imports GemBox.Spreadsheet
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
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 into 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
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, see the Excel specifications and limits.
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 System;
using System.IO;
using GemBox.Spreadsheet;
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 System
Imports System.IO
Imports GemBox.Spreadsheet
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
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 System;
using System.IO;
using System.Linq;
using GemBox.Spreadsheet;
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 System
Imports System.IO
Imports System.Linq
Imports GemBox.Spreadsheet
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