Clean a messy export

2. The monthly file is broken in familiar ways

You are the Level 2 Power user preparing an export before a meeting. Its real header is on row four; dates have four formats; values mix mg/L, g/L, and missing. Last month's manual spreadsheet repairs cannot be reconstructed. A tidy result may hide a wrong conversion or wrongly removed duplicate.

Build a workflow that reads unchanged raw data, applies named rules, validates the result, and writes a new file. Then rerun it on next month's synthetic fixture. The product is the inspectable route from raw input to verified output, not one repaired table.

3. After this you can

• Build a repeatable read -> headers -> types -> missing -> deduplicate -> validate -> write workflow.
• Normalise declared date and unit formats without silent guessing.
• Flag missing, duplicate, and unparseable rows instead of making them disappear.
• Verify row counts, identifiers, types, and totals before exporting.
• Rerun the saved workflow on a second month's files without rebuilding its transformations.

4. Prerequisites

• T10-L01 · Ask questions about a spreadsheet.
• KNIME Analytics Platform installed from an approved source, with a local workspace you control.
• Permission to read and write files in one local exercise folder.
• The synthetic CSV fixtures in section 6. No account, database, credential, AI extension, or network connection is required.

Use only public, synthetic, course-provided, or explicitly approved data. Do not substitute instrument data about people, patient or participant records, employee data, customer exports, confidential invoices, credentials, or unpublished results. Keep the exercise local. Before any future use with real data, obtain approval for the source, workspace, output location, retention, and people who may receive the result.

5. The idea in one page

Preserve evidence before changing values

Keep the raw export unchanged and read it into the workflow. Add a raw inspection branch so you can compare every later table with what arrived. A visual connection shows intended data flow; only an executed node's table shows what happened on this run.

Give each transformation one visible purpose:

Declare conversions instead of guessing

Write the accepted date patterns and units in a workflow annotation. For the Lab framing, mg/L has factor 1 and g/L has factor 1000; the canonical unit is mg/L. For the Company framing, EUR has factor 1 and kEUR has factor 1000; the canonical unit is EUR. The shared formula is:

canonical_value = parsed_number * declared_unit_factor

Anything outside the map receives parse_error. Blank, missing, and pending never mean zero.

Validate before the writer

Count rows at boundaries, not only at the end. The invariant is:

raw rows = unique output rows + rejected duplicate rows
unique output rows = valid rows + missing-source rows + parse-error rows

Build the Validate group as an executable gate, not a checklist. The reader's path column has KNIME's Path type, which regexReplace must not receive directly. Use Path to String first to create the String column source_file; then, before GroupBy, derive run_month with String Manipulation: regexReplace($source_file$, ".*(2026-[0-9]{2}).*", "$1"). Add Rule Engine indicator columns to the clean branch. Configure bad_key with MISSING $record_id$ => 1, $record_id$ MATCHES "^\\s*$" => 1, and TRUE => 0. Configure bad_status with $value_status$ = "valid" => 0, $value_status$ = "missing_source" => 0, $value_status$ = "parse_error" => 0, and TRUE => 1. Add is_valid, is_missing, and is_parse_error in the same way: the named status returns 1 and TRUE returns 0. Use GroupBy with no grouping columns to produce one clean-summary row containing first run_month, row count, unique count of record_id, sums of all five indicators, count of non-missing value_canonical, and sum of value_canonical. Make equivalent one-row raw and duplicate-audit counts with GroupBy, then combine the three summaries with two Cross Joiner nodes.

Create a four-column Table Creator control table containing run_month, expected_valid, expected_missing, and expected_total: use 2026-08,4,1,41.5 and 2026-09,5,0,50.5 for Lab, or 2026-08,4,1,370.0 and 2026-09,5,0,475.0 for Company. Inner-join exactly one control row to the summary on run_month; zero or multiple matches are a failed configuration. Use Column Renamer so the aggregate fields have the names below, then add Math Formula: total_delta = abs($numeric_total$ - $expected_total$). Configure the final Rule Engine with exactly these two rule entries; the complete Boolean condition before the first arrow is one executable entry on one line, not several lines that KNIME would interpret as separate incomplete rules. Do not round before comparing.

$raw_count$ = 6 AND $clean_count$ = 5 AND $audit_count$ = 1 AND $raw_count$ = $clean_count$ + $audit_count$ AND $distinct_keys$ = $clean_count$ AND $bad_key$ = 0 AND $bad_status$ = 0 AND $is_parse_error$ = 0 AND $is_valid$ = $expected_valid$ AND $is_missing$ = $expected_missing$ AND $numeric_count$ = $is_valid$ AND $total_delta$ < 0.000001 => "PASS"
TRUE => "FAIL"

