Two programs that cannot talk
The shop keeps its sales in one program. The council wants a copy in its own program. The two cannot open each other's files.
Both can read plain text. So the sales are saved as text by one and loaded by the other.
Key words
| Word | What it means | Example |
|---|---|---|
| Text file | a file made only of readable characters, with no formatting | |
| Export | save data out of one program into a file | |
| Import | load data from a file into a program | |
| CSV | comma-separated values: one row per line, commas between columns | Cola,3,120 |
| TSV | tab-separated values: the same, with a tab between columns | |
| JSON | text that holds named values in curly brackets | {"item": "Cola"} |
| Delimiter | the mark that separates one column from the next | |
| Header row | the first line, which names the columns |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can say why data is saved to and loaded from text files, and read and write CSV, TSV and JSON.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Why use text files?
| Reason | What it means |
|---|---|
| Any program can read it | No special software is needed to open it |
| People can read it | You can open it in a plain editor and check it |
| Small and simple | Easy to send, store and back up |
| A common middle step | Two different programs can share data through it |
| Easy to test | You can compare two files line by line |
The cells students fill in are left blank in this preview.
The three formats
| Format | Looks like | Best for |
|---|---|---|
| CSV | `Cola,3,120` | Flat tables from spreadsheets and other programs |
| TSV | `Cola` tab `3` tab `120` | Tables where the data holds commas |
| JSON | `{"item": "Cola", "qty": 3}` | Records with named values, even nested ones |
The cells students fill in are left blank in this preview.
Try it: JSON in Python
import json
text = '{"item": "Cola", "qty": 3, "in_stock": true}'
record = json.loads(text)
print(record["item"], record["qty"])
record["qty"] = 2
print(json.dumps(record))
# Change the text so qty is 10, then run it again.
Things that go wrong
A comma inside a value, like Cheese, ham, breaks a CSV row unless the value is in quotes. A TSV avoids this because tabs are rare inside values.
Everything in a text file is text. The number 3 is read as the characters "3". Convert it back to a whole number when you load it.
Try it: read CSV text in DuckDB
SELECT split_part(line, ',', 1) AS item,
CAST(split_part(line, ',', 2) AS INTEGER) AS qty,
CAST(split_part(line, ',', 3) AS INTEGER) AS price_pence
FROM (SELECT unnest(string_split(text, chr(10))) AS line FROM raw)
WHERE line NOT LIKE 'item%';