Practical 15 – Importing External Data into Excel

1. Objective

The primary objective of this practical is to equip students with the skills to import various types of external data into Microsoft Excel for analysis and manipulation. Students will learn to:

·         Import data directly from web pages, transforming it into usable tables.

·         Import data from text files, correctly handling delimiters and data types.

·         Understand and utilize Excel’s "Get Data" capabilities for robust data import and transformation.

·         Clean and prepare imported data for further analysis.

·         Appreciate the importance of external data integration in real-world scenarios.

2. Introduction

In today’s data-driven world, information rarely resides neatly within a single Excel spreadsheet. Often, the data you need for analysis comes from diverse external sources—whether it’s financial statistics from a website, sales records from a CSV file, or textual reports from a Word document. Excel provides powerful tools to connect to these external sources, import the data, and even transform it to fit your analytical needs. Mastering data import is a critical skill for any data professional, enabling you to consolidate information and perform comprehensive analyses.

3. Pre-requisites

·         Basic understanding of Microsoft Excel interface.

·         Familiarity with basic data entry and formatting.

·         Access to the internet for web data import.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

We will use a new workbook for this practical to practice importing various data types.

Step 5.1: Opening a New Workbook and Overview of "Get Data"

1.     Open Excel: Launch Microsoft Excel and select Blank workbook.

2.     Rename Sheet: Double-click on Sheet1 and rename it to Imported Data.

3.     Explore "Get Data": Go to the Data tab on the Excel ribbon.

o    Observe the Get & Transform Data group. This is where Excel’s powerful Power Query tools reside, which allow you to connect to various data sources.

Step 5.2: Importing Data from a Web Page

We will import a table of countries and their populations from a Wikipedia page.

1.     Identify Web Source: Open a web browser and navigate to a Wikipedia page containing tabular data, for example, search for "List of countries by population" on Wikipedia. Look for a simple table.

2.     Copy URL: Copy the URL (web address) of the page from your browser’s address bar.

3.     Start Web Import:

o    In Excel, go to the Data tab.

o    In the Get & Transform Data group, click From Web.

4.     Enter URL: In the From Web dialog box, paste the copied URL into the URL field and click OK.

5.     Navigator Dialog Box: Excel will analyze the web page and display a Navigator dialog box showing available tables and sections.

o    In the left pane, browse through the suggested tables. Click on different table names to see a preview of the data in the right pane.

o    Find the table that contains the country and population data you want. For example, "Table 1" or a table clearly labeled "List of countries by population".

6.     Load or Transform:

o    If the preview looks good and requires no cleaning, click Load. The data will be imported directly into your Excel worksheet.

o    If the data needs cleaning or transformation (e.g., removing unnecessary columns, changing data types), click Transform Data. This will open the Power Query Editor.

§  Power Query Editor Example:

§  Remove unwanted columns: Select a column header, right-click, and choose Remove.

§  Change data type: Select a column header, click the data type icon (e.g., ABC or 123) in the header, and choose the correct data type (e.g., Whole Number for population).

§  When finished, click Close & Load on the Home tab of the Power Query Editor.

7.     Observation: The selected table will be imported into a new sheet or on your Imported Data sheet, formatted as an Excel Table.

Step 5.3: Importing Data from a Text/CSV File

CSV and TXT files are common formats for exchanging tabular data.

1.     Create Sample Text File:

o    Open Notepad or any text editor.

o    Type the following data, separating values with commas (for CSV) or tabs (for TXT):

o    ProductID,ProductName,Category,Price

o    101,Laptop,Electronics,1200

o    102,Mouse,Accessories,25

o    103,Keyboard,Accessories,75

104,Monitor,Electronics,300

o    Save the file as products.csv (or products.txt) to a location on your computer (e.g., your Desktop).

2.     Start Text/CSV Import:

o    In Excel, go to the Data tab.

o    In the Get & Transform Data group, click From Text/CSV.

3.     Select File: Navigate to where you saved products.csv (or products.txt), select it, and click Import.

4.     Data Transformation Preview: Excel will show a preview of your data.

o    Delimiter: Ensure the Delimiter dropdown is set correctly (e.g., Comma for CSV, or Tab for TXT). Excel usually auto-detects this.

o    Data Type Detection: Check if the column headers and data types are correctly identified.