Name its output column validation_result, then add a second Rule Engine that creates a String column named active_port with exactly $validation_result$ = "PASS" => "top" and TRUE => "bottom". Use Table Row to Variable on that one-row table. In IF Switch, use the String flow variable active_port to override Select active port; KNIME's documented values for that setting are top, bottom, and both, not Boolean values. Feed the clean table into the switch's data port and the flow-variable connection into its variable port. Connect only the top output to CSV Writer; terminate the bottom output in a labelled Table View: VALIDATION FAILED—DO NOT WRITE. Thus a failed result makes the writer's input port inactive. Do not connect an ungated clean branch to the writer, do not permit both, and do not configure a fallback that selects top. If validation cannot produce its one-row result, the switch and writer must remain unexecuted. Stop and inspect the earliest wrong table rather than hand-fixing an output.

6. The worked example: one cleanup skeleton, two settings

Create a local workflow named T10-L02-clean-export. In readers and writers, choose Relative to > Current workflow data area; keep fixtures at input/... and generated files at output/.... Arrange nodes in seven annotated groups: Read, Headers, Types, Missing, Deduplicate, Validate, and Write. Labels vary by release, so consult installed node descriptions when necessary.

In every framing, branch the reader output to a Table View before transformations. Configure each reader to skip the first three metadata lines and take the fourth line as column names. In the reader's transformation settings, set all three source columns to String and enforce those types; do not let automatic type guessing decide which date or value forms survive. Enable the reader's path-column option and name the appended Path column source_path before any concatenation. Immediately after reading (or after concatenating Company inputs), add Path to String to create the String column source_file. Preserve both columns. All later filename extraction, including regexReplace for run_month, must use source_file, never the Path-typed source_path. Never point a writer at a raw filename.

Use this conversion contract as a workflow annotation:

Build the date parser with reproducible nodes rather than automatic format guessing. First use String Manipulation to append date_trim with strip($date_raw$). Then use three consecutive String Manipulation nodes, appending the named output at each step:

date_step_1 = regexReplace($date_trim$, "^([0-9]{2})/([0-9]{2})/([0-9]{4})$", "$3-$2-$1")
date_step_2 = regexReplace($date_step_1$, "^([0-9]{4})/([0-9]{2})/([0-9]{2})$", "$1-$2-$3")
date_iso    = regexReplace($date_step_2$, "^([0-9]{2})\\.([0-9]{2})\\.([0-9]{4})$", "$3-$2-$1")

Parse date_iso with String to Date&Time as a date, exact pattern yyyy-MM-dd, strict parsing, and “insert missing on parsing error”; append date. Canonical dates pass through; unmatched or impossible dates become missing and later receive parse_error.

For the value parser, append value_trim = strip($value_raw$). Choose only the regular expression for the framing being built; do not combine both unit sets:

Lab:     ^([+-]?(?:[0-9]+(?:\\.[0-9]*)?|\\.[0-9]+))\\s+(mg/L|g/L)$
Company: ^([+-]?(?:[0-9]+(?:\\.[0-9]*)?|\\.[0-9]+))\\s+(EUR|kEUR)$

Add a Rule Engine column value_shape_ok whose first entry is $value_trim$ MATCHES "<selected expression>" => TRUE and whose fallback is TRUE => FALSE. Next append number_text and unit with two String Manipulation nodes, substituting the same selected expression literally for <selected expression>:

number_text = regexReplace($value_trim$, "<selected expression>", "$1")
unit        = regexReplace($value_trim$, "<selected expression>", "$2")

Convert number_text with String to Number, configured to append parsed_number and insert missing on conversion error. Create unit_factor with Rule Engine: for Lab use $unit$ = "mg/L" => 1.0, $unit$ = "g/L" => 1000.0, TRUE => ?; for Company use $unit$ = "EUR" => 1.0, $unit$ = "kEUR" => 1000.0, TRUE => ?. Calculate value_canonical = $parsed_number$ * $unit_factor$ with Math Formula. Then use Constant Value Column to create the String column canonical_unit explicitly: every Lab row receives mg/L, and every Company row receives EUR. Do not rename the parsed source unit into canonical_unit; retain both so the conversion remains auditable.

