Track 14 · Practical Data Analysis · Lesson 1 of 11

Importing data

Import a CSV file, read the import check, and verify that what was loaded matches the file before analysing anything.

25 minIntermediate8 graded exercisesFree

A marketing manager sends you an export of 700 customer records and asks for the average annual spend by city by Thursday. Before any average, you need to know what actually arrived: how many rows, which columns, which types — and which problems the import quietly let through.

Most business data reach an analyst as a CSV file (comma-separated values): plain text in which each line is one record and the values in a line are separated by a delimiter. The first line is usually a header that names the columns.

A CSV has no types, no formatting and no formulas — just text. Everything else (this column is a number, that one is a date, this blank means missing) is decided by the software that reads the file. That is why importing is an analytical step, not a clerical one.

Worked example

Reading a small CSV by hand

A file contains these four lines:

customer_id,city,annual_spend_sar,note
C0001,Riyadh,4679.64,
C0002,"Al Khobar",NA,"called, no answer"
C0003,jeddah,1250,unknown

How many records and columns are there, and what does each cell become?

Header. The first line names four columns: customer_id, city, annual_spend_sar, note. It is not a record, so there are 3 records, not 4.
Delimiter and quotes. Values are separated by commas. A value that itself contains a comma must be wrapped in double quotes: "called, no answer" is one value. Without the quotes the line would have five values and everything after the comma would shift one column to the right.
Missing values. C0001's note is empty and C0002's spend is NA: both are standard ways of writing missing. But C0003's note unknown is just a word — software that does not know your conventions keeps it as text.
Spellings. Riyadh and jeddah differ in capitalisation. The file does not care; your city counts will, unless you standardise them later.

3 records × 4 columns. Two cells are missing (one blank, one NA); unknown survives as text and jeddah as a separate spelling — problems the import itself will not fix.

## How the lab imports a file

The lab has an Import CSV… button. It reads .csv, .tsv and .txt files only — an Excel workbook must first be saved as CSV (in Excel: File → Save As → CSV UTF-8). The file is parsed in your browser and stored only in this browser; it is never uploaded to Statistics Quest's servers, and clearing your site data removes it.

Step	What happens	What to check
Size limits	Up to 5 MB, 50 000 rows, 200 columns, 1 000 characters per cell	Split or sample larger files first
Delimiter	Detected from the first lines: comma, semicolon or tab	Columns line up in the Data tab
Header	First row = column names, turned into lowercase_with_underscores ("Annual Spend (SAR)" → annual_spend_sar)	Names are unique and meaningful
Spaces	Leading and trailing spaces are trimmed from every value	" Riyadh " becomes "Riyadh"; "RIYADH" stays different
Missing values	Blank, NA, N/A, nan, null, none, ".", "-", #N/A and "?" (any capitalisation) become missing	Other placeholders ("unknown", "999", "-1") are NOT recognised
Types	A column is numeric if at least 95% of its non-missing values are numbers; ISO dates (2024-03-15) are dates; true/false/yes/no are booleans; few distinct values → category, otherwise text	Inferred types in the Columns table
Safety	Nothing is evaluated: a cell such as =SUM(A1) stays plain text	—

What the lab does when it reads a CSV file

Dataset

Customer records (messy)

700 rows · one row = one customer record (some customers appear twice) · synthetic data

About this dataset Open in the lab

Customer	Signup date	City	Segment	Age (years)	Annual spend (SAR)	Referral source	Email opt-in
C0001	2024-02-28	Riyadh	Small business	31	571670	Advertisement	true
C0002	2021-07-15	Jeddah	Individual	20	4679.64	Friend	true
C0003	2024-08-12	Dammam	Small business	30	3027.49	Advertisement	false
C0004	2022-11-14	Riyadh	Individual	34	5440.72	Search	false
C0005	2022-12-01	Riyadh	Individual	47	1382.89	Social media	false
C0006	2023-11-01	Khobar	Individual	37	4162.73	Store visit	false
C0007	2022-12-06	Jeddah	Individual	36	4146.99	Friend	false
C0008	2023-10-20	Riyadh	Individual	24	2789.46	Search	false

