반응형
이런 상황, 겪어본 적 있으신가요?
매월 말, 팀원 10명이 각자 작성한 엑셀 보고서를 하나로 합쳐야 하는 상황. 파일을 하나씩 열고, 복사하고, 붙여넣고, 저장하는 작업을 반복하다 보면 어느새 1~2시간이 훌쩍 지나 있습니다. 파일이 100개라면? 생각만 해도 아찔하죠.
이 글에서는 파이썬 코드 몇 줄로 폴더 안의 엑셀 파일을 전부 하나로 합치는 방법을 다룹니다. 코딩을 잘 몰라도 따라할 수 있도록 단계별로 설명합니다.
사전 준비
필요한 라이브러리 설치
이 글에서는 pandas와 openpyxl 두 가지 라이브러리를 사용합니다. 터미널(또는 Anaconda Prompt)을 열고 아래 명령어를 실행합니다.
pip install pandas openpyxl- pandas: 엑셀 파일을 읽고 데이터를 합치는 핵심 라이브러리입니다.
- openpyxl: pandas가 .xlsx 파일을 읽고 쓸 때 내부적으로 사용하는 엔진입니다. 반드시 함께 설치해야 합니다.
폴더 구조 예시
합칠 엑셀 파일들이 하나의 폴더 안에 있다고 가정합니다. 아래와 같은 구조를 기준으로 설명합니다.
project/
excel_files/
report_01.xlsx
report_02.xlsx
report_03.xlsx
...
report_100.xlsx
merge.py ← 우리가 작성할 파이썬 파일
상황별 코드
엑셀 파일을 합치는 방식은 상황에 따라 다릅니다. 가장 흔한 세 가지 경우를 모두 다룹니다.
Case 1. 모든 파일의 시트 구조가 동일한 경우 (가장 흔한 경우)
각 파일의 첫 번째 시트에 같은 열(컬럼) 구조의 데이터가 들어 있고, 이를 세로로 쌓아 합치는 경우입니다. 월별 매출 보고서, 팀원별 실적 파일 등이 여기에 해당합니다.
# merge.py
import pandas as pd
import glob
import os
# 합칠 파일들이 있는 폴더 경로
folder_path = "excel_files"
# 폴더 안의 모든 .xlsx 파일 경로를 리스트로 가져오기
file_list = glob.glob(os.path.join(folder_path, "*.xlsx"))
print(f"발견된 파일 수: {len(file_list)}개")
# 각 파일을 읽어서 리스트에 담기
df_list = []
for file in file_list:
df = pd.read_excel(file)
df["출처파일"] = os.path.basename(file) # 어느 파일에서 왔는지 기록 (선택)
df_list.append(df)
print(f"읽는 중: {os.path.basename(file)}")
# 전체 데이터를 하나로 합치기
merged_df = pd.concat(df_list, ignore_index=True)
# 결과 저장
output_path = "merged_result.xlsx"
merged_df.to_excel(output_path, index=False)
print(f"\n완료! 저장 위치: {output_path}")
print(f"총 {len(merged_df)}행이 합쳐졌습니다.")
Case 2. 파일마다 여러 시트가 있고, 특정 시트만 합쳐야 하는 경우
각 파일 안에 "1월", "2월", "요약" 같은 여러 시트가 있을 때, 특정 이름의 시트만 골라서 합칩니다.
# merge_sheet.py
import pandas as pd
import glob
import os
folder_path = "excel_files"
target_sheet = "요약" # 합칠 시트 이름
file_list = glob.glob(os.path.join(folder_path, "*.xlsx"))
df_list = []
for file in file_list:
try:
df = pd.read_excel(file, sheet_name=target_sheet)
df["출처파일"] = os.path.basename(file)
df_list.append(df)
print(f"읽는 중: {os.path.basename(file)} - [{target_sheet}] 시트")
except Exception as e:
# 해당 시트가 없는 파일은 건너뜀
print(f"건너뜀: {os.path.basename(file)} → {e}")
merged_df = pd.concat(df_list, ignore_index=True)
merged_df.to_excel("merged_result.xlsx", index=False)
print(f"\n완료! 총 {len(merged_df)}행")
Case 3. 파일마다 모든 시트를 시트별로 나눠서 합치는 경우
여러 파일에 걸쳐 있는 "1월" 시트끼리, "2월" 시트끼리 각각 합쳐서 하나의 결과 파일에 시트별로 저장하는 경우입니다.
# merge_by_sheet.py
import pandas as pd
import glob
import os
from collections import defaultdict
folder_path = "excel_files"
file_list = glob.glob(os.path.join(folder_path, "*.xlsx"))
# 시트 이름을 키로, 데이터프레임 리스트를 값으로 담는 딕셔너리
sheet_data = defaultdict(list)
for file in file_list:
xl = pd.ExcelFile(file)
for sheet_name in xl.sheet_names:
df = xl.parse(sheet_name)
df["출처파일"] = os.path.basename(file)
sheet_data[sheet_name].append(df)
print(f"읽는 중: {os.path.basename(file)}")
# 시트별로 합쳐서 하나의 엑셀 파일에 저장
output_path = "merged_by_sheet.xlsx"
with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
for sheet_name, df_list in sheet_data.items():
merged = pd.concat(df_list, ignore_index=True)
merged.to_excel(writer, sheet_name=sheet_name, index=False)
print(f"저장 중: [{sheet_name}] 시트 → {len(merged)}행")
print(f"\n완료! 저장 위치: {output_path}")
자주 발생하는 문제와 해결법
문제 1. 파일마다 열(컬럼) 이름이 조금씩 다를 때
어떤 파일은 "매출액", 어떤 파일은 "매출 금액"처럼 열 이름이 조금씩 다르면 concat 후 열이 따로 생겨 데이터가 어긋납니다. 이럴 때는 읽을 때 열 이름을 통일해줍니다.
# 읽은 후 열 이름 강제 통일
df.columns = ["날짜", "담당자", "매출액", "비고"] # 원하는 열 이름으로 직접 지정
# 또는 특정 열 이름만 변경
df = df.rename(columns={"매출 금액": "매출액", "담당자명": "담당자"})
문제 2. 헤더(첫 행)가 여러 줄이거나 위에 불필요한 행이 있을 때
엑셀 파일 상단에 회사 로고나 제목 행이 있어서 실제 데이터가 3행부터 시작하는 경우, skiprows 옵션으로 건너뜁니다.
# 위에서 2줄을 건너뛰고, 3번째 줄을 헤더로 사용
df = pd.read_excel(file, skiprows=2)
# 헤더 없이 순수 데이터만 읽기
df = pd.read_excel(file, header=None)
문제 3. .xls 파일(구버전 엑셀)이 섞여 있을 때
.xls 파일은 openpyxl이 아닌 xlrd 엔진이 필요합니다. 두 형식이 섞여 있다면 확장자에 따라 엔진을 분기합니다.
pip install xlrdimport pandas as pd
import glob
import os
folder_path = "excel_files"
# .xlsx와 .xls 모두 검색
file_list = glob.glob(os.path.join(folder_path, "*.xlsx")) + \
glob.glob(os.path.join(folder_path, "*.xls"))
df_list = []
for file in file_list:
ext = os.path.splitext(file)[1].lower()
if ext == ".xlsx":
df = pd.read_excel(file, engine="openpyxl")
elif ext == ".xls":
df = pd.read_excel(file, engine="xlrd")
df["출처파일"] = os.path.basename(file)
df_list.append(df)
merged_df = pd.concat(df_list, ignore_index=True)
merged_df.to_excel("merged_result.xlsx", index=False)
문제 4. 파일이 너무 많아서 속도가 느릴 때
반복문 안에서 concat을 매번 호출하면 파일 수에 비례해 속도가 급격히 느려집니다. 반드시 리스트에 모두 담은 뒤 마지막에 한 번만 호출하는 방식을 사용하세요.
# 느린 방식 (사용하지 마세요)
merged_df = pd.DataFrame()
for file in file_list:
df = pd.read_excel(file)
merged_df = pd.concat([merged_df, df]) # ← 반복할수록 느려짐
# 빠른 방식 (권장)
df_list = []
for file in file_list:
df_list.append(pd.read_excel(file))
merged_df = pd.concat(df_list, ignore_index=True) # ← 마지막에 한 번만
실전에서 바로 쓰는 완성 코드
위 내용을 모두 담아 실전에서 바로 사용할 수 있는 완성본입니다. 폴더 경로만 바꿔서 실행하면 됩니다.
실행 예시
python merge.py --folder excel_files --sheet 요약 --output merged_result.xlsx# merge.py
import pandas as pd
import glob
import os
import argparse
def get_args():
p = argparse.ArgumentParser(description="엑셀 파일 일괄 합치기")
p.add_argument("--folder", default="excel_files", help="엑셀 파일이 있는 폴더 경로")
p.add_argument("--sheet", default=None, help="합칠 시트 이름 (없으면 첫 번째 시트)")
p.add_argument("--output", default="merged_result.xlsx", help="결과 파일 이름")
p.add_argument("--skiprows", type=int, default=0, help="상단에서 건너뜀 행 수")
return p.parse_args()
def read_excel_safe(file, sheet_name, skiprows):
"""파일 읽기 실패 시 None 반환 (오류 파일은 건너뜀)"""
try:
ext = os.path.splitext(file)[1].lower()
engine = "openpyxl" if ext == ".xlsx" else "xlrd"
df = pd.read_excel(file, sheet_name=sheet_name,
skiprows=skiprows, engine=engine)
df["출처파일"] = os.path.basename(file)
return df
except Exception as e:
print(f" [건너뜀] {os.path.basename(file)} → {e}")
return None
def main():
args = get_args()
# .xlsx / .xls 모두 수집
file_list = sorted(
glob.glob(os.path.join(args.folder, "*.xlsx")) +
glob.glob(os.path.join(args.folder, "*.xls"))
)
if not file_list:
print(f"[오류] '{args.folder}' 폴더에 엑셀 파일이 없습니다.")
return
print(f"[INFO] 발견된 파일 수: {len(file_list)}개")
print(f"[INFO] 대상 시트 : {args.sheet if args.sheet else '첫 번째 시트'}")
print(f"[INFO] 건너뜀 행 수 : {args.skiprows}\n")
df_list = []
for file in file_list:
df = read_excel_safe(file, args.sheet, args.skiprows)
if df is not None:
df_list.append(df)
print(f" 읽기 완료: {os.path.basename(file)} ({len(df)}행)")
if not df_list:
print("\n[오류] 읽을 수 있는 파일이 없습니다.")
return
merged_df = pd.concat(df_list, ignore_index=True)
merged_df.to_excel(args.output, index=False, engine="openpyxl")
print(f"\n[완료] 저장 위치 : {args.output}")
print(f"[완료] 합산 파일 수 : {len(df_list)}개")
print(f"[완료] 합산 총 행수 : {len(merged_df)}행")
if __name__ == "__main__":
main()
코드 핵심 옵션 설명
- --folder: 합칠 엑셀 파일이 들어 있는 폴더 경로를 지정합니다. 기본값은
excel_files입니다. - --sheet: 합칠 시트 이름을 지정합니다. 지정하지 않으면 각 파일의 첫 번째 시트를 읽습니다.
- --output: 결과 파일 이름을 지정합니다. 기본값은
merged_result.xlsx입니다. - --skiprows: 파일 상단에 불필요한 행이 있을 때 건너뜀 행 수를 지정합니다. 기본값은 0입니다.
- 출처파일 열: 합쳐진 데이터에 어느 파일에서 온 행인지를 자동으로 기록합니다. 나중에 데이터를 역추적할 때 유용합니다.
- 오류 파일 건너뜀: 특정 파일이 손상됐거나 시트가 없어도 나머지 파일은 정상 처리됩니다.
마치며..
오늘은 파이썬을 이용해 반복적인 엑셀 병합 업무를 자동화하는 방법을 알아보았습니다. 기술의 목적은 결국 인간의 시간을 더 가치 있는 곳에 쓰게 하는 것입니다. 단순한 복사·붙여넣기 작업은 파이썬에게 맡기고, 여러분은 합쳐진 데이터를 바탕으로 더 중요한 의사결정에 집중하시길 바랍니다.
만약 코드 실행 중 ImportError: No module named 'openpyxl' 같은 오류가 발생한다면, 아래 관련 포스팅의 환경 설정 가이드를 참고해 보시기 바랍니다.
관련 내용
- [VS Code/Anaconda] Visual Studio Code 프로젝트에 conda 가상환경 적용
- [Anaconda/Windows] Anaconda 다운로드 및 설치
- [Pytorch] ImportError: No module named 'torch' (라이브러리 인식 문제 해결법)
본 포스팅의 코드는 자유롭게 수정 및 배포가 가능하나, 상업적 이용 시에는 출처를 반드시 밝혀주시기 바랍니다.
반응형