Microsoft Office Tutorials and References
In Depth Information
The Use First Row As Column Headers check box indicates whether the first row of the file
contains the column names or not and works the same as the Excel data source. By default,
this check box is cleared even if the majority of the CSV files follows this convention and
contain the column header.
As soon as you ill the parameters, the grid shows a preview of the data as it will look in
PowerPivot; you can use the grid to select or deselect any column and to set row filters, as
you can with any other data source you have seen up to now. When you are done with the
setup, clicking Finish starts the loading process.
After the loading is finished, you still need to check whether the column types have been
correctly detected. CSV files do not contain, for instance, the type of each column, so PowerPivot
tries to guess the types by analyzing the file content. Clearly, as with any guess, it might fail to
detect the correct type.
In the example, PowerPivot detected the correct type of all the columns except the discount
column. PowerPivot missed that one because the lat file contains the percentage symbol
after the number, and so PowerPivot treats it as a character string and not a number. If you
need to change the column type, you can always do that later using the PowerPivot ribbon.
Note If the content of the CSV file needs to be adjusted in some way (as might be true of the
sample file we have been discussing, in which you would like to remove the percentage symbol
from the Discount column), you can always load the file using the standard Excel functions in an
Excel worksheet and then create a linked table. Having the data in an Excel file allows you to
modify the data to suit your needs. Always remember that PowerPivot tables are not updatable, so if
you need to make any kind of change, you can always use Excel tables as an intermediate step.
Loading from the Clipboard
Let us move forward in our description of the many ways to load data into PowerPivot and
analyze a way to load data without using any data source: the Clipboard. Using the Clipboard,
we can load data into PowerPivot from any application, as long as we are able to copy data
from that application to the Clipboard.
Let us suppose that you have a Microsoft Word table, like the one shown in Figure 5-34
(which you can also find in the companion Word document CH05-05-Word table.docx).