Preview
Importing Data into Excel
Practice Exercise 2: Import a Comman Separated Value (CSV) into Excel
STEP 1: Create a new sheet, Sheet1. Rename Sheet 1 to EMPLOYEES (FIGURE 1).
STEP 2: Go up to the Ribbon and select the Data tab.
Select the choice, “From Text/CSV” (FIGURE 2).
STEP 3: This is what the file looks like as a true text file. Excel parses the data so that it appears in the appropriate columns (FIGURE 3).
STEP 4: On the “Import Data” dialog box, from the data files folder, select the file: “XYZ COMPUTERS EMPLOYEES” (FIGURE 4).
STEP 5: In the Microsoft Query editor, select the “Load to” button to load the data into our Excel workbook (FIGURE 5).
STEP 6: On the “Import Data” dialog box, select the following choices: (FIGURE 6)
- Table
- Existing worksheet =$A$1
Select the OK button
STEP 7: The result of loading the CSV text file into Excel is an Excel table, shown below. The file type has changed from .csv to .xlsx (FIGURE 7).
STEP 8: Save Excel Book1 with new filename, “XYZ SWITCHBOARD.xlsx” (FIGURE 8).
STEP 9: To view all workbook/table connections, select the Data tab, in the Get & Transform Data Ribbon section, select “Existing Connections” (FIGURE 9).
STEP 10: Existing Connection dialog box has 2 tabs:
- Connections
- Tables
FIGURE 10 shows the Connections tab. This view shows all connections to this Excel workbook.
STEP 11: FIGURE 11 shows the Tables tab. This view shows only the tables connected to this workbook.
About External Data Connections
When external data connections are established, Excel maintains the connection so that future work with the file can continue without having to reload the file into Excel each time. This also ensures that we are using the correct table for our data that requires updating.
The main benefit of connecting to external data is that you can periodically analyze this data in Excel without repeatedly copying the data, which is an operation that can be time-consuming and error-prone. After connecting to external data, you can also automatically refresh (or update) your Excel workbooks from the original data source whenever the data source is updated with new information.
The advantages are simplification of your transactions; you know you are connecting to a data source previously encountered. It also ensures you are not making mistakes while moving data. See Appendix B of this Student Guide for more information about data connections.
Excel best practices with data is to create a file with multiple tabs, each of which has an external data connection. The user is in effect creating an Excel switchboard file (in our case it is the Excel file, SWITCHBOARD.xlsx. After your various external data sources have been updated, you can check these files for recent updates by selecting the “Refresh or Refresh ALL” icon on the :”Queries and Connections section of the Ribbon, as shown below in Figure 12:
Note: If an external data connection file has been moved, you must reimport the file to re-establish the data connection with the new location.
END OF LESSON
Leave a Reply