Import and Export CSV Files with Delimiters

DsExcel Java provides support for opening and saving CSV files with custom delimiters for rows, cells and columns. Users can use any custom character of their 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
   getColumnSeparator setColumnSeparator	These methods can be used to get or set the column delimiter while opening CSV files.
   getColumnSeparator setColumnSeparator	These methods 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
   getRowSeparator setRowSeparator	These methods can be used to get or set the column delimiter while opening CSV files.
   getRowSeparator setRowSeparator	These methods can be used to get or set the column delimiter while saving CSV files.

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

   Settings	Description
   getCellSeparator setCellSeparator	These methods can be used to get or set the column delimiter while opening CSV files.
   getCellSeparator setCellSeparator	These methods can be used to get or set the column 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();
        
// Setting ColumnSeparator, RowSeparator & CellOperator in CsvOpenOptions
CsvOpenOptions openOption = new CsvOpenOptions();
openOption.setColumnSeparator(",");
openOption.setRowSeparator("\r\n");
openOption.setCellSeparator('"');

// Opening csv in workbook
workbook.open("test.csv", openOption);

// Saving workbook to csv
workbook.save("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.getWorksheets().get(0);
Object data = new Object[][] { 
{ "Name", "City", "Birthday", "Sex", "Weight", "Height" },
{ "Bob", "NewYork", new GregorianCalendar(1968, 6, 8), "male", 80, 180 },
{ "Betty", "NewYork", new GregorianCalendar(1972, 7, 3), "female", 72, 168 },
{ "Gary", "NewYork", new GregorianCalendar(1964, 3, 2), "male", 71, 179 },
{ "Hunk", "Washington", new GregorianCalendar(1972, 8, 8), "male", 80, 171 },
{ "Cherry", "Washington", new GregorianCalendar(1986, 2, 2), "female", 58, 161 },
{ "Eva", "Washington", new GregorianCalendar(1993, 2, 5), "female", 71, 180 } };

// Set data
worksheet.getRange("A1:F5").setValue(data);
worksheet.getRange("A:F").setColumnWidth(20);

// Setting ColumnSeparator/ RowSeparator & CellOperator in CSVSaveOptions
CsvSaveOptions saveOption = new CsvSaveOptions();
saveOption.setColumnSeparator(",");
saveOption.setRowSeparator("\r\n");
saveOption.setCellSeparator('"');

// Saving workbook to csv
workbook.save("SaveCSVDelimiter.csv", saveOption);