PowerPivot - Import from text file with non-standard delimiter

Last week while teaching a PowerPivot course I was asked if it was possible to import data into a Power Pivot table from text files with non-standard delimiters.

The answer is – YES!!

ODBC Desktop Text File Driver

PowerPivot uses the Microsoft ODBC Desktop Database Drivers.

• The Text File Driver is the driver used to import data from text files.

The Text File Driver supports the use of a schema.ini file, located in the same folder as the text source file, which can be configured to define delimiters, language and data type settings.

schema.ini File

Here is a sample dat file called data1.txt:

Date!Name!Qty
01/01/2016!Fred!5
02/01/2016!Jim!10
03/01/2016!Mary!12
04/01/2016!Sue!15
05/01/2016!George!30
06/01/2016!Anne!20

As you can see the field delimiter for this file is an exclamation mark (!).

When you launch the Power Pivot import from text file the following delimiters are on offer as standard:

To successfully import the records from dat1.txt into a PowerPivot table we must create a schema.ini file in the same folder as data1.txt and the file should contain the following lines:

[data1.txt]
Format=Delimited(!)

When the flat file import is attempted again the Text File Driver will locate the section in the schema.ini file that matches the specified file name and will import accordingly. We can see in the file above that the delimiter has been set to an exclamation mark. The screen shot below shows that the fields have been interpreted correctly:

Although the dialog still shows a comma as the selected delimiter, the delimiter setting has been taken from the schema.ini file.

Want to learn more about Excel PowerPivot? Take a look at our Excel PowerPivot training course.