There are six common import errors for Excel (XLSX) files.
1. File size
In Portal 2.0, due to GraphQL limitations on `Int` values, it is generally best to keep file sizes under 1GB. Also keep in mind that larger files will take longer to process, and that the speed of processing will be dependent on your local hardware.
For Portal 3.0 customers, the main issue with large files is upload speeds. Because of browser limitations, files larger than 250mb will take longer to upload. However, since Portal 3.0 processes files on our servers, once uploaded, the files are typically processed much faster.
For more information about file sizes, see this Knowledge Base article.
You may run into matching errors when columns don’t match the expected field names in your data template. It may be that the field names are not on the first line, your file completely lacks column names, or your data is missing required fields completely.
Further complicating things in Excel documents is the fact that sometimes there are multi-row field names instead of single-row. Let’s say your column headers are North, South, East, and West, but they’re a subset of another header—Direction. The actual data, then, is buried under a secondary header, and that will create an Excel import error.
3. Data Translation
You may run into data translation errors if the encoding is incorrect or unexpected. Special or non-standard characters may create an encoding error, even if the Excel file is saved with UTF-8 encoding.
Incorrect or unexpected values in fields may cause an error when uploading Excel files. You can learn more about field types here, but, in short, you should have boolean values in boolean fields, numbers in number fields, strings in string fields, and so on.
Sometimes, Excel will treat text as if it is a number, and will, for example, strip out leading zeros from ZIP codes or GPS coordinates.
5. Missing Data
Missing data is one of the most common errors for Excel imports. You may be missing date information from invoices, ZIP codes from addresses, or original list dates from real estate sales data. In some cases, this missing data can be automatically appended, while in other cases, incomplete data must be pulled by a person from one system to another. Any required value that is missing will be flagged as a required field and trigger an Excel import error.
6. Non-digestible formats
Non-digestible formats include simple format mismatches and complex format mismatches. Simple format mismatches need format normalization, such as phone numbers, social security numbers, or ZIP codes, and are typically numbers only, without symbols. Complex format mismatches require format and data normalization, and include things like dates and addresses.
This is a common issue with both CSV and Excel files, however, Excel’s powerful formatting features often create data in a format that is non-digestible.
Charts and pivot tables are the most common examples of non-digestible data from Excel. Though they are easy to create in Excel, they cannot be imported.