24.3 Importing Data from a Database

To import data, navigate to the Get External Data group in Power Pivot. To import data from a database, click From Database and select the type of database you’d like to import from. In this case, we will import from an Access database.

Figure 24.3: Power Pivot menu options.

If you haven’t already, download the XYZCorpSales2021 Access database, named XYZCorpSales2021.accdb, and select that database to import. In the Import Wizard dialog box that opens, you can either write your own custom structured query language (SQL) statements to select data from the database or simply select which tables you would like to import. The wizard also provides an option to Preview & Filter the data that is imported by selecting only specific columns or rows that match specified criteria.

Figure 24.4: The Select Tables and Views dialog allows you to select the tables to be imported and to Preview & Filter data before it is imported.

The video shows several different ways to filter data. Power Pivot can import millions of rows of data. In our case, we only have just over a hundred thousand rows.

Figure 24.5: Table Import Wizard successfully imported over 100,000 rows of data.

After importing the data into Excel, we can see the data presented in our workbook in a view very similar to how we might see data presented in another database management system. This presentation of the data in rows and columns is called the Data View.

Figure 24.6: Sample imported data.

To see the relationships between the data we’ve just imported, switch to the Diagram View.

Figure 24.7: Diagram View of imported data.

Now, you may have already built a query, or have one that could be easily adjusted that defines the data you’d like to import from a database into your workbook in Excel. Instead of selecting the desired data from lists of tables and views like we just demonstrated, we can write or utilize an already drafted query to import the desired data.

Begin by clicking Manage to return to the Data Model of the workbook. Click From Database, then select the type of database you’d like to use as the source of your data import. At this point, we choose how to import the data by selecting the radio button next to “Write a query that will specify the data to import” and clicking Next.

A window is then opened that includes space to write a SQL query. You can simply do this here, or you can click the Design button to import a pre-written query that is saved somewhere on your device. Follow the remaining prompts to import the desired data.

Previous Next