파이썬 라이브러리를 활용한 엑셀(Excel) 데이터 파일 취합 자동화

매월 또는 매주 정기적으로 발생하는 수십 개의 부서별 매출 자료, 정산 내역, 혹은 설문조사 결과 등의 엑셀 파일들을 하나의 파일로 합치는 작업은 많은 직장인들의 업무 시간을 갉아먹는 대표적인 단순 반복 업무입니다. 파일을 일일이 열어서 복사(Ctrl+C)하고 마스터 시트에 붙여넣기(Ctrl+V)하는 과정은 시간이 오래 걸릴 뿐만 아니라, 누락이나 중복 같은 실수가 발생할 확률도 매우 높습니다. 이번 포스팅에서는 데이터 분석 전문 파이썬 라이브러리인 판다스(pandas)를 활용하여, 폴더 내의 모든 엑셀 데이터를 단 몇 초 만에 하나의 데이터프레임으로 일괄 취합하고 정제하는 자동화 방법을 소개합니다.

문서 변환 자동화가 필요한 이유

수십 개의 엑셀 시트를 하나로 합쳐야 하는 업무적 한계

단순히 파일 2~3개를 합치는 수준이라면 수작업이 빠를 수 있지만, 파일의 개수가 수십 개를 넘어가거나 수만 행에 달하는 대용량 데이터라면 이야기가 달라집니다. 엑셀 프로그램 자체의 로딩 시간과 메모리 제한으로 인해 컴퓨터가 멈추는 현상이 발생하기도 하며, 작업자의 집중력이 흐려지면서 특정 데이터를 누락하는 치명적인 인적 오류(Human Error)가 발생하기 쉽습니다. 이러한 단순 취합 프로세스를 파이썬 코드로 자동화하면, 데이터의 양에 관계없이 매번 동일한 규칙으로 신속하고 정확하게 결과물을 도출할 수 있어 업무 생산성이 극대화됩니다.

파이썬을 활용한 엑셀 취합 환경 세팅

pandas 라이브러리 설치 및 연동 환경 세팅

대량의 데이터를 고속으로 처리하고 취합하기 위해 파이썬의 핵심 데이터 분석 라이브러리인 판다스(pandas)와 엑셀 파일을 읽고 쓰기 위한 엔진인 openpyxl 패키지를 사용합니다. 자동화 스크립트를 작성하기 전, 시스템 명령 프롬프트(CMD) 또는 터미널 창을 열고 아래 명령어를 입력하여 필요한 라이브러리들을 먼저 설치해 주어야 합니다.

Bash

pip install pandas openpyxl

자동화 스크립트 작성 및 실행 과정

핵심 코드 리뷰 및 적용 방법

환경 세팅이 완료되었다면 지정된 폴더 안의 모든 엑셀 파일(.xlsx)을 탐색하여 첫 번째 시트의 데이터를 자동으로 읽어와 하나의 엑셀 파일로 병합해 주는 전체 파이썬 스크립트 코드입니다.

Python

import os
import pandas as pd

def batch_merge_excel_files(folder_path, output_file_name):
    # 지정 폴더 내의 모든 파일 목록 탐색
    all_files = os.listdir(folder_path)
    
    # 확장자가 .xlsx인 엑셀 파일만 정확하게 필터링
    excel_files = [f for f in all_files if f.endswith('.xlsx') and not f.startswith('~$')]
    
    if not excel_files:
        print("취합할 엑셀 파일이 폴더 내에 존재하지 않습니다.")
        return
        
    print(f"총 {len(excel_files)}개의 엑셀 파일을 발견했습니다. 취합을 시작합니다.")
    
    # 개별 데이터프레임을 저장할 빈 리스트 생성
    df_list = []
    
    for file_name in excel_files:
        file_path = os.path.join(folder_path, file_name)
        try:
            # 엑셀 파일 읽기 (첫 번째 시트 기준)
            df = pd.read_excel(file_path)
            
            # 추후 데이터 출처 확인을 위해 파일명을 새로운 열로 추가
            df['출처파일명'] = file_name
            
            df_list.append(df)
            print(f"읽기 완료: {file_name}")
        except Exception as e:
            print(f"파일 읽기 실패 ({file_name}): {e}")
            
    # 리스트에 모인 모든 데이터프레임을 위아래로 이어 붙이기
    combined_df = pd.concat(df_list, ignore_index=True)
    
    # 최종 취합본 저장 경로 지정 및 생성
    output_path = os.path.join(folder_path, output_file_name)
    combined_df.to_excel(output_path, index=False)
    
    print(f"\n모든 엑셀 파일 취합이 완료되었습니다.")
    print(f"최종 저장 경로: {output_path}")

# 자동화 작업을 수행할 대상 폴더 및 결과 파일명 설정
target_folder = r"C:\Your\Excel\Folder\Path"
result_file = "최종_엑셀_취합본.xlsx"

if __name__ == "__main__":
    batch_merge_excel_files(target_folder, result_file)

이 코드는 pd.concat() 함수를 사용하여 수십 개의 데이터를 메모리 상에서 고속으로 결합합니다. 특히 데이터의 양이 늘어나더라도 서식 깨짐 없이 값 위주로 완벽하게 병합해 주는 장점이 있습니다. 코드 하단의 target_folder 변수에 변환하고자 하는 실제 엑셀 파일들이 모여 있는 디렉터리 경로를 지정한 뒤 스크립트를 구동하면, 기존 열 구조를 유지한 채 파일들이 순서대로 합쳐진 마스터 파일이 자동으로 생성됩니다.