업무를 하다 보면 똑같은 형식의 엑셀 파일이 수십 개, 수백 개씩 쌓이는 경우가 있어요. 매달 들어오는 판매 보고서나 주간 실적 파일들을 일일이 복사해서 붙여넣기 하는 건 정말 시간 낭비예요. 파이썬을 사용하면 이런 반복 작업을 단 몇 줄의 코드로 해결할 수 있어요.
필요한 도구 설치하기
파이썬에서 엑셀 파일을 다루려면 pandas 라이브러리가 필요해요. 터미널이나 명령 프롬프트에서 다음 명령어를 입력해서 설치해요.
pip install pandas openpyxl
openpyxl은 엑셀 파일을 읽고 쓰는 데 필요한 도구예요. pandas와 함께 설치하면 엑셀 파일을 쉽게 다룰 수 있어요.
기본 병합 코드
가장 간단한 형태의 엑셀 파일 병합 코드예요. 폴더 안에 있는 모든 엑셀 파일을 하나로 합쳐요.
import pandas as pd
import os
# 병합할 파일들이 있는 폴더 경로
folder_path = "C:/Users/사용자/엑셀파일폴더"
# 빈 데이터프레임 만들기
all_data = pd.DataFrame()
# 폴더 안의 모든 엑셀 파일 찾기
for filename in os.listdir(folder_path):
if filename.endswith('.xlsx'):
file_path = os.path.join(folder_path, filename)
# 엑셀 파일 읽기
df = pd.read_excel(file_path)
# 데이터 합치기
all_data = pd.concat([all_data, df], ignore_index=True)
# 합친 데이터를 새 엑셀 파일로 저장
all_data.to_excel("통합파일.xlsx", index=False)
이 코드는 지정한 폴더에서 .xlsx 확장자를 가진 모든 파일을 찾아서 하나로 합쳐요. ignore_index=True 옵션을 사용하면 인덱스가 새로 정리되어서 깔끔하게 만들어져요.
glob를 사용한 더 편리한 방법
glob 모듈을 사용하면 파일을 찾는 과정이 더 간단해져요. 특정 패턴의 파일만 골라서 합칠 수도 있어요.
import pandas as pd
from glob import glob
# 특정 패턴의 파일만 찾기
excel_files = glob('./data/*.xlsx')
# 빈 데이터프레임 만들기
total_data = pd.DataFrame()
# 각 파일을 읽어서 합치기
for file in excel_files:
temp = pd.read_excel(file)
total_data = pd.concat([total_data, temp], ignore_index=True)
# 결과 저장
total_data.to_excel("전체통합.xlsx", index=False)
glob 패턴을 사용하면 파일 이름에 특정 문자가 들어간 것만 선택할 수도 있어요. 예를 들어 glob('./data/*2025*.xlsx')라고 하면 파일명에 2025가 들어간 것만 골라내요.
여러 시트가 있는 엑셀 파일 다루기
엑셀 파일에 여러 시트가 있을 때는 조금 다른 방법을 써야 해요.
import pandas as pd
from glob import glob
excel_files = glob('./data/*.xlsx')
all_sheets = []
for file in excel_files:
# 모든 시트를 딕셔너리로 읽기
sheets_dict = pd.read_excel(file, sheet_name=None)
# 각 시트를 리스트에 추가
for sheet_name, sheet_data in sheets_dict.items():
# 시트 이름과 파일명 정보 추가
sheet_data['시트명'] = sheet_name
sheet_data['파일명'] = file.split('/')[-1]
all_sheets.append(sheet_data)
# 모든 시트 데이터 합치기
combined = pd.concat(all_sheets, ignore_index=True)
combined.to_excel("모든시트통합.xlsx", index=False)
이 코드는 각 파일의 모든 시트를 읽어서 하나로 합쳐요. 어느 파일의 어느 시트에서 온 데이터인지 알 수 있도록 정보도 함께 저장해요.
특정 열만 선택해서 합치기
모든 데이터가 필요한 게 아니라 특정 열만 필요할 때가 있어요. 그럴 때는 이렇게 해요.
import pandas as pd
from glob import glob
excel_files = glob('./data/*.xlsx')
all_data = pd.DataFrame()
# 필요한 열만 지정
columns_needed = ['날짜', '상품명', '판매량', '매출']
for file in excel_files:
df = pd.read_excel(file)
# 필요한 열만 선택
if all(col in df.columns for col in columns_needed):
df_selected = df[columns_needed]
all_data = pd.concat([all_data, df_selected], ignore_index=True)
all_data.to_excel("선택열통합.xlsx", index=False)
헤더가 다른 위치에 있을 때
가끔 엑셀 파일의 헤더가 첫 번째 줄이 아닌 경우가 있어요. 그럴 때는 header 옵션을 사용해요.
import pandas as pd
from glob import glob
excel_files = glob('./data/*.xlsx')
all_data = pd.DataFrame()
for file in excel_files:
# 헤더가 3번째 줄에 있는 경우 (0부터 시작하므로 2)
df = pd.read_excel(file, header=2)
all_data = pd.concat([all_data, df], ignore_index=True)
all_data.to_excel("헤더조정통합.xlsx", index=False)
대용량 파일 처리하기
파일이 매우 크거나 개수가 많을 때는 메모리 문제가 생길 수 있어요. 이럴 때는 파일을 조금씩 읽어서 처리해요.
import pandas as pd
from glob import glob
excel_files = glob('./data/*.xlsx')
# 첫 번째 파일로 초기화
first_file = pd.read_excel(excel_files[0])
first_file.to_excel("대용량통합.xlsx", index=False)
# 나머지 파일들을 하나씩 추가
for file in excel_files[1:]:
df = pd.read_excel(file)
# 기존 파일 읽기
existing = pd.read_excel("대용량통합.xlsx")
# 합치기
combined = pd.concat([existing, df], ignore_index=True)
# 다시 저장
combined.to_excel("대용량통합.xlsx", index=False)
print(f"{file} 처리 완료")
날짜별로 정렬해서 합치기
시계열 데이터를 다룰 때는 날짜 순서대로 정렬하는 게 중요해요.
import pandas as pd
from glob import glob
excel_files = glob('./data/*.xlsx')
all_data = pd.DataFrame()
for file in excel_files:
df = pd.read_excel(file)
all_data = pd.concat([all_data, df], ignore_index=True)
# 날짜 열을 datetime 형식으로 변환
all_data['날짜'] = pd.to_datetime(all_data['날짜'])
# 날짜순으로 정렬
all_data = all_data.sort_values('날짜')
# 저장
all_data.to_excel("날짜정렬통합.xlsx", index=False)
중복 데이터 제거하기
여러 파일에 같은 데이터가 있을 수 있어요. 중복을 제거하려면 이렇게 해요.
import pandas as pd
from glob import glob
excel_files = glob('./data/*.xlsx')
all_data = pd.DataFrame()
for file in excel_files:
df = pd.read_excel(file)
all_data = pd.concat([all_data, df], ignore_index=True)
# 중복 제거 (모든 열 기준)
all_data = all_data.drop_duplicates()
# 특정 열 기준으로 중복 제거
# all_data = all_data.drop_duplicates(subset=['주문번호'])
all_data.to_excel("중복제거통합.xlsx", index=False)
파일명에서 정보 추출하기
파일명에 날짜나 지점명 같은 정보가 들어있을 때, 이를 데이터에 추가할 수 있어요.
import pandas as pd
from glob import glob
import re
excel_files = glob('./data/*.xlsx')
all_data = pd.DataFrame()
for file in excel_files:
df = pd.read_excel(file)
# 파일명에서 날짜 추출 (예: 매출_2024_01.xlsx)
filename = file.split('/')[-1]
match = re.search(r'(\d{4})_(\d{2})', filename)
if match:
year = match.group(1)
month = match.group(2)
df['년도'] = year
df['월'] = month
all_data = pd.concat([all_data, df], ignore_index=True)
all_data.to_excel("파일정보추가통합.xlsx", index=False)
오류 처리 추가하기
실제로 사용할 때는 오류가 날 수 있는 상황을 대비해야 해요.
import pandas as pd
from glob import glob
excel_files = glob('./data/*.xlsx')
all_data = pd.DataFrame()
error_files = []
for file in excel_files:
try:
df = pd.read_excel(file)
all_data = pd.concat([all_data, df], ignore_index=True)
print(f"{file} 처리 성공")
except Exception as e:
print(f"{file} 처리 실패: {e}")
error_files.append(file)
# 결과 저장
if not all_data.empty:
all_data.to_excel("통합완료.xlsx", index=False)
print(f"\n총 {len(excel_files)}개 중 {len(excel_files) - len(error_files)}개 파일 통합 완료")
else:
print("통합할 데이터가 없습니다")
# 오류 파일 목록 출력
if error_files:
print("\n오류 발생 파일:")
for err_file in error_files:
print(f" - {err_file}")
CSV 파일도 함께 처리하기
엑셀 파일과 CSV 파일이 섞여 있을 때도 처리할 수 있어요.
import pandas as pd
from glob import glob
# 엑셀과 CSV 파일 모두 찾기
excel_files = glob('./data/*.xlsx')
csv_files = glob('./data/*.csv')
all_data = pd.DataFrame()
# 엑셀 파일 처리
for file in excel_files:
df = pd.read_excel(file)
all_data = pd.concat([all_data, df], ignore_index=True)
# CSV 파일 처리
for file in csv_files:
df = pd.read_csv(file, encoding='utf-8-sig')
all_data = pd.concat([all_data, df], ignore_index=True)
all_data.to_excel("엑셀CSV통합.xlsx", index=False)
이런 방식으로 파이썬을 활용하면 수작업으로 몇 시간 걸릴 일을 몇 초 만에 끝낼 수 있어요. 처음에는 코드가 복잡해 보일 수 있지만, 한 번 만들어두면 계속 재사용할 수 있어서 매우 효율적이에요.