1.4 Step 1: Importing Data into Excel; Practice Ex. 1 Import an Access Database into Excel – Intro to Excel 2016 Data Analysis

1.4   Step 1: Importing Data into Excel

There are several ways to import data into Excel. Let’s begin by importing a Microsoft Access file. Then we will import a CSV (Comma Separated Values) file, and another Excel file.

When you import data into Excel, you don’t just copy the data once and lose the connection to the original file. Instead, Excel creates a data connection to the source and, if you tell it to do so, checks the file to see if its contents have changed and if your worksheet needs to be updated. You get to control how often Excel updates your worksheet data by displaying the external data range properties dialogue box.

​Note: Technically, we really haven’t imported data into an Excel workbook until we select the “Load” option in the Power Query Editor. We are only viewing the data. We don’t “officially” bring the data into an Excel workbook until we select the “Load” choice from the Power Query Editor menu.

​Practice Exercise 1: Import an Access Database table into Excel

STEP 1: Go up to the Ribbon and select the Data tab. In the “Get and transform data” section, select “Get Data” (FIGURE 1).

Picture

FIGURE 1
STEP 2A: ​On the drop-down menu, select the choice, “From Database” (FIGURE 2).

STEP 2B: On the secondary menu, select the choice, “From Microsoft Access Database” (FIGURE 2).


Picture

FIGURE 2
STEP 3: ​From the “Import Data” dialog box, select from the Data Files folder, the file named,  “XYZ COMPUTERS” (FIGURE 3).
This file is a Microsoft Access file. It has the filename extension, “accdb”

Picture

FIGURE 3
STEP 4: ​You are advised that the connection to the database file is being established (FIGURE 4).

Picture

FIGURE 4
STEP 5: ​The Navigation screen of the Power Query Editor appears (FIGURE 5).

Picture

FIGURE 5
STEP 6A: ​Click on the CUSTOMERS table shown on the left of the screen. We can now see the data from the CUSTOMERS table load into Microsoft Query (FIGURE 6).

Picture

FIGURE 6
STEP 6B: ​At the bottom of the Navigator screen, select the arrowhead next to the “Load” button. On the drop-down menu, select the “Load to” choice. This choice gives you options on where to load the CUSTOMERS table data. Selecting only the “Load” choice will only load the CUSTOMERS table into your current Excel worksheet (FIGURE 7).

Picture

FIGURE 7
STEP 7: ​When you select “Load To…” option, the “Import Data” dialog box appears. On the “Import Data” dialog box, select the following choices:

  • Table
  • Existing worksheet  =$A$1

 
Select the OK button (FIGURE 8).


Picture

FIGURE 8
STEP 8: ​Our Access data table, “CUSTOMERS” has been converted into an Excel data table and loaded into our currently open worksheet (FIGURE 9).

Picture

FIGURE 9
STEP 9A: ​Now we will rename the sheet. Move mouse to the “Sheet1” tab at the bottom of the Excel worksheet screen. Right-button mouse click “Sheet1” tab. You will see a menu appear as shown in FIGURE 10. Select “Rename” from the menu. 

Picture

FIGURE 10
STEP 9B: Notice that “Sheet1” is now highlighted, indicating that you may enter a replacement name by typing it in at this time. Enter “CUSTOMERS” as the replacement name, then press the “Enter” key (FIGURE 11).

Picture

FIGURE 11
END OF PRACTICE EXERCISE 1