Microsoft Office Tutorials and References
In Depth Information
Note Let us review the difference between a linked table and an Excel import. Linked tables
need to be in the same Excel workbook in which the PowerPivot database is stored, which might
be a limitation in cases where you already have files containing all the relevant information. On
the other hand, when you use the Excel import feature, you can load data that resides in different
Excel worksheets. Always bear in mind that loading data does not create a link between the two
files: the process of importing data into PowerPivot creates a copy of it, so data is not refreshed
when you update the original Excel file. If you want to refresh data, you need to do it manually,
by using the Refresh button in PowerPivot.
Loading from Text Files
As you can see, the list of possible data sources for PowerPivot is growing very quickly. The
PowerPivot team has spent a lot of time to make it possible for you to load data from many
different sources because the main goal of PowerPivot is integration. It is now time to analyze
another possible source of data: text files.
Data can be stored in text files in a format know as Comma Separated Values (CSV). This
widespread format represents data as normal text lines, each one containing a row. Each
row contains columns, separated by commas (hence the name of the format).
If you have a CSV file containing some data (see the companion file CH05-04-2005_Plans.txt),
you can import it into PowerPivot using the text file data source. Let us suppose that our CSV
file contains the special offers planned for the year 2005, (the same data that we used to show
the Excel data source). It looks like this:
You can see that each line contains a row, and each row contains columns, each separated by
a comma from the next. Usually CSV files contain the column header in the first row of the
file so that the file contains both the data and the column names. This is the same standard
we normally use with Excel tables.
To load this file into PowerPivot, you need to use the From Text button in the PowerPivot
window. This action opens the Table Import Wizard for text files (see Figure 5-33), for which
you need to provide several parameters.