KNOWLEDGE BASE

Data and File Size Limitations with Jet-Based Data Sources


Product(s): Tableau Desktop, Tableau Public
Version(s): All
Last Modified Date: 16 Aug 2016

When you 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. This article focuses on limitations related to data and file size. For information about other types of limitations, see the Related Articles section in the right pane.

Note: Jet limitations might occur in workbooks created in Tableau Desktop 8.1 and earlier that use Excel and text file data sources, and workbooks that use the jet-based legacy connection and Microsoft Access data sources. To avoid Jet database engine limitations, consider upgrading your Excel or text file data sources. For more information, see Upgrading Data Sources in Tableau Desktop Help.

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. This is caused by a size limitation of 2GB for temporary files that Jet generates. If you get this error, check your Windows Temp folder for a .tmp file with a name that begins with JET and a file size of approximately 2GB.

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

    Note: Another cause of an "Unknown" error is outdated or corrupted Jet drivers. For more information, see the Related Articles section in the right pane.

General suggestions for resolving issues caused by Jet limitations

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.

If performance is slow after you have connected to the tables, you might want to take an extract.

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. You can find useful information and workarounds for these and other Jet-related issues on the Tableau community forums. See also Connect to Data and its sub-topics in the Desktop Help.

 

Alternate Search Terms:legacy connection, windows, file size
Did this article resolve the issue?