Why different CSV layouts cause silent errors

Combining monthly exports sounds straightforward until each source uses a slightly different layout. One supplier sends “SKU, Price, Stock”; another sends “Item Code, Unit Price, Quantity”; a third includes a warehouse column and leaves price blank. Simply appending rows can place values under the wrong headers or create separate columns that mean the same thing. The resulting spreadsheet may look complete while its totals and identifiers are unreliable. A repeatable Python workflow should define the intended output schema before loading files, map alternate field names explicitly, and record anything that cannot be reconciled.

The aim is not to force every source to look identical. It is to preserve each record’s meaning while producing a predictable table. A good process keeps original files untouched, distinguishes missing fields from empty values, and makes exceptions visible. This is particularly important for inventory feeds, product catalogs, supplier lists, and recurring data-entry exports where a small mismatch can affect thousands of rows.

Start with an output schema rather than a file

Decide which columns the final dataset actually needs. For a simple inventory merge, the output might contain source_file, sku, product_name, price, quantity, and warehouse. Specify whether each field is required, its intended data type, and how missing values should be represented. SKU should generally remain text because codes can contain leading zeros, letters, or hyphens. Quantity may be a nullable integer, while price may need decimal validation before conversion.

Do not automatically keep every column from every source. That approach produces a wide table with duplicate concepts such as product_id, item_code, and sku. Conversely, discarding unknown columns immediately can lose important supplier information. Maintain a separate exception report listing unexpected headers, and decide deliberately whether to add them to the canonical schema.

Inventory the input files

List the CSV files and inspect their headers, delimiters, and a few sample rows before merging. Check whether some files contain extra introductory lines, multiple tables, or different character encodings. A CSV is not guaranteed to use a comma, and a file extension does not prove its encoding. If a supplier export changes unexpectedly, treat it as a new input format that requires review rather than silently guessing.

Use pathlib to discover files from a dedicated input folder. Keep temporary files, prior merged outputs, and unrelated spreadsheets outside that folder. Sort file paths to make processing order reproducible, although the final record order should not be treated as a business rule.

Normalize column names carefully

Header normalization can remove leading spaces, convert names to lowercase, and replace spaces or hyphens with underscores. It should not indiscriminately remove every symbol or merge columns with similar names. “Price” and “Price including tax” are not interchangeable, nor are “Available” and “On order.” Maintain an explicit mapping for verified synonyms such as item_code to sku and unit_price to price.

Before renaming, check whether two original columns would map to the same destination. A source containing both sku and item_code may hold conflicting values. Rather than allowing one to overwrite the other, flag the file or compare the fields row by row under a documented rule. The mapping itself is part of the data contract and deserves version control.

Read identifiers as strings

Pandas often infers numeric types from CSV values. That is useful for calculations but risky for identifiers. A code such as 000018 can become 18 if interpreted as a number. Reading source columns as strings first provides a safer staging layer. Trim only unwanted surrounding whitespace; never remove zeros, punctuation, or internal spaces unless the business definition requires it.

For additional examples of identifier handling, see the guide to preserving leading zeros in CSV and Excel files. It explains why spreadsheet display formatting is not a substitute for preserving the underlying text value.

A practical pandas merging pattern

The following example assumes UTF-8 CSV files with comma delimiters and a single header row. It intentionally fails on unexpected duplicate canonical headers instead of choosing one silently. It reads all values as strings, adds provenance, aligns columns to a fixed schema, and writes a consolidated CSV. Adapt the mapping to verified source formats before running it on production data.

from pathlib import Path
import pandas as pd

folder = Path("input_csv")
output_columns = [
    "source_file", "sku", "product_name",
    "price", "quantity", "warehouse"
]
aliases = {
    "item_code": "sku",
    "product_code": "sku",
    "item_name": "product_name",
    "unit_price": "price",
    "stock": "quantity",
}
frames = []
for path in sorted(folder.glob("*.csv")):
    df = pd.read_csv(path, dtype="string", encoding="utf-8",
                     keep_default_na=False)
    normalized = [
        str(col).strip().lower().replace(" ", "_").replace("-", "_")
        for col in df.columns
    ]
    canonical = [aliases.get(col, col) for col in normalized]
    if len(canonical) != len(set(canonical)):
        raise ValueError(f"Conflicting columns in {path.name}")
    df.columns = canonical
    if "sku" not in df.columns:
        raise ValueError(f"Missing SKU in {path.name}")
    df["source_file"] = path.name
    frames.append(df.reindex(columns=output_columns))

if not frames:
    raise ValueError("No CSV files found")

merged = pd.concat(frames, ignore_index=True)
merged.to_csv("combined_inventory.csv", index=False,
              encoding="utf-8")

This example is deliberately conservative. It does not infer encodings, automatically repair values, or remove duplicate rows. Those decisions require source-specific rules and validation. For production use, add logging, a required-column checklist, exception exports, and type checks before the final file is released.

