Module 2
Importing and inspecting data
CSV, table-like Excel sheets and JSON fixtures; headers, delimiters and encodings.
You'll be able to
- Import CSV with the right delimiter and encoding
- Promote headers safely
- Inspect a JSON document
Lessons
- 1.Import CSV files: delimiters, encodings and headersTurn a delimited text file into a typed table you can trust.
- 2.Remove report banners before promoting headersClean the top and bottom of a report-style export so the header row is promoted correctly.
- 3.Inspect a JSON document before flattening itRead the shape of a JSON document and turn its main array into a table.
- 4.Import from Excel: tables, sheets and the navigation tablePick the right object from a workbook and get clean columns from it.
Take the importing and inspecting data quiz (8 questions)
Exercises
- beginner~7 minFree
Import a semicolon-separated stock file (hardware)
Read a CSV that uses semicolons, promote headers and set number types.
- beginner~7 minPro
Import a semicolon-separated stock file (pharmacy)
Read a CSV that uses semicolons, promote headers and set number types.
- beginner~7 minPro
Import a semicolon-separated stock file (cafe)
Read a CSV that uses semicolons, promote headers and set number types.
- beginner~7 minFree
Skip the title rows in a report export (consultancy)
The export starts with a report title and a blank line. Skip them, then promote the real headers.
- beginner~7 minPro
Skip the title rows in a report export (school)
The export starts with a report title and a blank line. Skip them, then promote the real headers.
- beginner~6 minFree
Import a CSV whose descriptions contain commas (hardware)
Some item descriptions contain commas and quote marks. Import the file so each description stays in one column.
- beginner~6 minPro
Import a CSV whose descriptions contain commas (pharmacy)
Some item descriptions contain commas and quote marks. Import the file so each description stays in one column.
- beginner~5 minFree
Turn a JSON list of accounts into a table (saas)
A JSON file holds a list of flat account records. Convert it to a table with one row per account.
- beginner~5 minPro
Turn a JSON list of accounts into a table (coworking)
A JSON file holds a list of flat account records. Convert it to a table with one row per account.
- beginner~6 minFree
Load the named table from an Excel workbook (kite shop)
The workbook has a notes sheet, a printed sales sheet and a proper Excel table. Load the table.
- beginner~6 minPro
Load the named table from an Excel workbook (bakery)
The workbook has a notes sheet, a printed sales sheet and a proper Excel table. Load the table.
- beginner~6 minFree
Remove the two footer lines from a stock export (hardware)
The export tool appends two summary lines at the bottom of every file. Remove them, then type the counts.
- beginner~6 minPro
Remove the two footer lines from a stock export (pharmacy)
The export tool appends two summary lines at the bottom of every file. Remove them, then type the counts.
- beginner~6 minPro
Remove the two footer lines from a stock export (cafe)
The export tool appends two summary lines at the bottom of every file. Remove them, then type the counts.
- beginner~6 minFree
Import a CSV that has no header row (cafes)
The till system writes daily takings without a header line. Name the columns yourself and type them.
- beginner~6 minPro
Import a CSV that has no header row (kiosks)
The till system writes daily takings without a header line. Name the columns yourself and type them.
- advanced~12 minFree
Read a fixed-width text report (parts)
An old system writes each record as fixed-width text. Cut each line at the right positions and type the result.
- advanced~12 minPro
Read a fixed-width text report (library)
An old system writes each record as fixed-width text. Cut each line at the right positions and type the result.