Advanced Excel: exercises

Module 5: Advanced Data Import and Export

Exercise 15: Using Downloaded JSON Data for Data

Connections
Objective: Import JSON data from a web source into Excel and transform it.

Step 1: Download the JSON Data

1.​ Open your web browser and navigate to:[Link]

2.​ Save the data:
○​ On the webpage, press Command + S (Mac shortcut for "Save As").
○​ Choose a location (e.g., your Desktop) and save the file as [Link].

Step 2: Import the JSON File into Excel

1.​ Open Excel and create a new workbook.

2.​ Go to Data > Get Data > From File > From JSON.
3.​ Browse to the location where you saved [Link] (e.g., Desktop) and select the
file.
4.​ Click Import. Excel will open Power Query and display a preview of the JSON data.

Step 3: Transform the Data

1.​ Expand JSON Records:

○​ In Power Query, click the small expand icon ( ) next to the column labeled
"Record" or "List."
○​ Repeat this process for nested fields to flatten the JSON structure into a
table.

​ ​ OR

○​ Click the small icon to the left of the List cell (often called a drill-down icon):
This will expand the list into individual rows, each corresponding to a JSON
record.
 2.​ Clean Up Columns:
○​ Remove unnecessary columns, such as userId (if not required).
○​ Rename columns:
■​ id → "Task ID"
■​ title → "Task Description"
■​ completed → "Is Completed"
3.​ Filter Data (Optional):
○​ Apply filters to focus on specific data. For example, filter for tasks where
completed = false.

Step 4: Load the Data into Excel

1.​ Once the data looks ready, click Close & Load in the top-left corner of Power Query.
2.​ The cleaned-up data will appear in a new worksheet.
Exercise 16: Importing Data
Objective: Use Get & Transform to import and transform data.

Step 1:Prepare a Mock CSV File:

1.​ Open TextEdit (or a plain text editor).

2.​ Paste the following data:
3.​ Name,Department,Salary

Alice,Marketing,60000

Bob,Engineering,75000

Charlie,HR,50000

Diana,Engineering,82000

4.​ Save the file as [Link] on your desktop.

Step 2: Import the CSV into Excel:

1.​ Open Excel and go to Data > Get Data > From Text/CSV.
2.​ Navigate to your desktop and select [Link].
3.​ Preview the data, then click Transform Data.

Step 3: Transform the Data:

1.​ In the Power Query Editor:

a.​ Rename the columns to Employee Name, Team, and Annual Salary.
b.​ Filter out rows where Salary < 60000 (go to the Salary column, click the
filter dropdown, and uncheck values below 60000).
2.​ Click Close & Load to load the transformed data into a new worksheet.
Exercise 17: Data Integration with SQLite and Excel
Objective: This exercise will guide you through creating and populating an SQLite database
using Python, exporting data from SQLite to an Excel file, and then performing basic data
analysis in Excel. By the end of this exercise, you'll be able to:

●​ Install and verify the necessary Python libraries.

●​ Create and populate an SQLite database.
●​ Query data from the SQLite database and export it to Excel.

Prerequisites Setup

To complete this exercise, you need:

1.​ SQLite Installed:​

○​ SQLite Command-Line Interface (CLI) installed on your system.

○​ To verify, open the terminal and run:

sqlite3 --version

If SQLite is not installed:​

​
macOS: Install using Homebrew:

brew install sqlite

Windows: Download the precompiled binaries from

[Link]](<[Link]

Linux: Use your package manager:

sudo apt install sqlite3

2.​ Basic Knowledge of SQL:

 ○​ You should be familiar with basic SQL commands such as CREATE TABLE,
INSERT, SELECT, and GROUP BY.​

3.​ Terminal/Command Line Skills:

○​ Basic familiarity with running commands in a terminal (macOS/Linux) or

Command Prompt/PowerShell (Windows).​

Step 1: Open SQLite CLI

1.​ Open your terminal and type:

Sqlite3

You should see something like:

SQLite version 3.43.2 2023-10-10 13:08:14

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

This means you are now in the SQLite CLI.

Create or open a database file:​

​
​ .open [Link]

This creates a file named [Link] in your current directory (or opens it if it already
exists).​
Step 2: Create and Populate the Database

Step 2.1: Create the Table

Run this SQL command to create the Employees table:

CREATE TABLE IF NOT EXISTS Employees (

​​ ID INTEGER PRIMARY KEY,

​​ Name TEXT,

​​ Department TEXT,

​​ Salary INTEGER

);

Step 2.2: Insert Sample Data

Insert data into the table using the following commands:

INSERT INTO Employees (ID, Name, Department, Salary) VALUES

(1, 'Alice', 'Marketing', 60000),

(2, 'Bob', 'Engineering', 75000),

(3, 'Charlie', 'HR', 50000),

(4, 'Diana', 'Engineering', 82000);

To verify the data was inserted, run:

SELECT * FROM Employees;

You should see:

1|Alice|Marketing|60000

2|Bob|Engineering|75000
 3|Charlie|HR|50000

4|Diana|Engineering|82000

Step 3: Query Data

Step 3.1: Calculate the Average Salary

To calculate the average salary for each department:

SELECT Department, AVG(Salary) AS AvgSalary

FROM Employees

GROUP BY Department;

You should see something like:

Department|AvgSalary

Engineering|78500

HR|50000

Marketing|60000

Step 4: Export Data for Excel

SQLite CLI doesn't export directly to Excel but can export to CSV, which Excel can open.

Step 4.1: Export the Table Data

1.​ Enable CSV mode:

.mode csv

2.​ Export the data to a CSV file:

​
​ .output [Link]
 SELECT * FROM Employees;

.output

3.​ Check your current directory for the [Link] file. You can open this file in
Excel to view the data.

Step 4.2: Export Custom Query Results

To export the average salary query results:

1.​ Enable CSV mode again (if not already enabled):​

​
​ .mode csv

2.​ Export the query results to another CSV file:

output Department_Avg_Salary.csv

SELECT Department, AVG(Salary) AS AvgSalary

FROM Employees

GROUP BY Department;

.output

3.​ Exit SQLite

.quit

4.​ Open Department_Avg_Salary.csv in Excel to see the average salaries.

Step 5: Analyse Data in Excel

1.​ Open the [Link] or Department_Avg_Salary.csv file in Excel.

 2.​ Use Excel’s features (e.g., PivotTables) for further analysis, or simply inspect the
data.

Outcome

By the end of this exercise, you’ve:

1.​ Created and populated an SQLite database using SQL commands.

2.​ Queried data directly in SQLite CLI.
3.​ Exported data to CSV files for use in Excel.