The (synthetic) customer export used throughout this track. One row is meant to be one customer — but some customers appear twice. Look at the first row's annual spend: 571 670 SAR for a homeware shop is suspicious (it will turn out to be recorded in halalas).

A first look after every import

Count rows and columns and compare them with what the sender says the file contains.
Read the import check: missing-value tokens per column, values that did not fit the inferred type, rows with the wrong number of values.
Check the inferred types in the Data tab's Columns table — is every quantity numeric, every code a category or identifier?
Check the Missing and Distinct columns of the profile: an ID column should have as many distinct values as rows; a city column should have a handful.
Sort and scroll: sort each numeric column to see its smallest and largest values; open the filter list of each category to see its spellings.

Quick check

The Data tab shows 700 rows and the customer_id column has 679 distinct values. What is the most likely explanation?

Exercises

The lesson is complete when every exercise is answered correctly. Hints are free; viewing a worked solution before solving means the exercise won't count towards mastery.

0 of 8 exercises correct (not saved — sign in to keep your progress)

Exercise 1 · Guided, difficulty 1 of 3

Which cells become missing?

Not started

A till system exports this CSV file:

order_id,channel,amount_sar
R101,unknown,48
R102,n/a,212
R103,n/a,259
R104,Store,666
R105,#N/A,749
R106,?,852
R107,Store,450
R108,null,340
R109,Phone,72
R110,Store,234
R111,NA,659
R112,Store,667

You import it into the lab. How many values of channel will the import check report as missing?

Exercise 2 · Application, difficulty 1 of 3

What arrived?

Not started

Open customers_messy in the lab (Data tab). Use the row count and the Columns table to answer: how many rows (records), how many columns, and how many distinct values does customer_id have?

Data: customers_messy · Open in the lab to explore and calculate.

Exercise 3 · Practice, difficulty 1 of 3

Missing token or real value?

Not started

Each item is the full content of one cell in a CSV file you import into the lab. Will the lab read it as missing, or keep it as a value?

Exercise 4 · Application, difficulty 2 of 3

Download, import, compare

Not started

On its dataset page, customers_messy can be downloaded as a CSV file. Suppose you download it and import that file into the lab, then read the import check and the Columns table.

How many referral_source values does the import check report as missing?
How many distinct city spellings does the imported table have (missing excluded)?

Remember what the import does to spaces, and that the download protects text cells that begin with -, =, + or @ by putting an apostrophe in front of them (so a spreadsheet cannot run them as formulas).

Data: customers_messy · Open in the lab to explore and calculate.

Exercise 5 · Interpretation, difficulty 2 of 3

A semicolon file with decimal commas

Not started

A supplier's system exports prices like this:

sku;price;stock
A-101;12,5;40
A-102;8,75;12
A-103;19,9;0

You import the file into the lab. What happens to the price column?

Exercise 6 · Practice, difficulty 1 of 3

From workbook to checked table

Not started

A colleague emails you an Excel workbook of customer records. Put the steps in a sensible order.

Exercise 7 · Interpretation, difficulty 1 of 3

What is true about the lab's import?

Not started

Select all statements that are true about importing your own file into the lab.

Exercise 8 · Interpretation, difficulty 2 of 3

Note what the import check missed

Not started

You imported the downloaded customer file. The import check lists 7 missing cities, 18 missing ages, 12 missing spends and 14 missing referral sources (N/A), and no other problems. The Columns table shows 15 distinct cities and 679 distinct customer IDs in 700 rows.

Write two or three sentences for your analysis log: what does the import check not tell you, and what will you check next?

Data: customers_messy · Open in the lab to explore and calculate.

What you can now do

Read a CSV's structure: header, delimiter, quoted values and missing-value tokens
Import a CSV into the lab and know what it does to names, spaces, missing tokens and types
Verify an import by comparing row, column, missing and distinct counts with expectations
Recognise problems an import cannot detect: placeholders, inconsistent spellings and repeated records