Finally create value_status in Rule Engine, testing in this order: the framing's exact missing token (missing or pending) gives missing_source; missing date, false value_shape_ok, missing parsed_number, or missing unit_factor gives parse_error; only the remaining rows give valid. Preserve date_raw, value_raw, unit, source_path, and source_file beside value_canonical and canonical_unit so a reviewer can trace the result. Unknown units and malformed numbers therefore stay missing and visible rather than becoming zero.

Configure Duplicate Row Filter on the exact-row signature record_id, date_raw, and value_raw, retaining the first exact occurrence in the clean branch. Before that filter, make a parallel audit branch: group by the same three columns, count occurrences, keep groups where the count exceeds one, and calculate rejected_occurrences = occurrence_count - 1 with reason duplicate_exact. Preserve the source path aggregation in that audit summary. Use the sum of rejected_occurrences, not the number of duplicate groups, as audit_count. Do not deduplicate on record_id alone. If one identifier occurs with different source values, both rows remain in the clean branch; the later distinct_keys = clean_count gate then fails instead of silently choosing a winner.

Lab framing: an instrument export with mixed units

Save this synthetic file as lab-2026-08.csv:

Synthetic spectrometer export
Run month: 2026-08
Canonical target: mg/L
sample_id,measured_at,result
S-101,2026-08-01,12.0 mg/L
S-102,01/08/2026,0.011 g/L
S-103,2026/08/01,missing
S-104,2026-08-02,8.5 mg/L
S-104,2026-08-02,8.5 mg/L
S-105,02.08.2026,0.010 g/L

Rename sample_id, measured_at, and result to the stable schema. Execute and inspect after every group. The deduplication audit must account for one rejected S-104 occurrence, preserve its source values, and label it duplicate_exact; do not simply discard it.

The clean writer should produce these values, sorted by record_id:

Verify the run independently: six raw rows become five unique rows plus one rejected duplicate. Four values are numeric, one is missing, and none has a parse error. The numeric total is 12.0 + 11.0 + 8.5 + 10.0 = 41.5 mg/L. This total checks conversion mechanics only; it is not a scientific interpretation.

Company framing: two department exports joined into one table

Save the first synthetic department file as company-east-2026-08.csv:

Synthetic department export
Run month: 2026-08
Canonical target: EUR
expense_id,expense_date,amount
C-201,2026-08-01,120.00 EUR
C-202,01/08/2026,0.085 kEUR
C-203,2026/08/01,pending

Save the second as company-west-2026-08.csv:

Synthetic department export
Run month: 2026-08
Canonical target: EUR
expense_id,expense_date,amount
C-204,2026-08-02,75.00 EUR
C-204,2026-08-02,75.00 EUR
C-205,02.08.2026,0.090 kEUR

Read both files with source paths, concatenate them, and rename fields to the stable schema. The audit records one rejected C-204 occurrence as duplicate_exact; C-203 remains missing_source.

The output contains C-201 through C-205 once each: 120.00, 85.00, missing, 75.00, and 90.00 EUR. Verify six raw, five unique, one duplicate, four valid, one source-missing, zero parse errors, and total 370.00 EUR.

Prove that the workflow is repeatable

Copy the selected raw fixture or fixtures to September filenames. Change only every month/date from 08 to 09, then replace the Lab token missing with 9.0 mg/L or the Company token pending with 105.00 EUR. Point only the reader path at the September input and reset/re-execute the workflow. Do not edit transformation nodes.

The Lab rerun must report six raw rows, five unique rows, five valid values, no missing values, one duplicate, and 50.5 mg/L. The Company rerun must report the same counts and 475.00 EUR. Reopen the written September CSV with a fresh CSV Reader. Confirm five rows, unique IDs, canonical numeric type, no parse errors, and the expected total. This final read-back catches writer configuration mistakes such as the wrong delimiter, path, or column selection.

Now prove the gate fails safely inside the same T10-L02-clean-export package. Copy the August synthetic input to a failure fixture. Remove the repeated S-104 or C-204 row, blank the S-101 or C-201 identifier, and change 0.010 g/L to 0.010 lb/L or 0.090 kEUR to 0.090 USD. Delete output/SHOULD-NOT-EXIST.csv from the workflow data area, select the failure fixture, and point the writer to that exact relative name. Execute from reset. The summary must show failed count, key, status, and total gates; active_port must equal bottom; the switch must activate only its bottom port; the writer must remain unexecuted; and the sentinel file must not exist. Restore the normal fixture and confirm one complete run still writes the verified clean table.

