KNOWLEDGE BASE

Error: "Database error 0x80004005: Field separator matches decimal separator or text delimiter"


Published: 17 Apr 2017
Last Modified Date: 17 Apr 2017

Issue

When working with Text files, the following error might occur: 
“Database error 0x80004005: The text file specification field separator matches decimal separator or text delimiter.”

Alternatively, you might see unexpected nulls when viewing your underlying data.

Environment

Tableau Desktop

Resolution

  • Create a schema.ini file: You can create a standard schema.ini file that will tell Jet how to read the CSV file, regardless of the regional settings. First, include a line that defines the current decimal separator as used in the CSV. For example, if the CSV uses a period as a decimal separator, type “DecimalSymbol=.”. Then, save the schema.ini file in the same directory as the CSV file. For more information about the schema.ini file, go to the MSDN website: https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver. Using the example above, the first two entries in your schema.ini file will look like the following:

    [filename.csv]
    DecimalSymbol=.

  • Replace the decimal separator:  Alternatively, replace the decimal separator in the CSV file with another character, such as a semicolon (;). To do this, use the find and replace feature in the text editor you use to open the CSV file, find all characters that are used as a decimal separator, such as a period, and replace with a semicolon.

  • Export the underlying data to Microsoft Excel or another data source: If the above two solutions do not work, consider exporting your data to another data source, such as Excel or SQL Server.

 

Cause

These issues can occur when the data source in your workbook is a comma separated values (CSV) file that was created on an operating system (OS) with an English locale and being accessed on an OS with a non-English locale.

The Microsoft Jet Database Engine, which Tableau uses to connect to a CSV file, is unable to directly handle certain values in the data. It cannot handle certain values when the format in the Regional and Language settings of the OS is set to use a character other than a period (.) as a decimal separator.
For example, your OS settings may be set to use a comma (,) for a decimal separator.

Additional Information

The Schema.ini file must be called Schema.ini and saved in the same folder as the CSV or tab-delimited file.

The Schema.ini file overrides any results from the initial Jet scan with the characteristics that you specify for a particular column, such as the data type or the number of decimal places in a column.
Did this article resolve the issue?