BISM : Importing Text Files into Power Pivot

A recent post has shown an issue with the import of some text files into powerpivot. It would appear that, for some fields the default import for text files assumes integer data types regardless of the underlying type. Because the import utility cannot be configured to specify the data type for fields, the data in columns are dropped from the import.

Text files are imported into via the [From Text] button of the ‘Get External Data’ section of the powerpivot ribbon. The import options are very limited and only specify the file name and generic file properties (delimiter and first row field headings). The advanced option only specifies text encoding.


This works where the fields to be imported are numeric types (as above) or capable of numeric conversion but not when the field contains a character type. For example; the following pictures show the outcome of text imports over a non-numeric field. Here, the first record (NA) is not imported and the other records are converted to numeric data.


After the import, specifying and reimporting the field as a text data type converts the field but does not import solve the dropping of non-numeric data.

To import the file with the expected type (and all data), we need to provide a data file with quoted data. For example, the following file will import correctly.