Loading External Data into Power Pivot - Microsoft Business Intelligence Tools for Excel Analysts - page 82

3. Fill in the following information and when you're finished, click Next.

Friendly Connection Name: This field allows you to specify your own name for the external
source. Enter a name that is descriptive and easy to read.

●

File Path: Enter the full path of your target text file. You can click the Browse button to
search for and select the file you want to pull from.

●

Column Separator: Select the character used to separate the columns in the text file. Before
you can do this, you need to know how the columns in your text file are delimited. For
example, a comma-delimited file has commas separating the columns. A tab-delimited file
has tabs separating the columns. Click the drop-down arrow next to the Comma Separator
field to see choices for the more common delimiters: Tab, Comma, Semicolon, Space,
Colon, and Vertical bar.

●

Use First Row as Column Headers: If your text file contains header rows, be sure to select
the Use First Row as Column Headers check box to make sure the column headers are
recognized as headers when imported.

●

After you click Next, you get an immediate preview of the data in the text file.

4. Filter any unwanted columns by removing the check next to the column names. You can also
click the drop-down arrows next to each column to apply any record filters.

5. Click the Finish button to start the import process.

Upon completion, the data from your text file will be part of the Power Pivot Data Model. As
always, be sure to review and create relationships to any other tables you've loaded into
Power Pivot.

Anyone who's worked with text files in Excel knows they are notorious for importing
numbers that look like numbers, but are really coded as text. In standard Excel, Text to
Columns fixes these kinds of issues. This can be a problem in Power Pivot, too. When
importing text files, you'll want to take the extra step of verifying that all columns have
been imported with the correct data formatting. You can use the formatting tools found
on the Power Pivot window's Home tab to format any column in the Data Model.

Note

Loading data from the clipboard
Power Pivot includes an interesting option for loading data straight from the clipboard — that is,
pasting data you've copied from some other place. This option is meant to be used as a one-off tech-
nique to quickly get useful information into the Power Pivot Data Model.