Handle absent columns differently from blank cells

A missing column means the source file did not supply that field at all. A blank cell means the column exists but a particular record has no value. Those situations may require different follow-up actions. For example, a supplier that never provides warehouse locations is not equivalent to one that provides a warehouse column but leaves ten rows empty. Record missing columns per file in a validation log before adding empty placeholders for output alignment.

Avoid filling missing prices with zero unless zero is an actual, validated business value. A zero price could be interpreted as a free item or trigger downstream errors. Similarly, a missing quantity is not necessarily zero stock. Keep missing values explicit until an approved transformation rule can resolve them.

Validate data types after alignment

Once headers are standardized, convert fields according to the output contract. Strip currency symbols only when their meaning is known; a dollar sign does not establish whether the amount is USD, CAD, or AUD. Use strict numeric conversion and report values that fail, such as “call for price” or “10 units.” For monetary calculations, consider decimal arithmetic rather than binary floating point.

Dates deserve the same care. The text 03/04/2026 can mean March 4 or April 3 depending on the source. Define a date format for each supplier and reject ambiguous inputs. Do not let automatic parsing make unreviewed assumptions. For database-bound data, see the related article on cleaning CSV data before database import.

Detect duplicates using business keys

After concatenation, identify whether records repeat. The correct key depends on the dataset. A SKU may be unique within one supplier but not across all suppliers. A warehouse inventory snapshot might require supplier, sku, warehouse, and snapshot_date together. Blindly calling drop_duplicates can discard legitimate entries or retain conflicting updates.

Create a duplicate report showing the key and all conflicting rows. If the business rule says the latest timestamp wins, verify timestamps are trustworthy and use a stable tie-breaker. If duplicate rows are genuinely identical, they may be safely collapsed after the rule is documented. The same principle applies to duplicate product listings in ecommerce catalogs, where similar names do not always indicate the same product.

Check row counts and source coverage

A merged file should be auditable. Compare the number of data rows read from each source with the number retained after processing. If five files contain 200, 150, 80, 120, and 50 rows, a simple append should yield 600 rows before intentional filtering or deduplication. A smaller total without an explanation is a warning.

Retain source_file on every row so problems can be traced back to the original export. For stronger provenance, also store a batch identifier, import time, and checksum of the original file in a separate manifest. These details help distinguish a reprocessed batch from a genuinely new supplier submission.

Build a useful exception report

An exception report is often more valuable than a success message. Capture missing required columns, unrecognized headers, invalid numeric values, duplicate canonical names, unreadable files, and rows with missing business keys. Group exceptions by file and severity so the team can decide whether to stop the entire import or quarantine only affected records.

Do not quietly skip a problematic file and declare the merge complete. A partial output can be useful for investigation, but it must be clearly labeled incomplete. For scheduled jobs, establish a minimum expected file count and row-count range, and send failures to monitoring or a review queue.

Avoid accidental encoding damage

If source files come from different systems, encoding problems may appear as replacement characters or garbled names. Specify the known encoding for each source rather than applying the same fallback to every file. If decoding fails, quarantine the file for inspection. Guessing an encoding and saving the result can permanently obscure the original text in downstream copies.

The separate tutorial on fixing garbled special characters in CSV files covers diagnosis and repair in more depth. The merge process should preserve raw input files so that a decoding decision can be corrected later.

Test the output in the destination system

Opening a merged CSV in a spreadsheet is only a visual check. Spreadsheet software may automatically convert text identifiers, dates, and long numeric-looking values. Validate the exported file with a programmatic read using explicit types, then test a small import into the actual destination database, catalog platform, or reporting tool.

Confirm field order, required values, record counts, delimiters, quoting, newline handling, and Unicode text. Check that commas inside product descriptions remain properly quoted and that multiline descriptions do not split records. The destination’s schema and import settings are the final authority on whether the file is usable.

Make the workflow repeatable

A reliable recurring merge separates configuration from code. Store column mappings, required fields, encodings, and supplier-specific rules in reviewed configuration files. Version these rules and record which version produced each output. Use a staging directory and write the finished file only after validation succeeds; for production systems, an atomic replacement strategy helps prevent consumers from reading a half-written output.

Keep a small test suite with representative files: one normal source, one with reordered columns, one with missing optional columns, one with unknown headers, and one with conflicting mapped fields. Add regression cases whenever a supplier changes its export. These practices turn a one-off script into a maintainable data transformation process, similar to the broader workflows described in data cleansing.

Conclusion

Combining CSV files with different columns is primarily a schema-management problem, not a concatenation problem. Python and pandas make the mechanical work easy, but trustworthy results depend on explicit field mappings, text-safe identifiers, careful missing-value handling, duplicate rules, and measurable validation. Start with a documented output schema, keep the source files intact, and make every exception traceable. The resulting dataset will be easier to audit, update, and import as source formats evolve.