Getting ready

A text file to simulate the output file for the sales record header has been created and is available from the online resources for this chapter. This file includes the sales_order_id, customer_id, employee_id, currency_id, customer_po_id, sales_territory_id, order_dt (order date), due_dt (due date), ship_dt (ship date), and sales_amount fields.

The file will be imported as a table into PowerPivot (and the tabular model). A screenshot of the file is shown as follows:

How to do It…

Most data imports can be managed from the PowerPivot window:

How it works…

Once the file name has been provided, the Table Import Wizard window uses a text driver to scan the first 200 rows of the file and determine the data type of each column. The text driver then uses this definition to import all the data for the file. The columns order_dt, due_dt, and ship_dt have been defined as dates, even though the file did not explicitly specify a date type (after all it is text).

Once the wizard has determined the data types for columns, it will use these types for the entire load. This may create issues where the value within the file does not conform to the data type specifications. In such a situation, the specific value will be discarded from the load. The row will be imported even if all the values are discarded. This type of situation can occur when a numeric value is expected, and a text value is found in the file, or the expected date format for the import cannot be derived from the underlying date value. For example, when the column format is DD-MM-YY, and the value follows the format MM-DD-YY.

The wizard can be defined for different delimiters. Tab, Comma, Semicolon, Space, Colon, and Vertical Bar (Pipe) are supported. This is regardless of the file extension (so you could import a .csv file with a Tab delimiter). However, the importer only allows you to import files that have extensions of .csv, .tab, or .txt. Additionally, you can specify if the first row of the file contains headings (and these are also imported).

There's more...

Although you can specify standard delimiters, the file may contain an unsupported character as its delimiter, and the data format chosen by the wizard may simply be the wrong type for the file. To overcome these issues (or the issues they may create), the text driver defaults (which are defined by the Wizard) can be overridden, so that the input file can be fully defined as load time. This is done by using a defining file titled schema.ini. This file needs to be saved in the same location as the file being imported. This offers the modeler full control over the specification of the import file. Consider the previous text import file with the following changes;

Repeat the recipe using the 02_master_tilde.csv file ensuring that the schema.ini file exists in the same directory. When the Wizard loads, an information box at the bottom of the window will indicate the presence of a schema.ini file, and this file will be used to specify import settings, as shown in the following screenshot:

The schema.ini file is a text file and can therefore be examined in notepad—a sample of our file is shown in the next screenshot. In order for it to be used, it must reside in the same folder as the file(s) being imported. The following are some universal notes about the structure of the file that should be mentioned:

Naturally, there can only be one schema.ini file in a directory. However, the same file can be used to specify the format of individual files. This is achieved by simply extending the next specification with the name of the file, as has been done for 02_master_tilde.csv.