Preview
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).
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).
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”
STEP 4: You are advised that the connection to the database file is being established (FIGURE 4).
STEP 5: The Navigation screen of the Power Query Editor appears (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).
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).
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).
STEP 8: Our Access data table, “CUSTOMERS” has been converted into an Excel data table and loaded into our currently open worksheet (FIGURE 9).
STEP 9: Move mouse to Sheet1 at the bottom of the Excel screen. Double-click the Sheet1 tab. 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 10).
END OF LESSON
Leave a Reply