5.     Load or Transform:

o    If the preview looks good, click Load. The data will be imported into your Excel worksheet.

o    If you need to make changes, click Transform Data to open the Power Query Editor. For instance, you might change the Price column to a Decimal Number type. Then, Close & Load.

6.     Observation: The product data will be imported into a new Excel Table.

Step 5.4: Refreshing Imported Data

When source data changes (e.g., a website updates, or a CSV file is modified), you can refresh your Excel table to get the latest information.

1.     Modify Source Data:

o    Open your products.csv file (from Step 5.3) in Notepad.

o    Add a new row: 105,Webcam,Accessories,50

o    Save the products.csv file.

2.     Refresh in Excel:

o    Go to the sheet containing your imported product data.

o    Click any cell within the imported table.

o    Go to the Data tab.

o    In the Queries & Connections group, click Refresh All.

3.     Observation: The new row "Webcam" should now appear in your Excel table.

Step 5.5: Importing from a Folder

This is useful for consolidating multiple similar files (e.g., monthly sales reports) from a single folder.

1.     Create Sample Folder & Files:

o    Create a new folder on your Desktop named Sales Reports.

o    Inside this folder, create two new CSV files:

§  JanSales.csv:

§  Date,Product,UnitsSold

§  2025-01-01,Laptop,5

2025-01-02,Mouse,10

§  FebSales.csv:

§  Date,Product,UnitsSold

§  2025-02-01,Keyboard,8

2025-02-02,Monitor,3

2.     Start Folder Import:

o    In Excel, go to the Data tab.

o    In the Get & Transform Data group, click Get Data > From File > From Folder.

3.     Browse for Folder: Navigate to and select your Sales Reports folder, then click OK.

4.     Folder Contents Preview: Excel will show a list of files in the folder.

5.     Combine & Transform: Click the Combine & Transform Data button.

o    This will open a dialog showing a sample file (JanSales.csv). Excel will try to detect delimiters and headers. Click OK.

o    This will open the Power Query Editor, where both files’ data will be combined and stacked.

6.     Close & Load: If the combined data looks correct, click Close & Load on the Home tab of the Power Query Editor.

7.     Observation: All sales data from both files will be imported into a single Excel table, along with a Source.Name column indicating which file each row came from.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks:

1.     Import Economic Data from Web:

o    Find a Wikipedia page (or similar reliable source) containing a table of GDP per capita for various countries.

o    Import this table into a new Excel worksheet named GDP Data.

o    Clean the data in Power Query Editor: remove any unnecessary columns (like ranks, references), and ensure the GDP values are correctly formatted as numbers.

o    Refresh the data to simulate an update.

2.     Import Student Contact List:

o    Create a text file (e.g., contacts.txt) with comma-separated values for 5 students, including StudentID,Name,Email,PhoneNumber.

o    Import this contacts.txt file into a new Excel worksheet named Student Contacts.

o    Ensure all columns are correctly parsed and data types are set.

3.     Combine Monthly Expenses:

o    Create a new folder named Expenses.

o    Inside, create expenses_Q1.csv and expenses_Q2.csv with the following structure:

§  Date,Category,Amount

§  2025-01-15,Food,50

§  2025-02-20,Transport,30

§  2025-03-10,Utilities,70

§  (for Q1)

§  2025-04-05,Food,60

§  2025-05-12,Entertainment,40

§  2025-06-25,Transport,35

§  (for Q2)

o    Import data from the Expenses folder, combining these two files into a single Excel table on a sheet named Combined Expenses.

o    Ensure Date and Amount columns have appropriate data types.

4.     Import from a Local Excel File (using Get Data):

o    Use your marksheet (from Practical 3) as a source.

o    On a new sheet, use Get Data -> From File -> From Excel Workbook to import the "Marksheet" table from your own Excel workbook.

o    Load it into a new sheet named Marksheet Import.

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsx file) containing the sheets for GDP Data, Student Contacts, Combined Expenses, and Marksheet Import, each with its correctly imported and cleaned data.

·         Be prepared to demonstrate how to import data from various sources (web, text/CSV, folder) and refresh the connections.

·         Be able to explain the role of the Power Query Editor in transforming data during the import process.

·         Explain the difference between Load and Transform Data options when importing.