Jollen MoyaniA comma-separated value (CSV) document is one of the most used formats for storing extensive data in a specific structure. It uses a comma (,) as the delimiter to separate each field in a row, and every row indicates a new record. Because CSV documents are text documents, many devices can read them without the need for extra software because they already have built-in applications that open and read text documents. Other document formats may require additional applications to open them.
CSV is sometimes called a character separator value document because the delimiter can be any character. However, the comma is most commonly used.
This article will show you how to import and export CSV data in your Excel files using the Syncfusion .NET Excel (XlsIO) library in C#.
Note: If you are new to our Excel Library, following the getting started guide is highly recommended.
Import CSV data to Excel
As mentioned previously, CSV documents can be opened easily on any device, but they don’t provide data management features. In such cases, you can import the CSV data into Excel files, which support opening CSV files with other options like sorting, filtering, and exporting to other formats.
Syncfusion Excel Library also supports opening CSV and all types of delimited documents.
Refer to the following code example to import a CSV document and apply filters to the data using the Excel Library.
using Syncfusion.XlsIO;
using System;
using System.IO;
namespace CsvToExcel
{
internal class Program
{
static void Main(string[] args)
{
using(ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(@"../../../Data/PurchasedItems.csv", FileMode.Open,FileAccess.ReadWrite);
//Opening CSV document with comma separator
IWorkbook workbook = application.Workbooks.Open(inputStream, ",");
IWorksheet worksheet = workbook.Worksheets[0];
//Applying filters to the data
IAutoFilters filters = worksheet.AutoFilters;
filters.FilterRange = worksheet[1, 1, worksheet.UsedRange.LastRow, worksheet.UsedRange.LastColumn];
IAutoFilter filter = filters[1];
filter.AddTextFilter("Wednesday");
//Saving the CSV data as Excel
FileStream outputStream = new FileStream(@"PurchasedItems.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(outputStream);
}
}
}
}
The following image shows the input CSV document.
Refer to the following image. It shows the output Excel document after applying filters to the imported CSV data using the Syncfusion Excel Library.
Export Excel to CSV
Similarly, you can export the previous Excel document again as a CSV document and share it with users using the Excel Library.
Refer to the following code example.
using Syncfusion.XlsIO;
using System;
using System.IO;
namespace ExcelToCSV
{
internal class Program
{
static void Main(string[] args)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(@"../../../Data/PurchasedItems.xlsx", FileMode.Open, FileAccess.ReadWrite);
//Opening CSV document with comma separator
IWorkbook workbook = application.Workbooks.Open(inputStream);
//Saving the Excel data as CSV
FileStream outputStream = new FileStream(@"PurchasedItems.csv", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(outputStream,",");
}
}
}
}
Refer to the following images.
Export CSV data with custom separator
In CSV documents, the commonly used separator is a comma (,), but this character can also be customized based on the user’s requirements.
Syncfusion Excel Library supports modifying the separator while saving a CSV document. However, the user must be aware of the separator character used in the CSV document before processing the document. Otherwise, the data cannot be read properly.
Refer to the following code example to export CSV data with a custom separator, a semicolon (;) in this case, using the Excel Library.
using Syncfusion.XlsIO;
using System;
using System.IO;
namespace CustomCsvSeparator
{
internal class Program
{
static void Main(string[] args)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(@"../../../Data/PurchasedItems.csv", FileMode.Open, FileAccess.ReadWrite);
//Opening CSV document with comma separator
IWorkbook workbook = application.Workbooks.Open(inputStream, ",");
//Saving the CSV data with separator as ";"
FileStream outputStream = new FileStream(@"PurchasedItems.csv", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(outputStream, ";");
}
}
}
}
Refer to the following images.
GitHub reference
Check out our importing and exporting CSV data in Excel using C# demo on GitHub.
Wrapping up
Thanks for reading! In this blog, we’ve seen how to import and export CSV data in C# using the Syncfusion Excel Library. Take a moment to peruse the documentation where you’ll find information on importing other data formats like DataTable, collection objects, DataView, DataColumn, and HTML, all with accompanying code samples.
Using the Excel Library, you can export Excel data to PDF, image, DataTable, CSV, TSV, HTML, collection objects, ODS, JSON, and other file formats.
Are you already a Syncfusion user? You can download the product setup here. If you’re not a Syncfusion user, you can download a free 30-day trial.
Please let us know in the comments section below if you have any questions about these features. You can also contact us through our support forum, support portal, or feedback portal. We are always happy to assist you!
