KNOWLEDGE BASE

Limitations to Data and File Sizes with Jet-based Data Sources


Published: 11 Apr 2017
Last Modified Date: 21 Apr 2017

Issue

When trying to connect to a data source based on the Microsoft Jet database engine, such as Excel, Access or a plain text (CSV) file, you may encounter unexpected issues that are related to limitations in the Jet database engine.  Microsoft Jet technology is used with Tableau Desktop prior to 8.2 for Excel and CSV files or when using the legacy connector option with Tableau Desktop 8.2 and higher.

Known data or file size limitations

  • Working with large CSV files can be problematic.
    There is no explicit or documented file-size limit, but as a general guideline, keep a CSV file size smaller than 4GB. Even if you can connect to a CSV file larger than 4GB, taking an extract might cause an error such as

    Query too complex

    In addition, when you try to connect to a large CSV file, an error might occur that suggests that your computer does not have enough temporary disk space, even if it does.

  • Table size is limited to 255 columns (fields). In most cases, Jet fails when it reaches the 255 column limit.

  • Union contains more than 255 columns (fields). If a union contains more than 255 columns, the following error occurs: 

    Database error 0x800004005: Too many fields defined

    Note: Unions may contain more than 255 columns if using a UNION ALL statement in your custom SQL query. If using UNION ALL, the total number of columns in the union are equal to the number of columns counted each time the UNION ALL statement is used. This can cause the number of columns to exceed the column limit. For example, suppose you have UNION ALL on 3 tables with 102 columns each. The statement, SELECT * FROM "Table1" UNION ALL SELECT * FROM "Table2" UNION ALL SELECT * FROM “Table3” will count 306 columns and therefore exceed the Jet column limit.

  • Memo fields in Access data sources might be truncated after 255 characters.

  • Table and file names are limited to 64 characters. If a table or file name exceeds the 64-character limit, when you connect to the data source from Tableau, you might get any of the following error messages:
    • An error occurred while communicating with data source <data-source-file-name>… with an additional suggestion to check your permissions.
    • <data-source-file-name> is not a valid file name…. Check for punctuation or other invalid characters in the file name.
    • Unable to open the text file…. Check the file type and your permissions.
    • The table <table-name> does not exist.
  • Field names are limited to 255 characters. Although Jet can read fields with names that are longer, many operations that you perform on those fields cause an "Unknown" error. This limitation also causes fields to disappear without indication when you create an extract.

Environment

  • Tableau Desktop
  • Windows
  • Excel
  • Access
  • Text files (CSV)

Resolution

Workaround solutions

  • If the issue you have encountered is caused by a column, table, or file name that exceeds the limited number of characters, the most reliable way to resolve the issue is to change the name at the data source level.
  • If you have a data source that contains more than 255 columns, or the data source file size is exceptionally large, you might be able to successfully connect to your data source from Tableau if you first separate the table into smaller sections (each with fewer than 255 columns). Then in Tableau, connect using multiple tables and joins. To do this, you will need a field to use as a unique key for the join. For example, create a row number field converted to a string data type.
Note: Even if this workaround enables you to connect to the data, when you select View Data, you might get the following error: 
Database error 0x800004005: Too many fields defined.
  • If performance is slow after you have connected to the tables, you might want to make an extract.
  • If you get an error, that suggests that your computer does not have enough temporary disk space, even if it does, check your Windows Temp folder for a .tmp file with a name that begins with JET and a file size of approximately 2GB.

 

 

Cause

This is caused by a size limitation of 2GB for temporary files that Jet generates.

Additional Information


Jet limitations might occur in:
  • Workbooks created in Tableau Desktop 8.1 and earlier
  • Excel and text file data sources
  • Microsoft Access data sources
  • Workbooks that use the jet-based legacy connection
To avoid Jet database engine limitations, consider upgrading your Excel or text file data sources.

Note: Another cause of an "Unknown" error is outdated or corrupted Jet drivers.
Did this article resolve the issue?