CSV·엑셀 파일 여러 개를 하나로 자동 병합하기

CSV·엑셀 파일 여러 개를 하나로 자동 병합하기

작성자:

월말마다 지점별로 날아온 CSV 서른 개를 엑셀에 하나씩 열어 복사·붙여넣기 하고 계신가요. 파일 하나당 1분씩만 잡아도 30분이고, 중간에 한 번 헤더 줄까지 같이 붙여넣으면 그 행을 찾느라 다시 10분이 날아갑니다. 1년이면 여섯 시간이 넘는 작업인데, 실수 위험까지 감안하면 자동화 이득이 가장 확실한 종류의 일입니다. 이번 글에서는 csv 파일 합치기를 파이썬과 파워셸로 처리하는 방법을, 설치가 필요 없는 방식부터 엑셀 다중 시트까지 순서대로 다룹니다.

먼저 확인할 것: 파일들의 열 구성이 같은가

병합 작업이 실패하는 이유는 대부분 코드가 아니라 데이터입니다. 시작 전에 세 가지만 확인하세요.

  • 열 이름과 순서가 같은가. 어떤 지점만 “비고” 열을 추가했다면 그 열은 나머지 행에서 빈칸이 됩니다.
  • 헤더가 첫 줄에 있는가. 위에 제목 행이나 빈 줄이 끼어 있으면 그 줄이 열 이름이 되어 버립니다.
  • 인코딩이 같은가. 엑셀에서 “CSV(쉼표로 분리)”로 저장한 파일은 UTF-8이 아니라 CP949인 경우가 많습니다.

세 번째는 한글 깨짐 오류 해결 총정리에서 다룬 그 문제입니다. 병합 스크립트가 UnicodeDecodeError로 멈추면 십중팔구 이 경우입니다.

설치 없이: 파워셸 한 줄로 CSV 합치기

윈도우에는 이미 도구가 들어 있습니다. 파이썬을 깔 수 없는 회사 PC라면 파워셸이 가장 빠른 길입니다. 합칠 CSV들이 있는 폴더에서 파워셸을 열고 아래를 실행하세요.

# 폴더 안의 모든 CSV를 하나로 합칩니다 (헤더는 한 번만)
Get-ChildItem -Filter *.csv |
    ForEach-Object { Import-Csv $_.FullName } |
    Export-Csv merged.csv -NoTypeInformation -Encoding UTF8

핵심은 Import-Csv입니다. 텍스트를 그대로 이어 붙이는 게 아니라 열 이름을 이해한 상태로 읽기 때문에, 헤더 줄이 중간중간 섞여 들어가지 않습니다. 명령 프롬프트의 copy *.csv merged.csv가 위험한 게 바로 이 지점입니다 — 두 번째 파일부터의 헤더가 전부 데이터 행으로 들어갑니다.

Windows PowerShell 5.1에서 -Encoding UTF8은 BOM이 붙은 UTF-8을 만듭니다. 합친 파일을 엑셀로 열 거라면 오히려 BOM이 있는 편이 한글이 안 깨져서 낫고, 다른 프로그램에 넘길 거라면 BOM을 신경 써야 합니다.

파이썬 + pandas: 기본형

파일 개수가 많거나 매달 반복할 작업이라면 파이썬 쪽이 확장하기 좋습니다. 라이브러리 하나만 설치하면 됩니다.

# pip install pandas
import glob
import pandas as pd

files = sorted(glob.glob("data/*.csv"))
print(f"대상 파일 {len(files)}개")

frames = [pd.read_csv(f, dtype=str, encoding="utf-8-sig") for f in files]
merged = pd.concat(frames, ignore_index=True)

merged.to_csv("merged.csv", index=False, encoding="utf-8-sig")
print(f"{len(merged)}행 저장 완료")

이 코드에서 의도적으로 넣은 옵션이 두 개 있습니다. dtype=str은 모든 값을 문자열로 읽으라는 뜻인데, 이걸 빼면 pandas가 열마다 자료형을 추측합니다. 그러면 00123 같은 사번이 123이 되고, 전화번호 앞자리 0이 사라집니다. encoding="utf-8-sig"는 BOM이 있든 없든 UTF-8을 제대로 읽습니다. 저장할 때도 같은 값을 주면 결과 파일을 엑셀로 바로 열 수 있습니다.

출처를 남겨야 나중에 추적이 됩니다

합치고 나면 “이 행이 어느 파일에서 온 거지?”를 알 수 없게 됩니다. 숫자가 이상할 때 원본을 되짚을 방법이 사라지는 셈이라, 열 한두 개를 더 붙여 두는 게 좋습니다.

import glob, os
import pandas as pd

frames = []
for path in sorted(glob.glob("data/*.csv")):
    df = pd.read_csv(path, dtype=str, encoding="utf-8-sig")
    df["출처파일"] = os.path.basename(path)          # 어느 파일에서 왔는지
    df["지점명"] = os.path.basename(path).split("_")[0]  # 매출_강남_202609.csv → 매출
    frames.append(df)

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

파일명 규칙이 매출_강남_202609.csv처럼 일정하다면 split("_")만으로도 지점명·기간을 열로 뽑아낼 수 있습니다. 원본 파일명에 정보를 넣어 두는 습관이 여기서 값을 합니다.

엑셀(xlsx) 파일 합치기 — 시트가 여러 개일 때

