Auto-generating SCHEMA.INI
Power Pivot (and Tabular) uses the Access Connectivity Engine (ACE) provider to import from text files. This provider was originally designed for Microsoft Access databases and it’s limited to import files no larger than 2 GB (or 4 GB with the Office cumulative update from April 2012) as explained here. The Power Pivot Table Import Wizard allows you to import from delimited files and only a subset of delimiters are available for you to choose from.
However, the ACE provider supports a SCHEMA.INI file that can describe other formats, such as fixed-length files, or delimited files that use other delimiters, such as a double-pipe character “||”. If a SCHEMA.INI file exists in the same folder where the source file is located, Power Pivot will use the file.
Generating the schema file can be tedious, especially if the source file has many columns. However, the ODBC Data Source Administrator tool can auto-generate the file for you.
- Open ODBC Data Source Administrator (32 or 64 bit) and click Add.
- In the Create New Data Source, select the Microsoft Text Driver.
- Click Finish. In the ODBC Text Setup, specify the folder where the source file that you want to import from is located.
- Click the Define Format button.
- The tool is capable of interfering the format by clicking the Guess button.
- Once you click OK, the tool generates the schema.ini file in the same folder where the source file is located.
For some obscure reason that this support article refers to, you might get an error “Failed to save table attributes of (null) into (null)” but the schema.ini file will be still generated.
