Import a small fictional dataset

To import data is to read an external file, usually a CSV, into a table so that each line becomes a record and each column becomes a field.

Practical tasks supply a small file and ask you to load it, then check it. The skill is not clicking “import”. It is choosing the right settings and then proving the result matches the source.

This lesson uses invented data only. It belongs to database structure and validation, and it builds on field types and keys from the earlier lessons.

What settings matter during an import?

Four choices decide whether the table comes out right.

1. Delimiter: the character separating fields. Use the one the file really uses, usually a comma.
2. First row contains field names: tick this when the first line is a header, so it is not stored as a record.
3. Text qualifier: the quotation marks around values that contain the delimiter.
4. Field types and key: set each type from the sample values, and choose the key, before the data loads.

Software names these options differently. Look for the idea, not the exact label.

Worked example

A fictional file called members.csv (invented) holds this content:

MemberID,Name,DateJoined,Fee
M001,Aiman Rahim,2024-03-15,45.50
M002,"Tan, Mei Ling",2024-04-02,45.50
M003,Kumar Raj,2024-05-20,30.00
M004,Sofia Lim,2024-06-11,45.50
M005,Daniel Ong,2024-07-01,30.00

Step 1, count the source. There is 1 header line and 5 data lines. The table should hold 5 records.

Step 2, choose settings. Delimiter: comma. First row contains field names: yes. Text qualifier: the double quotation mark, because “Tan, Mei Ling” contains a comma.

Step 3, set types and key. MemberID: text and primary key. Name: text. DateJoined: date. Fee: currency.

Step 4, import and check the count. The table shows 5 records. That matches Step 1.

Step 5, spot-check the awkward row. Open M002. Name must read Tan, Mei Ling in one field, and Fee must read 45.50. If Name reads “Tan and Fee reads Mei Ling”, the qualifier setting was wrong.

Step 6, check totals. Fees add up to 45.50 + 45.50 + 30.00 + 45.50 + 30.00 = 196.50. Check it a second way: three fees of 45.50 give 136.50 and two of 30.00 give 60.00, and 136.50 + 60.00 = 196.50. A total that does not match the source file signals a bad import.

The mistake to watch for

A frequent error is to leave “first row contains field names” unticked.

Mistaken result: The table has 6 records, and the first one has MemberID = “MemberID”, Name = “Name” and Fee = “Fee”.

The header line was loaded as data. Because Fee now holds the word “Fee”, the field may even be forced to text, which breaks totals.

The correction is to compare the record count with the source before doing anything else. The file has 5 data lines, so 6 records means something extra was loaded.

Delete the bad record, re-run the import with the header option ticked, and check types again. Another symptom to know: if every line sits in a single column, the delimiter was wrong.

Check yourself

1. A CSV file has 1 header line and 12 data lines. How many records should the table hold after a correct import?

2. After an import, every row shows the whole line in the first field, such as “M001;Aiman Rahim;45.50”. What setting was most likely wrong?

3. The value Lee, Anna appears in a CSV without quotation marks. What goes wrong?

Where this leads next

After a clean import, learn to spot values that were read in the wrong form with detecting an incorrect numeric-text interpretation. If keys or types still feel uncertain, revisit defining a key and a validation condition. The read-only SQL practice lab offers a bundled fictional table to look at.

Imports go wrong in small, easy-to-miss ways. A teacher in online one-to-one ICT tuition can hand you files with deliberate faults, so you learn to find them yourself.