엑셀은 파일 하나에 시트가 여러 개라 CSV보다 한 겹 더 들어갑니다. sheet_name=None을 주면 모든 시트를 한 번에 읽어 딕셔너리로 돌려줍니다.

# pip install pandas openpyxl
import glob
import pandas as pd

frames = []
for path in sorted(glob.glob("보고서/*.xlsx")):
    # sheet_name=None 이면 모든 시트를 {시트이름: 데이터} 형태로 한 번에 읽습니다
    sheets = pd.read_excel(path, sheet_name=None, dtype=str)
    for name, df in sheets.items():
        df["시트명"] = name
        frames.append(df)

merged = pd.concat(frames, ignore_index=True)
merged.to_excel("merged.xlsx", index=False)

pd.read_excel은 xlsx를 읽을 때 openpyxl을 씁니다. 설치를 빼먹으면 파일을 여는 순간 오류가 나니 함께 설치하세요. 옛날 .xls 형식이라면 xlrd가 따로 필요한데, 이 경우는 엑셀에서 xlsx로 다시 저장하는 쪽이 훨씬 간단합니다.

반대 방향 — 합친 데이터를 다시 나누는 작업도 자주 필요합니다.

# 반대로, 합친 데이터를 지점별 시트로 쪼개서 한 파일에 담기
import pandas as pd

merged = pd.read_excel("merged.xlsx", dtype=str)

with pd.ExcelWriter("지점별.xlsx", engine="openpyxl") as writer:
    for 지점, group in merged.groupby("지점명"):
        # 시트 이름은 31자 제한이 있어 잘라 줍니다
        group.to_excel(writer, sheet_name=str(지점)[:31], index=False)

방법별 비교

방법설치엑셀 파일적합한 상황
copy *.csv (cmd)불필요불가헤더가 없는 로그 파일 정도
파워셸 Import-Csv불필요불가회사 PC, 일회성 작업
pandaspip 설치가능매달 반복, 열 가공이 필요할 때
엑셀 Power Query엑셀 내장가능코드 없이 GUI로 하고 싶을 때

엑셀만 쓰는 동료에게 넘겨야 한다면 Power Query(데이터 탭 > 데이터 가져오기 > 폴더에서)도 선택지입니다. 폴더를 지정해 두면 파일이 추가될 때 새로 고침만 눌러도 반영된다는 게 장점이고, 변환 단계가 복잡해지면 관리가 어렵다는 게 단점입니다.

실제로 사고가 나는 지점

아래 스크립트는 합치기 전에 문제를 먼저 알려 줍니다. 조용히 잘못 합쳐진 파일을 며칠 뒤에 발견하는 것보다, 실행할 때 경고가 뜨는 편이 훨씬 낫습니다.

import glob
import pandas as pd

files = sorted(glob.glob("data/*.csv"))
base = None
frames = []

for path in files:
    df = pd.read_csv(path, dtype=str, encoding="utf-8-sig")

    if df.empty:
        print(f"[건너뜀] 빈 파일: {path}")
        continue

    cols = list(df.columns)
    if base is None:
        base = cols
    elif cols != base:
        print(f"[경고] 열 구성이 다릅니다: {path}")
        print(f"    빠진 열: {set(base) - set(cols)}")
        print(f"    추가된 열: {set(cols) - set(base)}")

    frames.append(df)

merged = pd.concat(frames, ignore_index=True)
print(f"파일 {len(frames)}개 / 총 {len(merged)}행")

그리고 인코딩이 제각각인 파일 더미를 받았다면 이렇게 넘깁니다.

def read_any(path):
    """엑셀에서 "CSV로 저장"한 파일은 대개 cp949입니다."""
    for enc in ("utf-8-sig", "cp949", "utf-8"):
        try:
            return pd.read_csv(path, dtype=str, encoding=enc)
        except UnicodeDecodeError:
            continue
    raise ValueError(f"인코딩을 못 찾았습니다: {path}")
  • 빈 파일. 헤더만 있고 데이터가 없는 파일은 df.empty로 걸러 냅니다.
  • 열 이름의 공백. "금액 "과 "금액"은 다른 열로 취급됩니다. df.columns = df.columns.str.strip()을 한 줄 넣어 두세요.
  • 결과 파일을 같은 폴더에 저장하는 실수. data/*.csv로 읽으면서 결과도 data/에 두면, 다음 실행 때 결과 파일까지 다시 합쳐집니다. 저장 위치는 반드시 다른 폴더로 잡으세요.
  • 중복 행. 같은 파일을 두 번 받는 일이 흔합니다. merged.drop_duplicates()를 한 번 통과시키면 안전합니다.

마무리

지금 합칠 파일이 있는 폴더에서 파워셸을 열고 위의 Import-Csv 한 줄부터 실행해 보세요. 결과가 맞는지 눈으로 확인되면, 그다음에 파이썬으로 옮겨 출처 열을 붙이고 검증 코드를 얹는 순서가 부담이 적습니다. 매달 하는 작업이라면 윈도우 작업 스케줄러에 걸어 두는 것까지가 한 세트입니다.

다음 글에서는 VS Code의 멀티커서와 다중 선택 편집을 다룹니다. 이런 스크립트를 쓸 때 반복되는 줄을 한 번에 고치는 방법입니다.

댓글 남기기