Record these executable checks in the workflow annotation; a visual inspection alone is not a pass:

7. What goes wrong

The clean file is repaired by hand

Symptom: today's output is correct, but no node explains why. Fix: encode each correction as a named transformation and regenerate the output from raw data.

Rows disappear instead of being flagged

Symptom: rows vanish without reasons. Fix: preserve rejected identifiers, source values, filenames, and reason codes; reconcile clean plus rejected with raw.

Text silently becomes a number

Symptom: missing becomes 0, or 0.011 g/L becomes 0.011 mg/L. Fix: separate parsing and conversion, reject undeclared units, and verify counts and totals.

Duplicate detection uses the wrong key

Symptom: distinct records are collapsed. Fix: declare the key, audit exact repeats, and fail on conflicting duplicates.

Changes have no record

Symptom: connected nodes do not reveal formats or policies. Fix: annotate accepted inputs, factors, statuses, and validation totals.

The workflow knows one filename

Symptom: September requires editing transformations. Fix: keep month information in paths and source data; change only input and output filenames.

The writer runs before checks pass

Symptom: a plausible CSV is shared despite failed checks. Fix: gate the writer and independently reopen a passing output.

8. Do it yourself: a 60-minute two-month run

Minutes 0-8: choose the Lab or Company framing. Create one local exercise folder, preserve the August raw fixture unchanged, and record expected files, six raw rows, stable columns, key, accepted dates, missing token, and unit factors in a workflow annotation.

Minutes 8-20: build Read and Headers. Add the raw Table View branch, skip the three metadata lines, rename to the stable schema, and preserve source_file. For Company, concatenate the two department tables. Confirm six rows before transformation.

Minutes 20-35: build Types and Missing. Parse only declared dates and units, calculate the canonical value, and assign valid, missing_source, or parse_error. Inspect original and transformed columns side by side.

Minutes 35-43: build Deduplicate and the GroupBy/Rule Engine validation summary. Preserve the repeated row in an audit branch. Connect the resulting flow variable to the fail-closed IF Switch; the writer must receive data only from its PASS output.

Minutes 43-50: connect the writer only after validation. Write to a new August clean filename, reopen it with a fresh reader, and confirm five unique rows and the expected total. Do not overwrite or move the raw fixture.

Minutes 50-57: create the September input exactly as described in section 6. Change only reader and writer paths, rerun, and verify the expected five-valid-value total. Run the synthetic failure fixture and confirm the sentinel output is absent. If you must modify a transformation node, record the run as failed until the workflow is input-independent.

Minutes 57-60: save the workflow, add the two run results to its annotation, reset and execute it once from the selected raw input, then package or export the saved workflow using the method supported by your installation.

9. Exit check

Deliver exactly one artifact: one saved workflow package named T10-L02-clean-export, including its workflow-relative synthetic input fixture or fixtures, that reproduces the selected clean table in one complete run.

It passes when its annotations declare the schema, date patterns, unit factors, missing policy, duplicate key, and expected checks; its raw and audit branches preserve evidence; the count, key, status, and total rules control the writer's input port; the packaged failure fixture leaves SHOULD-NOT-EXIST.csv absent; August produces the verified five-row output; and changing only the file paths produces the verified September output. It fails if any raw cell or written output is manually repaired, a rejected row has no reason, the writer can run despite failed checks, or the package contains real or sensitive data.

10. Rule to remember

Clean the process, not the file.

11. Further reading & tools

• Taught: T10-L01 · Ask questions about a spreadsheet - verifies row counts, types, missing values, and totals before cleanup begins.
• Taught: KNIME: connected, inspectable workflows - builds the local node, port, execution, and intermediate-table inspection habits used here.
• Taught: T12-L01 · What you may paste - decides whether source data may enter a selected tool or service.
• Catalogued: KNIME Analytics Platform User Guide (opens in a new tab) - primary guidance for current workflow, node, configuration, execution, and output behaviour.
• Catalogued: KNIME Flow Control Guide (opens in a new tab) - primary guidance for flow variables and the top, bottom, or both IF Switch setting.
• Catalogued: KNIME File Handling Guide (opens in a new tab) - primary guidance for workflow data areas, relative paths, reader type enforcement, path columns, and writers.
• Catalogued: KNIME Analytics Platform (opens in a new tab) - primary product overview; verify current installation and organisational approval before use.
• Catalogued: Tools index - current tool references and deployment notes.