1.4.1 Importing Data into Excel; Practice Ex. 2 Import a Comma Separated Value Text File into Excel – Intro to Excel 2016 Data Analysis

1.4.1 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).

Picture

FIGURE 1
STEP 2: ​Go up to the Ribbon and select the Data tab.
Select the choice, “From Text/CSV” (FIGURE 2).

Picture

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).

Picture

FIGURE 3
STEP 4: ​On the “Import Data” dialog box, from the data files folder, select the file: “XYZ COMPUTERS EMPLOYEES” (FIGURE 4).

Picture

FIGURE 4
STEP 5: ​In the Microsoft Query editor, select the “Load to” button to load the data into our Excel workbook (FIGURE 5).

Picture

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


Picture

FIGURE 6
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).

Picture

FIGURE 7
STEP 8: ​Save Excel Book1 with new filename, “XYZ SWITCHBOARD.xlsx” (FIGURE 8).

Picture

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).

Picture

FIGURE 9
STEP 10: ​Existing Connection dialog box has 2 tabs:

  1. Connections
  2. Tables

 
FIGURE 10 shows the Connections tab. This view shows all connections to this Excel workbook. 


Picture

FIGURE 10
STEP 11: FIGURE 11 shows the Tables tab.  This view shows only the tables connected to this workbook.

Picture

FIGURE 11
END OF PRACTICE EXERCISE #2