Connect to a Flat File from Power Pivot

If you're using the Power Pivot add-in to import data into a data model, one of the data sources you can import is a flat file. The Table Import Wizard lets you connect to a flat file (.txt), tab-separated file (.tab), or a comma-separated file (.csv), to import file contents into a table in the model. More about Get data using the Power Pivot add-in.

1. In Power Pivot, click Home > Get External Data > From Other Data Sources.

2. Scroll down and choose Text File.

3. In Friendly Connection name, type a unique name for this data source connection. This is a required field.

4. In File Path, specify a full path for the file.

5. Click Browse to navigate to a location where a file is available.

6. In Column Separator, choose the separator that separates values into columns.

7. Click Advanced to specify encoding and locale options for the flat file.

8. Check the Use first row as column headers box if the first row is a column name.

Preview the data in the selected file, and use the following options to modify the data import.

Note:  Only the first 50 rows in the file are displayed in this preview.

1. Clear the checkbox on columns you want to exclude from the import.

2. Click the down-arrow button in the column filter to sort and filter the data. For example, if the column contains product category names, you can select individual categories to import.

3. Click OK to return to the Table Import wizard.

4. Click Finish to import.

Flat files are imported as a single table in the data model. If another table in the model has a column that contains identical values, you can relate the tables and use them simultaneously in the same PivotTable or Power View report.

For example, suppose you have two flat files: one contains a list of companies and another contains a list of advertisers. If both files include the company name, you can use the Name column as the basis for relating the two tables in your model. More about Create a relationship between two tables or Create relationships in Diagram View.