Did you know? A pupil can collect three perfectly sensible temperatures, then import a spreadsheet that quietly changes their meaning. The thermometer is not faulty. The problem may be one tiny punctuation mark: a comma used as a decimal separator instead of the full stop expected by the importing software.
Checking a CSV decimal separator means examining how the original file writes fractional numbers, deciding which character divides columns, and converting the values with the correct regional number settings before calculating or graphing them. The immediate answer is to inspect the raw text, set the delimiter and numeric locale deliberately, then verify several values against the original records.
This is an education problem, not merely a computer problem. A sound graph begins with an honest representation of the observations. When students can explain why a particular number is 21.5 degrees Celsius rather than text or 215, they can trust their conclusions for a reason.
Why a comma can mean two different things
A CSV file is plain text organized into rows and columns. In a comma-separated file the comma normally separates fields; however, many regions also use a comma inside a number to indicate its decimal part. Those two uses must not be confused. Some school exports therefore use a semicolon as the field delimiter when fractional values contain decimal commas; other files quote fields or use different conventions.
The UK government’s published tabular-data guidance explains that a CSV parser needs to handle delimiters and quoting correctly. Microsoft’s Power Query guidance explains how the locale associated with the file or a column’s type conversion changes interpretation of imported values. A file extension by itself does not guarantee that the imported numbers are correct.
A fully checked fictional science example
Imagine a student records water temperature every 30 seconds. These figures are invented only to demonstrate a check, not measurements from an actual class experiment. The original file uses semicolons between fields and commas inside temperatures:
- Header: time_s ; temperature_C
- Row 1: 0 ; 21,5
- Row 2: 30 ; 21,8
- Row 3: 60 ; 22,1
Here, time_s means elapsed time measured in seconds. The intended temperatures are 21.5 °C, 21.8 °C and 22.1 °C. Set the field delimiter to semicolon, then convert the temperature column with a locale that treats the comma as the decimal character. Do not treat the comma as another column break.
Hand-check 1 — shape: Every data row should contain exactly two fields. If the import shows three fields, or puts part of a temperature into an unexpected column, investigate the delimiter before doing any arithmetic.
Hand-check 2 — numeric meaning: The imported temperatures should read 21.5, 21.8 and 22.1 as numeric values. If the software instead shows text strings, missing values, errors or 215, 218 and 221, the conversion has not been verified.
Hand-check 3 — mathematics: The temperature mean is (21.5 + 21.8 + 22.1) ÷ 3 = 65.4 ÷ 3 = 21.8 °C. The observed change from the first to the third reading is 22.1 − 21.5 = 0.6 °C over 60 seconds. The average change per second across those two endpoints is 0.6 ÷ 60 = 0.01 °C/s. This endpoint rate describes the example data; it does not establish that warming was physically uniform between readings.
A quick magnitude check matters: typical classroom water readings around 20 °C do not become 200-plus °C because a CSV import says so. An unexpectedly huge reading is a reason to examine the import, not a reason to invent a spectacular scientific finding.
Diagnose the learner’s actual misconception
Before showing software menus, ask: ‘In 21,8, which character marks the fraction and which separates columns?’ Then show the raw source row 30;21,8 and ask the student to predict the number of fields and the temperature. This tests the intended data model independently from spreadsheet familiarity.
- Two fields, 21.8 °C: the student understands the intended format; proceed to import settings.
- Three fields: the student may be treating the decimal comma as a field delimiter.
- 218 °C: the separator may have been stripped or misread; check the source and the conversion step.
- “I cannot tell”: that is a defensible answer if the file’s provenance or delimiter has not been established. Investigate instead of guessing.
A failure can happen at more than one step. The file may be perfectly formed but opened under a different locale, or its fields may be separated properly while its numeric column remains typed as text. Repair the relevant step rather than applying a blanket ‘replace every comma’ command, which can damage quoted text, descriptions or multi-column records.
Progress from teacher modelling to independent transfer
First practice: import with the original beside you
Let the teacher model a small two-column file. Save a copy, open the original in a plain-text viewer, circle the delimiter, mark the decimal symbol, import using an explicit delimiter, and check at least the first, middle and last temperature against their source rows. Keep the original untouched.
Second practice: change one assumption
Give a second invented file that uses commas to separate columns but full stops for decimals: 0,21.5 and 30,21.8. Students must explain why a semicolon setting would now be wrong. Ask them to name the delimiter and decimal separator separately rather than choose a setting by habit.
Third practice: work independently
Students import a four-row unfamiliar sample, record the chosen settings in one sentence, and calculate a mean by hand before using a spreadsheet formula. Remove the teacher’s hints. The observable success criterion is not ‘the graph looks smooth’; it is that every original numeric record maps to the intended value, with the same units and row count.
What parents, pupils and teachers can do next
- Student: keep a short import audit: source filename, delimiter, decimal separator, numeric units, three values checked, and whether any rows failed conversion.
- Teacher: provide a deliberately mixed pair of files and mark the reasoning about each, not just whether Excel eventually displays a chart.
- Parent: ask ‘What would the graph look like if the values were interpreted ten times too large?’ and let the child locate the source of the discrepancy.
- Project team: protect personal information in class exports; work with anonymised IDs and authorised files rather than uploading pupil data to an unknown converter.
Progress is visible when the learner can spot a conversion problem in an unfamiliar file, explain the significance of the punctuation, fix the import without destroying the source, and verify the mathematics afterwards. One well-audited import shows more understanding than five attractive charts produced on autopilot.
Limitations: when you should not force a conversion
Not every comma or full stop belongs to a number. Text fields, thousands separators, dates, quoted notes, and files that mix regional conventions need deliberate treatment. A number like ‘1,234’ might mean one point two three four or one thousand two hundred thirty-four, depending on the documented convention. If the source does not resolve that ambiguity, ask the data creator or obtain the metadata. Do not silently choose whichever interpretation creates a pleasing result.
Some file viewers automatically infer separators, types and dates; others use different defaults. Menu names change with versions and devices. The transferable procedure is inspection, deliberate parsing, source-value validation and independent arithmetic — not memorising one screenshot of a menu.
Frequently asked questions
Is changing every comma to a full stop the quickest fix?
It can corrupt a CSV’s field separators or quoted text. First establish the delimiter and data types. Convert the numeric column using the intended locale when appropriate; preserve the raw file so the conversion can be audited.
What if the graph is still wrong after temperatures import correctly?
Check which column is on each axis, whether elapsed time is numeric and in seconds, and whether observations were sorted in the correct sequence. Correctly parsed temperatures are necessary but not sufficient for a defensible graph.
Does every CSV use commas between fields?
The term CSV conventionally refers to comma-separated values, but text exports encountered in practice may use semicolons or tabs. Verify the actual file, particularly when regional settings affect its interpretation.
How many values must I check by hand?
Check representative rows, including unusual extremes and any value the analysis relies on. A small illustrative file can be checked completely. A large real dataset needs systematic validation as well as sampled inspection; three correct rows alone cannot prove there are no other errors.
The core aim: make the invisible interpretation visible
The difference between a good result and an accidental one often lies in a hidden assumption: what do the characters in a file mean? Explore the existing guide to preserving student codes with leading zeros and the guide to sorting dates stored as text for other ways to protect meaning during import.
Our wider Education Hub and diagnosis-first Secondary 1 Mathematics teaching reference share the same principle: teach the learner to understand the representation, practise with guidance, and finally check the result independently. A science chart becomes trustworthy only when the student can trace its numbers back to what was recorded.