How to read CSV files in other programs?

Symptoms

Most of modern text editors can detect encoding of a document automatically. Also, they have an option in the FileOpen dialog that allows to choose encoding: ANSI, Unicode or other. You should know for a fact the encoding of a document to use this option.

Cause

The DATAEXTRACTION, ATTEXT and TABLEEXPORT commands can generate CSV files that can be read in other software such as Excel. The result will be correct if one pays attention to the delimiter and the encoding.

Solution

Encoding: characters can be encoded in more than one way, and if a document is interpreted in the wrong encoding in Excel, LibreOffice or another editor, the characters display as completely different characters.

These are the encodings for the different extraction commands:

  • DATAEXTRACTION: UTF-8 with BOM
  • ATTEXT UTF-8 without BOM.
  • TABLEEXPORT: UTF-8 without BOM

Delimiter/separator: although CSV stands for comma separated file, the separator is not always a comma. If the import operation quietly assumes a different delimiter from the one in the CSV file, the import fails.

  • DATAEXTRACTION: the delimiter can be set in the extraction dialog
  • ATTEXT: delimiter can be set in the extraction dialog
  • TABLEEXPORT: the delimiter is set on OS level in regional settings.

Excel has several ways to read a CSV file. Sometimes just opening with the default settings will work, but if that fails, one can switch to another method that offers a dialog where the user can select the encoding and the delimiter explicitly:

  • Read Data: this is an import, and the resulting document will be saved as excel
  • Open: this will save to the original format
  • Double-click open