This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Import External Data to PowerPivot
Problem: How do I get non-Excel data into PowerPivot?
PowerPivot can import from many databases, such as from SQL Server or even text files. In the PowerPivot window, choose From Text. You will have to browse to your text file, specify the field delimiter, and check the box for Use First Row as Column Headers.
Figure 952 Import data from a text file.
As you are importing data, you can choose to skip certain columns. If you are bringing in many million rows of data, skipping long text fields is a way to save space in the Excel file.
You can import data from any of these sources:
● SQL Server
● Sharepoint Reports
● Public data from Azure DataMarket
● ATOM Feeds
● Text Files
● SQL Azure
● IBM DB2
● OLEDB/ODBC data sources
For each data source type, you might need connect strings and/or credentials to connect to the data set.
Additional Details: You can import data to multiple worksheets in PowerPivot. You might have Excel data on one sheet, SQL Server on another sheet, and data from the Azure DataMarket on a third sheet.