Documents for Excel .NET Edition | GCDocuments
File Operations / Import and Export CSV File with Delimiters
In This Topic
    Import and Export CSV File with Delimiters
    In This Topic

    GcExcel .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.

    C#
    Copy Code
    // 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.

    C#
    Copy Code
    // 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);