Import and Export CSV File with Delimiters

DsExcel .NET allows users to open and save CSV files with custom delimiters for rows, cells and columns. You can use any custom character of your choice as a delimiter. For instance - Comma (,) , Semicolon (;) , Quotes ( ", ' ) , Braces ( (), {} ), pipes ( | ), slashes (/ ), Carat ( ^ ), Pipe ( | ), Tab ( t ) etc.

Users can import and export the following three types of custom delimiters in CSV files as per their custom requirements and preferences. All these types of delimiters work independently and cannot be combined with each other.

1. Column Delimiters - These are the delimiters that separate the columns of a worksheet. By default, a column delimiter is of string type. Users can get or set the column delimiters using the options in the table shared below.

   Settings	Description
   CsvOpenOptions.ColumnSeparator	This property can be used to get or set the column delimiter while opening CSV files.
   CsvSaveOptions.ColumnSeparator	This property can be used to get or set the column delimiter while saving CSV files.

2. Row Delimiters - These are the delimiters that separate the rows of a worksheet. By default, a row delimiter is of string type. Users can get or set the row delimiters using the options in the table shared below.

   Settings	Description
   CsvOpenOptions.RowSeparator	This property can be used to get or set the row delimiter while opening CSV files.
   CsvSaveOptions.RowSeparator	This property can be used to get or set the row delimiter while saving CSV files.

3. Cell Delimiters - These are the delimiters that separate the cells of a worksheet. By default, the cell delimiter is of char type. Users can get or set the cell delimiters using the options in the table shared below.

   Settings	Description
   CsvOpenOptions.CellSeparator	This property can be used to get or set the cell delimiter while opening CSV files.
   CsvSaveOptions.CellSeparator	This property can be used to get or set the cell delimiter while saving CSV files.

Using Code

Refer to the following example code in order to import and export CSV files with delimiters using CsvOpenOptions class.

// Initialize workbook
Workbook workbook = new Workbook();
 
// Fetch default worksheet 
IWorksheet worksheet = workbook.Worksheets[0];
// Setting ColumnSeparator, RowSeparator & CellOperator in Open CSV options
var openOption = new CsvOpenOptions();
openOption.ColumnSeparator = ",";
openOption.RowSeparator = "\r\n";
openOption.CellSeparator = '"';
// Opening CSV in workbook
workbook.Open(@"test.csv", openOption);
// Saving workbook to CSV
workbook.Save(@"4-OpenCSVDelimeterRowColumnCell.csv");

Refer to the following example code in order to import and export CSV files with delimiters using CsvSaveOptions class.

// Initialize workbook
Workbook workbook = new Workbook();
 
// Fetch default worksheet 
IWorksheet worksheet = workbook.Worksheets[0];
object[,] data = new object[,]{
 {"Name", "City", "Birthday", "Sex", "Weight", "Height"},
 {"Bob", "NewYork", new DateTime(1968, 6, 8), "male", 80, 180},
 {"Betty", "NewYork", new DateTime(1972, 7, 3), "female", 72, 168},
 {"Gary", "NewYork", new DateTime(1964, 3, 2), "male", 71, 179},
 {"Hunk", "Washington", new DateTime(1972, 8, 8), "male", 80, 171},
 {"Cherry", "Washington", new DateTime(1986, 2, 2), "female", 58, 161},
 { "Eva", "Washington", new DateTime(1993, 2, 5), "female", 71, 180}};
// Set data
worksheet.Range["A1:F5"].Value = data;
worksheet.Range["A:F"].ColumnWidth = 20;
// Setting ColumnSeparator/ RowSeparator & CellOperator in Save CSV options
var saveOption = new CsvSaveOptions();
saveOption.ColumnSeparator = ",";
saveOption.RowSeparator = "\r\n";
saveOption.CellSeparator = '"';
// Saving workbook to CSV
workbook.Save(@"SaveCSVDelimiterRowColumnCell.csv", saveOption);