Merge CSV files and keep the source filenames

01Merge two files into five rows

If numbers look wrong after merging files, it can be hard to trace them to their source. This example keeps the original four columns and adds source_file at the end. The goal is to identify the original filename directly from each result row. First, add the input files' data row counts of 2 and 3, and write down the expected total of 5.

1. Find inputs/sales_01.csv and inputs/sales_02.csv in the extracted folder.
2. Open both files in a text editor and check that the first line is date,item,quantity,unit_price.
3. Run the command below in a terminal in the folder containing example.py.
4. Open outputs/merged.csv and check for five data rows and the source_file column.

python example.py

Separate input and output folders keep the previous merged.csv from becoming an input on a later run. The code also locates inputs relative to example.py. If you keep the folder structure when extracting the ZIP, you do not need to change the paths.

02Match the column structure before merging

Both the column names and their order must match. For example, the script stops if a file uses qty instead of quantity or moves unit_price to a different position. It does not guess meanings and align columns automatically, so you can spot when formats from different departments have been mixed.

The synthetic data includes “메모지, 대형” (large memo pad) and “표지 "파랑"” (cover "blue"), which contains double quotation marks. Reading CSV by simply using split on commas can break these values into the wrong fields. Let the standard csv module handle quoting rules, then write the rows back as read.

03Full code used in this example

collect_rows checks the header and rows in each CSV and adds the original filename. Only after all checks pass does main create a new output. This order prevents a partial merge of the earlier files from being left behind when a later file has incorrect columns.

"""inputs의 같은 구조 CSV를 결합합니다. 각 행에 원본 파일명을 붙입니다."""

import csv
import sys
from pathlib import Path

BASE = Path(__file__).resolve().parent
INPUT = BASE / "inputs"
OUTPUT = BASE / "outputs"
FIELDS = ["date", "item", "quantity", "unit_price"]


def collect_rows():
    if INPUT.is_symlink() or not INPUT.is_dir():
        raise ValueError("inputs 폴더가 없거나 링크입니다.")
    if getattr(INPUT, "is_junction", lambda: False)():
        raise ValueError("연결 디렉터리는 처리하지 않습니다.")
    # inputs 바로 아래의 .csv 파일만 읽습니다. 순서를 명시해 결과를 재현합니다.
    paths = sorted(
        (p for p in INPUT.iterdir() if p.suffix.lower() == ".csv"),
        key=lambda p: p.name.casefold(),
    )
    if not paths:
        raise ValueError("inputs에 CSV 파일이 없습니다.")
    merged = []
    counts = []
    for path in paths:
        if path.is_symlink() or not path.is_file():
            raise ValueError(f"일반 CSV 파일이 아닙니다: {path.name}")
        with path.open("r", encoding="utf-8-sig", newline="") as stream:
            reader = csv.DictReader(stream, strict=True)
            if reader.fieldnames != FIELDS:
                raise ValueError(f"{path.name}: 열 이름과 순서는 {FIELDS}여야 합니다.")
            count = 0
            for row in reader:
                # 열이 많으면 None 키, 적으면 None 값이 생깁니다.
                if None in row or any(value is None or not value.strip() for value in row.values()):
                    raise ValueError(f"{path.name}, {reader.line_num}행: 열 수 또는 빈 값을 확인하세요.")
                merged.append({**row, "source_file": path.name})
                count += 1
            counts.append((path.name, count))
    return merged, counts


def main():
    if OUTPUT.exists() or OUTPUT.is_symlink():
        raise FileExistsError("outputs가 이미 있습니다. 기존 결과를 옮긴 뒤 실행하세요.")
    rows, counts = collect_rows()  # 모든 파일을 검증한 다음에 출력합니다.
    OUTPUT.mkdir()
    target = OUTPUT / "merged.csv"
    with target.open("x", encoding="utf-8-sig", newline="") as stream:
        writer = csv.DictWriter(stream, fieldnames=FIELDS + ["source_file"])
        writer.writeheader()
        writer.writerows(rows)
    for name, count in counts:
        print(f"{name}: {count}행")
    print(f"완료: {len(counts)}개 파일 → {len(rows)}행")
    print(target)
    return 0


if __name__ == "__main__":
    try:
        raise SystemExit(main())
    except (OSError, ValueError, csv.Error) as error:
        print(f"중지: {error}", file=sys.stderr)
        raise SystemExit(2)

The checks use the way DictReader adds a None key when there are too many data fields and a None value when there are too few. Empty strings and whitespace-only values are also rejected. Setting newline to an empty string and specifying an encoding that handles the UTF-8 BOM keeps newline and Korean text handling consistent.

04Compare both row counts and sources

The terminal displays “sales_01.csv: 2행” (two rows), “sales_02.csv: 3행” (three rows), and “완료: 2개 파일 → 5행” (complete: two files → five rows), in that order. The header on the first line is not counted as data. Seeing 노트 (notebook) twice is not an error. Both original rows are retained; no totals or duplicate removal have been applied.

05Make four checks before trusting the result

1. Check that the sum of input row counts, 5, matches the number of result data rows.
2. Check that two rows have source_file set to sales_01.csv and three have it set to sales_02.csv.
3. Check that the item name containing a comma remains in one cell and that the double quotation marks have not disappeared.
4. Reopen both original files and confirm their contents are unchanged. Also check that running the script again stops because outputs already exists.

In actual work, checking row counts is separate from checking totals. Even if monetary totals happen to match, one row may be missing and another duplicated. Since this step does not perform calculations, start by comparing counts by source and the original text.

06Practice adding a third file

Before running again, rename the first outputs folder to keep it. Add sales_03.csv inside inputs with the same header and one data row. The expected data count is six. Check that the new row has sales_03.csv in source_file to see how adding an input affects the tracking column.

Only files with the .csv extension directly inside inputs are included. The script does not search subfolders recursively or merge text notes or Excel workbooks. If no CSV files are found, it treats this as an incorrect input selection and stops. A valid header-only CSV counts as 0 rows and does not prevent the other files from being merged.

07Check errors instead of accepting partial results

Error line numbers refer to physical lines read from the CSV. In complex CSV files with line breaks inside quoted values, these may differ from data row numbers in a spreadsheet. Use the filename in the message to narrow down the source first.

08Where merging ends and data validation begins

This script checks column structure, empty values, and CSV syntax. It does not determine whether quantity is numeric, date is a valid date, or amounts meet your work rules. If you need calculations after a simple merge, add numeric conversion and allowed-range checks as separate validation steps.

All rows are collected in memory and saved after validation, so this example is suitable for practicing with small work files. Very large CSV files need a design with temporary output and staged processing. A disk error during saving can leave partial results; do not treat the run as successful without a completion message. Concurrent input changes and network-path failures are outside the verification scope.

Execution and verification record

2026-09-19 · Windows 11 · CPython 3.12.14 · No additional packages · Run in a temporary copy of the distribution

• Merged 2 CSV files into 5 rows and checked source_file counts
• Preserved cells containing commas and double quotation marks
• Rejected incorrect headers, missing or extra fields, empty values, and malformed quoting before creating output
• Checked handling of header-only CSV files and no CSV files
• Confirmed unchanged original SHA-256 hashes and preservation of existing output

Site-wide writing and verification principles

References

The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.

• Official Python documentation · csv and DictReader ↗ (New tab)
• Official Python documentation · pathlib file paths ↗ (New tab)