Excel has only limited support for reading and writing text files. The XL Toolbox’ CSV commands facilitate importing and exporting CSV files with custom field separators and/or different number formats.

The XL Toolbox enables you to export an entire worksheet or just the selected range of cells, and define the decimal separator as well as the field separator. Column alignment is supported as well.

A CSV file is a file with comma-separated values:

Last name,first name,address
Doe,John,"123 Nice Street, Wonderful City"

The standard is defined in RFC4180, which explicitly requires the use of a comma (,) to separate the data fields. However, it is not uncommon for software packages to use alternative field separators such as a semicolon (;), especially in non-English speaking countries. Therefore, one might also call CSV a ”character-delimited values”, or ”delimiter-separated values” (DSV) format.

CSV Import

To open or import a CSV/DSV text file, click on the “Open CSV” button.

To set the field separator or the number format, click the little triangle to open the drop-down menu, and chooes “Open CSV with params…”:

CSV import settings

If you want to open a CSV file whose data fields are separated by TAB characters, enter \t as field separator.

Insider tip: use .txt extensions

The XL Toolbox internally uses Excel’s OpenText function. This function has a bug: It will not respect the settings for the field separator and number format unless the file being opened has a .txt extension.

When opening a file that does not have a .txt extension (for example, a .csv file), the XL Toolbox will make a copy of the file, give it a .txt extension, then open it using Excel’s OpenText function, and copy the worksheet to a newly created workbook.

Especially with very large files, you may want to enable directly opening the file by giving it a .txt extension.

CSV Export

You can export enire worksheets or just a range of cells to CSV files. To export with specific settings for field separator and number format, click on the little triangle to open the drop-down menu and choose the appropriate ‘… with params’ command:

CSV export settings

Tabularizing the CSV file

With the ‘Tabularize’ option checked off, the XL Toolbox will insert spaces (not tabs) to left-align the columns. The field separator will still be inserted between the columns. You may want to set it to one or more spaces to separate the columns.

Fields will not be escaped with quotation marks (") if the field separator is empty or consists of spaces.

Reuse your export range

When you export a range of cells to a CSV file, the XL Toolbox remembers your selection. You can export that same selection of cells again by not making a selection. If no range of cells is selected, the XL Toolbox attempts to restore the previous selection. This will work even if rows or columns have been deleted in the meantime.