← QCE Digital Solutions Unit 2

Problems caused by inconsistent data formats when inserting data

Data from different sources written in different ways (dates, text vs numbers, delimiters). It must be converted to one consistent format before it can be inserted into a data store.

Part of: QCE Digital Solutions Unit 2 — Data-Driven Problems and Solution Requirements — Module 12

Understanding Problems caused by inconsistent data formats when inserting data in QCE Digital Solutions Unit 2

What it does: One source separates fields with a pipe and the other with a comma. Each must be split using its own delimiter so the same fields line up before they are inserted. Dates written in different ways can be rejected or misread. Convert them to one format, such as YYYY-MM-DD, before inserting. A number field needs a real numeric value. The text must be converted first or the insert may fail. The delimiter tells the program where one field ends. Using the wrong one breaks the data apart in the wrong places. Converting before insertion stops bad or mismatched data entering the store and keeps it reliable.

Common mistakes: A CSV row uses commas as its delimiter. Splitting on ';' leaves the whole row in one piece, so the fields cannot be inserted. It should be split(csvRow, ','). The source date is DD/MM/YYYY, so p[0] is the day and p[2] is the year. The line keeps the day-first order, so the store gets an inconsistent date. It should build year-month-day: p[2]+'-'+p[1]+'-'+p[0].

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Problems caused by inconsistent data formats when inserting data to a class →

This module's key terms are also in a printable crossword (Modules 2–115) and word search (Modules 2–139).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

Two sources give dates as 03/04/2026 and 2026-04-03. What is the problem when inserting both into one date field?

The formats are inconsistent, so one may be rejected or misread (03/04 could be 3 April or 4 March)There is no problem because both are dates, so a database always understands them

Matches

INSERT INTO students VALUES (11, 'Kai', 'Lee', 'Seven', 'Red', 60);

↓

The yearLevel is written as the word 'Seven' (text) instead of the number 7, so it is the wrong format for a numeric field.

Applying

Meanings

Term

inconsistent data format

Meaning

Data from different sources written in different ways (dates, text vs numbers, delimiters). It must be converted to one consistent format before it can be inserted into a data store.

Practice

INSERT INTO orders VALUES (11, 2, 4, 2, '2026-03-09');

Testing

Gaps

markNum ← (markText)

Check Answer

Spots

1parts <- split(csvRow, ';')

2name <- parts[0]

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Two sources give dates as 03/04/2026 and 2026-04-03. What is the problem when inserting both into one date field?

A. There is no problem because both are dates, so a database always understands them

B. The formats are inconsistent, so one may be rejected or misread (03/04 could be 3 April or 4 March) ✓

Dates written in different ways can be rejected or misread. Convert them to one format, such as YYYY-MM-DD, before inserting.

2. A CSV file stores a price as the text "$12.50" but the database field is a decimal number. What should happen before insertion?

A. Remove the $ symbol and convert the text to a number like 12.50 ✓

B. Insert the text as it is, because the database will fix the type itself

A number field needs a real numeric value. The text must be converted first or the insert may fail.

3. Which is a result of inserting the text value "twenty" into a numeric Quantity field?

A. The database converts it to 20 automatically and stores it

B. The insert fails or the value is wrong, because text is not a number ✓

Text versus number mismatches cause errors. Words must be converted to numeric values before insertion.

4. One file separates fields with commas and another uses semicolons. What happens if both are read with a comma delimiter?

A. The semicolon file is read as one long field, so the data lands in the wrong fields ✓

B. Both files are split correctly because delimiters do not affect reading

The delimiter tells the program where one field ends. Using the wrong one breaks the data apart in the wrong places.

5. Source A lists fields as Name,Age,Year and Source B lists them as Year,Name,Age. What must be done before inserting both into one table?

A. Reorder the fields so every record matches the table's field order ✓

B. Nothing, because the data goes in by position and will match up

Inserting by position puts values in the wrong fields if the order differs. Convert each source to the same field order first.

Match the Pairs

INSERT INTO orders VALUES (11, 1, 2, 3, '03/02/2026'); → Fails to match the stored dates because it uses DD/MM/YYYY instead of the YYYY-MM-DD format the orderDate column uses

INSERT INTO products VALUES (11, 'Ruler', 'Stationery', '$2.50', 10); → Inserts a price as text with a dollar sign instead of a plain number, so the price column holds inconsistent data

Two files use different delimiters, so one must be split differently before its fields can be inserted into the same table → Ava|Nguyen|7 versus Ava,Nguyen,7

Sources can list fields in a different order, so values must be reordered to match the table before insertion → Nguyen,Ava,7 must become Ava,Nguyen,7

INSERT INTO students VALUES (11, 'Kai', 'Lee', 'Seven', 'Red', 60); → The yearLevel is written as the word 'Seven' (text) instead of the number 7, so it is the wrong format for a numeric field.

INSERT INTO orders VALUES (11, 2, 3, 1, 'Feb 5 2026'); → The date is written in a different style from the YYYY-MM-DD format used by the table, so it is inconsistent and must be converted.

Key Term

inconsistent data format

Data from different sources written in different ways (dates, text vs numbers, delimiters). It must be converted to one consistent format before it can be inserted into a data store.

Fill in the Blanks

1. markNum ← toInteger(markText) insert markNum into results

Hint: Type the function that turns the text value into a whole number before it is inserted.

2. fields ← split(line, ";")

Hint: Type the function that breaks a line into separate values at the delimiter.

3. fields ← split(line, ";") // File B order: mark;name. Index from 1 name ← fields[2]

Hint: Type the position of the name field in this file's different field order.

4. parts ← split(date, "/") // DD/MM/YYYY iso ← parts[3]+"-"+parts[2]+"-"+parts[1]

Hint: Type the index of the day part in the split date, so the date is rebuilt as year-month-day.

5. if format ≠ "YYYY-MM-DD" then date ← convertDate(raw) end if

Hint: Type the comparison operator that checks whether the format is not the required one, so it gets converted.

Spot the Error

Question 1

A parts <- split(csvRow, ';')

B name <- parts[0]

C mark <- integer(parts[1])

D insert(students, name, mark)

Line A — A CSV row uses commas as its delimiter. Splitting on ';' leaves the whole row in one piece, so the fields cannot be inserted. It should be split(csvRow, ',').

Question 2

A dateText <- '15/03/2025'

B p <- split(dateText, '/')

C iso <- p[0]+'-'+p[1]+'-'+p[2]

D insert(events, iso)

Line C — The source date is DD/MM/YYYY, so p[0] is the day and p[2] is the year. The line keeps the day-first order, so the store gets an inconsistent date. It should build year-month-day: p[2]+'-'+p[1]+'-'+p[0].

Question 3

A priceTxt <- '$4.50'

B clean <- remove(priceTxt, '$')

C price <- string(clean)

D insert(items, price)

Line C — The price column stores numbers, but string() leaves the value as text. The value must be converted to a real number before insertion: price <- real(clean).

Question 4

A row <- '7,Ava'

B p <- split(row, ',')

C name <- p[0]

D year <- integer(p[1])

Line A — The fields are in a different order from the one the code expects. The code reads name first and year second, so this row would put 7 in name and fail to convert 'Ava' to an integer. The row should be 'Ava,7'.

Question 5

A mark <- '88'

B total <- mark + 5

C insert(results, total)

Line B — mark is text, so adding 5 to it does not give a numeric result. The text must be converted first: total <- integer(mark) + 5.

Student with a class code? Join your class →