PowerPivot has the ability to aggregate and connect data from multiple sources. We will import some data from the Web into PowerPivot, which we may use later on in conjunction with the data imported from the database.
The web page http://data.worldbank.org/indicator/NY.GDP.PCAP.PP.CD contains historical GDP for each nation in the world, which can be simply downloaded as an Excel file. Open up the new Excel file using PowerPivot as shown in the following screenshot:
Select both the tables, and check the resulting sheet. Save as
GDPData.xlsx
for later usage.Similarly, download the unemployment rate data as an XLS (Excel) file from http://www.oecd-ilibrary.org/employment/employment-and-labour-markets-key-tables-from-oecd_20752342, which contains the historical unemployment rate of each OECD nation. This will not import to PowerPivot directly as the file is in the
.xls
format, but is based on another format (XML). Open it in Excel and save as a CSV file.Use PowerPivot to import the CSV file and save as
UnemploymentData.csv
.
Tip
Downloading the sample files
You can download the sample files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
PowerPivot has a built-in convertor that converts the data from various sources into PowerPivot data. These results can be saved separately as we have just performed and can be aggregated together at a later stage.
Similar to the data from the database, the changes made to the source files (that is, Excel files) may be updated in the PowerPivot file by simply clicking the Refresh button.
PowerPivot has limited support for certain sets of files, but these are more than enough for regular analysis.
Despite the fact that PowerPivot contains a tool called Importing from Text for the .txt
and .csv
files, and that we have manually created and used a .csv
file, these files are not readily used anymore as most institutions now use Excel in providing data.