# Import and Export CSV File This topic describes the properties to handle the spreadsheet documents (.csv files). ## Content DsExcel Java allows you to import data from CSV files into a workbook and export a workbook or worksheet to a CSV file. Use the [CsvOpenOptions](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html) and [CsvSaveOptions](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html) classes to configure how CSV data is read and written. ## Import and Export Options >type=note > **Note**: When importing and exporting CSV files, the [setSeparatorString](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setseparatorstring) method is obsolete. Use [setColumnSeparator](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setcolumnseparator), [setRowSeparator](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setrowseparator), and [setCellSeparator](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setcellseparator) instead. ### CSV Import Options | Class Name | Method Name | Description | | ---------- | ----------- | ----------- | | @rows=9:[CsvOpenOptions](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html) | [setConvertNumericData](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setconvertnumericdata) | This method can be used to set a value that indicates whether the string in text file is converted to numeric data. | | [setConvertDateTimeData](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setconvertdatetimedata) | This method can be used to set a value that indicates whether the string in text file is converted to date data. | | [setEncoding](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setencoding) | This method can be used to set the default encoding which is UTF-8. | | [setParseStyle](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setparsestyle) | This method can be used to specify whether the style for parsed values should be applied while converting the string values to number or date time. | | [setHasFormula](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#sethasformula) | This method can be used to specify whether the text is formula if it starts with "=". | | [setCellSeparator](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setcellseparator) | This method can be used to set the cell delimiter while opening CSV files. | | [setRowSeparator](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setrowseparator) | This method can be used to set the row delimiter while opening CSV files. | | [setColumnSeparator](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setcolumnseparator) | This method can be used to set the column delimiter while opening CSV files. | | [setParser](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setparser) | This method can be used to set a custom parser used when opening CSV content. | You can open a CSV file with the default settings or use [CsvOpenOptions](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html) to control how its values are imported. The following code opens CSV files with default and customized settings. ```java // Open a CSV file with the default settings. Workbook workbook = new Workbook(); workbook.open("test.csv", OpenFileFormat.Csv); // Open a CSV file with customized settings. Workbook workbookWithOptions = new Workbook(); CsvOpenOptions openOptions = new CsvOpenOptions(); openOptions.setConvertNumericData(false); openOptions.setConvertDateTimeData(false); openOptions.setParseStyle(false); workbookWithOptions.open("test.csv", openOptions); ``` ### CSV Export Options | Class Name | Method Name | Description | | ---------- | ----------- | ----------- | | @rows=8:[CsvSaveOptions](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html) | [setEncoding](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setencoding) | This method can be used to specify the default encoding which is UTF-8. | | [setValueQuoteType](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setvaluequotetype) | This method can be used to set how to quote values in the exported text file.
**Note:** DsExcel ignores this method when [setQuoteColumns](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setquotecolumns) method is set. | | [setTrimLeadingBlankRowAndColumn](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#settrimleadingblankrowandcolumn) | This method can be used to specify whether the leading blank rows and columns should be trimmed like in Excel. | | [setQuoteColumns](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setquotecolumns) | This method can be used to specify which column values will be in quotes while the values in the remaining columns are not. The column number starts at 0, and specifying an invalid column number has no effect. The column values in quotes are indicated by [setCellSeparator](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setcellseparator) method, which is typically set to double quote (") by default.
**Note:** If the value contains special characters such as quotes or separators, it will be in quotes always. | | [setCellSeparator](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setcellseparator) | This method can be used to set the cell delimiter while saving CSV files. | | [setColumnSeparator](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setcolumnseparator) | This method can be used to set the column delimiter while saving CSV files. | | [setEscapeFormulaLikeValues](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setescapeformulalikevalues) | This method can be used to set whether CSV values that begin with formula-like characters are escaped during export. | | [setRowSeparator](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setrowseparator) | This method can be used to set the row delimiter while saving CSV files. | You can export an entire workbook or a specific worksheet to a CSV file. The following code exports a workbook and a worksheet with the default settings. ```java // Create a workbook. Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.getWorksheets().get(0); // Export the workbook. workbook.save("workbook.csv", SaveFileFormat.Csv); // Export a specific worksheet. worksheet.save("worksheet.csv", SaveFileFormat.Csv); ``` The following code exports a CSV file using [CsvSaveOptions](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html) to customize the exported CSV content. ```java // Create a workbook. Workbook workbook = new Workbook(); // Configure CSV export options. CsvSaveOptions saveOptions = new CsvSaveOptions(); saveOptions.setValueQuoteType(ValueQuoteType.Always); saveOptions.setTrimLeadingBlankRowAndColumn(true); // Export the workbook with the specified options. workbook.save("export.csv", saveOptions); ``` ## Import with a Custom Parser DsExcel Java allows you to import CSV files using custom parsing rules to get results in a specific format. For instance, a cell with numeric type data is automatically parsed as a numeric cell. However, in some cases you want to load it as a string. In such cases, you can use this feature to define your own rules when you do not get the desired result with default parser settings of DsExcel. You can import CSV files with customized parsing rules by implementing [ICsvParser](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/ICsvParser.html) interface to define custom parsing rules using the [Parse](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/ICsvParser.html#Parse) method. The method accepts objects of [CsvParseResult](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvParseResult.html) and [CsvParseContext](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvParseContext.html) class as parameters. As CsvParseResult class represents the parsed text, you can pass the result generated by custom parsing rules to the CsvParseResult object and specify the location and text information of the target cell through the CsvParseContext class. Once the ICsvParser interface is implemented, pass it as the argument of the [setParser](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html#setParser) method of the [CsvOpenOptions](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html) class to get the expected results on importing the CSV file. ```Java // Create a new workbook. Workbook workbook = new Workbook(); // Create CsvOpenOptions and custom parser rules. CsvOpenOptions csvOpenOptions = new CsvOpenOptions(); csvOpenOptions.setParser(new CustomParser()); // Open csv file with option. workbook.open(fileStream, csvOpenOptions); public class CustomParser implements ICsvParser { @Override public void Parse(CsvParseResult csvParseResult, CsvParseContext csvParseContext) { if (csvParseContext.getText().startsWith("00")) { csvParseResult.setValue(csvParseContext.getText()); } else if(csvParseResult.getNumberFormat().equals("m/d/yyyy h:mm")){ csvParseResult.setNumberFormat("m/d/yyyy"); } } } ``` ## Prevent CSV Formula Injection When a CSV file is opened in a spreadsheet application, a value beginning with a formula-like character can be interpreted as a formula instead of plain text. This behavior can introduce CSV formula injection risks when the exported values contain untrusted or user-generated data. DsExcel Java provides the [CsvSaveOptions.setEscapeFormulaLikeValues](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setescapeformulalikevalues) method to specify whether an apostrophe(') is prefixed to formula-like values when exporting CSV files. The default value is **false**. When this method is set to **true**, DsExcel Java checks the first character of each exported value. If the first character is one of the following, an apostrophe is prefixed to the value: * Equal sign (`=`) * Plus sign (`+`) * Minus sign (`-`) * At sign (`@`) * Full-width variants of these characters * Tab(`\t`) * Carriage return(`\r`) * Line feed(`\n`) The following code exports formula-like values as text. ```java // Create a new workbook. Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("A1").setValue("=1+1"); worksheet.getRange("A2").setValue("+SUM(1,1)"); // Configure CSV export options. CsvSaveOptions saveOptions = new CsvSaveOptions(); saveOptions.setEscapeFormulaLikeValues(true); saveOptions.setValueQuoteType(ValueQuoteType.Always); // Save the CSV file. workbook.save("export.csv", saveOptions); ``` The value `=1+1` is changed to `'=1+1` before it is quoted and written to the CSV file. With `ValueQuoteType.Always`, the exported field is written as `"'=1+1"` instead of `"=1+1"`. >type=note > **Note**: [setEscapeFormulaLikeValues](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#setescapeformulalikevalues) does not change the behavior of [ValueQuoteType](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/ValueQuoteType.html) or [getQuoteColumns](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html#getquotecolumns). It sanitizes exported field text only when a dangerous leading character appears. The sanitized text then passes through the existing quoting logic. ## Configure Custom Delimiters DsExcel Java 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\. You can use the `ColumnSeparator`, `RowSeparator`, and `CellSeparator` method of the [CsvOpenOptions](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvOpenOptions.html) Class and [CsvSaveOptions](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CsvSaveOptions.html) Class to import and export the following three types of custom delimiters in CSV files. 1. **Column Delimiters** - These are the delimiters that separate the columns of a worksheet. By default, a column delimiter is of string type. 2. **Row Delimiters** - These are the delimiters that separate the rows of a worksheet. By default, a row delimiter is of string type. 3. **Cell Delimiters** - These are the delimiters that separate the cells of a worksheet. By default, the cell delimiter is of char type. The following code imports a CSV file with customized separator settings. ```java // Create a new 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"); ``` The following code exports worksheet data with customized separator settings. ```java // Create a new 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:F7").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); ```