Combine CSVs when column order changes
Map columns by exact header name, preserve identifiers and report an absent field before combining exports.
In this guide
A regional office sends the same columns in a different order. A second export omits region altogether. Copying the records underneath one header can produce a tidy-looking table with West in the order_id column. A correct row count does not catch this mistake. This guide establishes a mapping contract, combines three small files locally, and checks both the output values and the source counts.
Check whether this mapping fits your exports
This is a Python practice workflow for an explicitly defined target schema, not an automatic merger for arbitrary spreadsheets. Compare your header names with the mapping in this guide before adapting it. A differently named column may have a different business meaning, even when it contains similar values. The supplied helper requires exact header names and has no alias configuration. Decide and document any source renaming before adapting it; unresolved schema changes should stop the merge rather than produce blank columns that look successful.
Identify the difference between position and meaning
Download the three inputs and the expected output, or get everything in the practice pack. All records are fictional, and all inputs use UTF-8 with a BOM, commas and double-quote escaping.
| File | Header order | One data record |
|---|---|---|
| schema-a.csv | order_id, amount, region | 001, 10.00, East |
| schema-b.csv | region, order_id, amount | West, 002, 20.00 |
| schema-c.csv | order_id, amount | 003, 30.00 |
For this exercise, all amounts use the same currency and decimal convention. The first two files have the same schema in different positions. The third lacks an entire region column. That differs from a region column that exists but contains an empty value.
Inspect header names before loading or renaming anything. order_id, Order_ID and order_id are three different names under this tutorial's exact-match rule. Resolve spelling differences with the source owner rather than silently treating them as equivalent. The same header can also hide different units: amount in cents and amount in currency units require a deliberate conversion before combination.
Set the output contract
The target columns are source_file, order_id, amount, region and schema_status. Retain every valid input record in input-file order, preserve the identifier and amount as strings, and add provenance from the actual filename.
Two columns are required: order_id and amount. Region is optional for this exercise. When its header is absent, output an empty region field and the status missing region. When the region header exists, use complete, even if an individual region value is blank. Here, complete describes the schema, not the completeness or correctness of every business value.
Reject duplicate or blank headers, edge spaces in headers, unexpected columns, missing required columns and inconsistent row widths. These rules are intentionally conservative. A production team may approve other mappings, but they should be recorded before fields are discarded or renamed.
Run the local mapping example
Extract the practice ZIP into its own folder. It contains csv_map_headers.py and its helper csv_structure_check.py; keep both together. Use Python 3.9 or newer. The scripts need no extra packages and send no files to a service. Open a terminal in that folder and run:
python3 csv_map_headers.py combined.csv schema-a.csv schema-b.csv schema-c.csvOn Windows with the Python launcher, replace python3 with py -3. If Python is unavailable, use the expected table below as a specification for your data team; this article does not require installing Python to understand the mapping decision.
The command creates a new UTF-8 CSV with a BOM. It never modifies the inputs and refuses to overwrite an existing output. On a second run, choose another output name, such as combined-check.csv. List the intended input files explicitly: a wildcard could accidentally include a previous output or the expected-results file.
The program checks every source before opening the output, so a rejected input does not leave a partial combined file. It also refuses a repeated input path or two inputs with the same basename, which would make source tracking ambiguous. A storage failure during writing can still leave an incomplete file; keep the inputs and check the final output before using it.
Verify values as well as totals
The terminal should show one data record for each source and three output data records. Compare the output with schema-expected.csv:
| source_file | order_id | amount | region | schema_status |
|---|---|---|---|---|
| schema-a.csv | 001 | 10.00 | East | complete |
| schema-b.csv | 002 | 20.00 | West | complete |
| schema-c.csv | 003 | 30.00 | empty field | missing region |
Check schema-b directly: the ID is 002, the amount is 20.00 and the region is West. Verify that all three leading-zero IDs survive. Then reconcile the three records and the amount total of 60.00. Totals alone would not expose every column swap, and row counts alone would not expose altered amounts.
The program maps strings without validating monetary values or adding them. The 60.00 check is a separate test using the known example amounts. For your own data, resolve text amount formats before summarizing. To inspect the output in Excel, import identifiers as Text using the leading-zero guide; double-clicking the CSV can still change how Excel interprets IDs even though the file contains them correctly.
Distinguish a missing column from an empty cell
Copy schema-c.csv to candidate.csv and open the copy in a text editor. Try the following three versions of that third input. Keep schema-a.csv and schema-b.csv as the first two inputs and give each run a fresh output filename.
| Third input (header / data row) | Observed result for order 003 |
|---|---|
order_id,ammount / 003,30.00 | Required header missing. The command exited before writing the output. |
order_id,amount,region / 003,30.00, | Empty region value, status complete |
Original schema-c.csv: order_id,amount / 003,30.00 | Empty region value, status missing region |
For the first run use the command below. For the second, replace blocked.csv with present.csv and edit both lines in candidate.csv. The trailing comma on the data row creates an empty third field. For the last run use the original three-file command above.
python3 csv_map_headers.py blocked.csv schema-a.csv schema-b.csv candidate.csvThe deliberately misspelled amount header produced STOP: candidate.csv: required order_id or amount header missing. Compare the source header with the agreed schema. Correct a confirmed typo in a working copy. If the two names refer to different units or measures, resolve that difference before mapping.
If the receiver requires region, check both the header status and the actual region value. Either of the last two cases leaves order 003 outside the accepted delivery. Orders 001 and 002 total 30.00 together, and the held order accounts for the other 30.00. The original three-input run matched every cell of schema-expected.csv. Download the three run results.
Combining is also different from deduplication. The helper keeps repeated business IDs and does not join records. Use a documented rule in duplicate removal only if duplicates must actually be removed. Keep the source-file column through that review so an unexpected record can be traced back.
What about Power Query?
Microsoft documents that Power Query append aligns columns by header name and fills absent columns with nulls. The same source inspection still matters: capture whether a header was absent before appending, because an empty result alone does not explain the cause. This release verifies the downloadable Python route; it does not claim a newly tested Power Query interface procedure. See Microsoft's Append queries documentation for that product's behavior.
Test record and practical limits
On October 22, 2026, Python 3.9.6 on macOS produced a combined file that matched the independently stored expected CSV byte for byte. Tests checked reordered columns, preserved IDs, per-file counts, the 60.00 total, absent versus empty region, protected existing outputs, repeated sources and malformed inputs. All 37 assertions in the shared suite passed. The dated test record includes the source hashes and exact command. To reproduce it inside the extracted pack, run:
python3 test_csv_practice.pyThe helpers are intended for small practice files: each input is limited to 5 MB, and all decoded records are held in memory. They do not support alternate encodings or delimiters, normalize identifiers, validate dates, prevent formula interpretation in Excel, or deduplicate business records. Test a fictional subset and agree on a schema before adapting the scripts to a real workflow.
For notes containing commas, quotes or embedded newlines, first check CSV record boundaries. Finish with export verification before delivering a file.