엑셀 시트 CSV 변환 — 여러 시트를 파이썬으로 한 번에, 앞자리 0이 사라진 이유 (실측)

엑셀 시트 CSV 변환 — 여러 시트를 파이썬으로 한 번에, 앞자리 0이 사라진 이유 (실측)

작성자:

엑셀 파일 하나에 월별 시트가 여러 개 있고, 이걸 다른 프로그램에 넣으려면 시트마다 CSV로 저장해야 하는 경우가 있습니다. 엑셀에서 시트를 하나씩 열어 “다른 이름으로 저장”을 반복하는 대신, 엑셀 시트 CSV 변환을 파이썬으로 한 번에 처리하는 스크립트를 만들었습니다. 먼저 흔히 보이는 pandas 다섯 줄짜리 코드로 시트 6개, 10만 행짜리 시험 파일을 변환해 봤습니다. 오류 없이 4초 만에 끝났지만, 결과를 열어 보니 거래처코드 00332가 332로 바뀌어 있었고, 합계 수식 칸은 비어 있었고, 헤더에는 Unnamed: 6이 끼어 있었습니다. 이 글에서는 그 원인을 하나씩 확인하고 고친 스크립트를 소개합니다. 환경은 윈도우 11, Python 3.12.10, openpyxl 3.1.5, pandas 3.0.6입니다.

시험용 엑셀 파일

실무 파일에서 자주 걸리는 값을 일부러 넣은 파일을 openpyxl로 만들었습니다. 시트 구성은 아래와 같습니다.

시트내용넣어 둔 함정
1월·2월·3월 매출각 30행: 날짜, 거래처코드, 금액, 부가세, 비고코드 ‘00332’(문자열), 비고에 쉼표·줄바꿈, H2에 =SUM 수식
요약월별 합계 표A1:C1 병합 제목, 다른 시트를 참조하는 수식
메모비어 있음빈 시트
로그10만 행: 시각, 사용자, 동작, 값속도 측정용

파일 크기는 약 2.7MB였습니다. 생성 스크립트(make_sample.py)는 실행에 5.55초가 걸렸습니다.

흔한 pandas 코드로 변환하면 생기는 일

검색하면 가장 먼저 나오는 방식입니다. sheet_name=None을 주면 모든 시트를 {시트이름: DataFrame} 딕셔너리로 읽어 옵니다.

import pandas as pd

sheets = pd.read_excel("sales.xlsx", sheet_name=None)   # {시트이름: DataFrame}
for name, df in sheets.items():
    df.to_csv(f"out_naive/{name}.csv", index=False)

3.93초 만에 6개 파일이 생겼습니다. 앞부분과 BOM 유무를 출력해 보는 작은 스크립트(peek.py)로 결과를 확인했습니다.

--- 1월 매출.csv (1,782 bytes, BOM=없음)
    날짜,거래처코드,거래처,금액,부가세,비고,Unnamed: 6,합계
    2026-01-01,332,거래처332,486000,48600,정기 납품,,
    2026-01-02,405,거래처405,334000,33400,,,
    2026-01-03,75,거래처075,421000,42100,,,
--- 메모.csv (2 bytes, BOM=없음)

--- 요약.csv (94 bytes, BOM=없음)
    1분기 매출 요약,Unnamed: 1,Unnamed: 2
    월,매출합계,건수
    1월,,

예외는 하나도 나지 않았는데 문제가 다섯 가지 보였습니다.

  1. 앞자리 0이 사라졌습니다. 00332가 332, 00075가 75가 됐습니다.
  2. 헤더에 Unnamed: 6이 생겼습니다. 비어 있는 G열과, 병합된 제목 줄을 헤더로 읽은 요약 시트에서 생긴 이름입니다.
  3. 수식 칸이 비었습니다. 합계 칸과 요약 시트의 매출합계·건수가 모두 빈칸입니다.
  4. 빈 시트도 파일이 됐습니다. 메모 시트가 2바이트짜리 CSV로 저장됐습니다.
  5. BOM 없는 UTF-8입니다. 이 CSV를 다시 엑셀로 열어 볼 계획이라면 BOM이 있는 UTF-8(utf-8-sig)로 저장하는 것이 일반적인 권장 방식입니다.

셀에 실제로 무엇이 들어 있었나

pandas가 무엇을 바꾼 건지 확인하려고 openpyxl로 셀 값을 직접 꺼냈습니다.

$ python inspect_cells.py
[data_only=False]
  B2: '00332'                                  str
  H2: '=SUM(D2:D31)'                           str
[data_only=True]
  B2: '00332'                                  str
  H2: None                                     NoneType

거래처코드는 파일 안에 분명히 문자열 '00332'로 들어 있었습니다. 앞자리 0은 pandas가 읽으면서 “숫자처럼 생긴 열”을 정수로 추론해 지운 것입니다.

수식 칸은 원인이 달랐습니다. data_only=True는 수식 대신 “마지막으로 저장된 계산값”을 읽는 옵션인데, 이 파일은 openpyxl로 만들어서 계산값이 아예 저장되어 있지 않았습니다. openpyxl은 수식을 계산하지 않습니다. openpyxl 문서 설명에 따르면 엑셀에서 열어 저장한 파일에는 계산값이 함께 저장되므로 보통은 값이 나옵니다. 다만 이번에는 엑셀 프로그램으로 직접 확인하지는 않았습니다. 다른 프로그램이 만들어 낸 xlsx를 변환할 때 이 칸이 비어 나올 수 있다는 점만 기억해 두면 됩니다.

고친 스크립트: sheets2csv.py

pandas 대신 openpyxl로 셀 값을 그대로 읽고 표준 csv 모듈로 씁니다. 형 추론을 하지 않으니 문자열은 문자열 그대로 나옵니다. 그 밖에 이렇게 처리했습니다.

  • read_only=True로 열어 큰 시트도 행 단위로 읽습니다.
  • 기본 인코딩은 utf-8-sig(BOM 포함), 파일은 newline=""로 열어 빈 줄이 끼지 않게 합니다.
  • 날짜는 시간이 00:00:00이면 2026-01-01처럼 날짜만 씁니다. 날짜만 넣은 셀도 openpyxl은 datetime(2026, 1, 1, 0, 0)으로 돌려주기 때문입니다.
  • 끝쪽의 빈 행은 지우고, 내용이 없는 시트는 건너뜁니다.
  • 시트 이름에 파일 이름으로 못 쓰는 문자가 있으면 _로 바꾸고, 바꾼 이름이 겹치면 _2를 붙입니다.
  • --check를 주면 계산값이 없는 수식 셀 개수를 시트별로 경고합니다.
"""엑셀 파일의 모든 시트를 시트별 CSV로 저장한다.

사용법: python sheets2csv.py 파일.xlsx [-o 출력폴더] [--encoding utf-8] [--check]
"""
import argparse
import csv
import re
import sys
import time
from datetime import date, datetime, time as dtime
from pathlib import Path

from openpyxl import load_workbook

sys.stdout.reconfigure(encoding="utf-8")


def safe_name(name):
    """시트 이름을 파일 이름으로 쓸 수 있게 바꾼다(윈도우 금지 문자 제거)."""
    return re.sub(r'[\\/:*?"<>|]', "_", name).strip() or "sheet"


def to_text(v):
    """셀 값을 CSV에 넣을 문자열로. 날짜는 시간이 0이면 날짜만."""
    if v is None:
        return ""
    if isinstance(v, datetime):
        return v.strftime("%Y-%m-%d") if v.time() == dtime(0) else v.strftime("%Y-%m-%d %H:%M:%S")
    if isinstance(v, (date, dtime)):
        return v.isoformat()
    if isinstance(v, float) and v.is_integer():
        return str(int(v))
    return str(v)


def count_missing_formulas(path, sheet):
    """수식은 있는데 계산된 값이 저장돼 있지 않은 셀 수(엑셀에서 한 번도 저장 안 한 파일)."""
    wb_f = load_workbook(path, read_only=True, data_only=False)
    wb_v = load_workbook(path, read_only=True, data_only=True)
    missing = 0
    for rf, rv in zip(wb_f[sheet].iter_rows(values_only=True), wb_v[sheet].iter_rows(values_only=True)):
        for f, v in zip(rf, rv):
            if isinstance(f, str) and f.startswith("=") and v is None:
                missing += 1
    wb_f.close()
    wb_v.close()
    return missing


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("xlsx")
    ap.add_argument("-o", "--out", default=None, help="출력 폴더(기본: 파일이름_csv)")
    ap.add_argument("--encoding", default="utf-8-sig", help="엑셀에서 열 거면 utf-8-sig(기본)")
    ap.add_argument("--check", action="store_true", help="계산값 없는 수식 셀 검사")
    args = ap.parse_args()

    src = Path(args.xlsx)
    out = Path(args.out) if args.out else src.with_name(src.stem + "_csv")
    out.mkdir(parents=True, exist_ok=True)

    t0 = time.perf_counter()
    wb = load_workbook(src, read_only=True, data_only=True)   # 수식 대신 계산된 값
    used = set()
    for ws in wb.worksheets:
        t = time.perf_counter()
        rows = [[to_text(v) for v in row] for row in ws.iter_rows(values_only=True)]
        # 끝쪽의 완전히 빈 행 제거
        while rows and not any(rows[-1]):
            rows.pop()
        if not rows:
            print(f"  {ws.title:8} 건너뜀(빈 시트)")
            continue
        name = safe_name(ws.title)
        while name.lower() in used:                       # 이름이 겹치면 _2, _3 ...
            name += "_2"
        used.add(name.lower())
        dest = out / f"{name}.csv"
        with open(dest, "w", newline="", encoding=args.encoding) as f:
            csv.writer(f).writerows(rows)
        msg = f"  {ws.title:8} {len(rows):>7,}행 -> {dest.name} ({time.perf_counter() - t:.2f}초)"
        if args.check:
            n = count_missing_formulas(src, ws.title)
            if n:
                msg += f"  경고: 계산값 없는 수식 {n}개"
        print(msg)
    wb.close()
    print(f"완료: {out} ({time.perf_counter() - t0:.2f}초)")


if __name__ == "__main__":
    main()

같은 파일에 실행한 결과입니다.

$ python sheets2csv.py sales.xlsx
  1월 매출         31행 -> 1월 매출.csv (0.00초)
  2월 매출         31행 -> 2월 매출.csv (0.00초)
  3월 매출         31행 -> 3월 매출.csv (0.00초)
  요약             5행 -> 요약.csv (0.00초)
  메모       건너뜀(빈 시트)
  로그       100,001행 -> 로그.csv (3.65초)
완료: sales_csv (3.66초)
--- 1월 매출.csv (1,842 bytes, BOM=있음)
    날짜,거래처코드,거래처,금액,부가세,비고,,합계
    2026-01-01,00332,거래처332,486000,48600,정기 납품,,
    2026-01-02,00405,거래처405,334000,33400,,,
--- 2월 매출.csv (1,941 bytes, BOM=있음)
    날짜,거래처코드,거래처,금액,부가세,비고,,합계
    2026-02-01,00956,거래처956,251000,25100,"급행
    오후 도착",,

거래처코드의 앞자리 0이 그대로 남았고, Unnamed 헤더도 없어졌고, 빈 시트는 건너뛰었습니다. 줄바꿈이 들어간 비고는 따옴표로 감싸져 한 칸에 들어갔습니다. 걸린 시간은 3.66초로 pandas 방식(3.93초)과 거의 같았고, 그중 3.65초가 10만 행짜리 로그 시트였습니다. 시트 수보다는 전체 행 수가 시간을 좌우한다고 보면 됩니다.

수식 칸이 비는지 미리 경고 받기

수식 칸이 빈 채로 CSV가 만들어져도 스크립트는 정상 종료합니다. 그래서 --check 옵션을 붙였습니다. 파일을 수식용과 값용으로 한 번씩 더 열어서 “수식은 있는데 값이 없는” 셀을 셉니다.

$ python sheets2csv.py sales.xlsx -o out_check --check
  1월 매출         31행 -> 1월 매출.csv (0.00초)  경고: 계산값 없는 수식 1개
  ...
  요약             5행 -> 요약.csv (0.00초)  경고: 계산값 없는 수식 6개
  메모       건너뜀(빈 시트)
  로그       100,001행 -> 로그.csv (3.67초)
완료: out_check (10.62초)

시험 파일의 수식 9개(매출 시트마다 1개, 요약 시트 6개)를 모두 찾아냈습니다. 대신 파일을 두 번 더 읽어야 해서 3.66초가 10.62초로 약 3배 느려졌습니다. 그래서 기본값으로 켜 두지 않고, 처음 보는 파일을 변환할 때만 붙이는 옵션으로 두었습니다.

pandas를 계속 쓰고 싶다면

이미 pandas로 후처리를 하고 있다면 dtype 옵션으로 앞자리 0 문제를 막을 수 있습니다. 세 가지 방식을 비교했습니다.

$ python pandas_fix.py
기본                    거래처코드 첫 값: np.int64(332)  날짜: Timestamp('2026-01-01 00:00:00')
dtype=str               거래처코드 첫 값: '00332'        날짜: '2026-01-01 00:00:00'
dtype={'거래처코드': str}  거래처코드 첫 값: '00332'        날짜: Timestamp('2026-01-01 00:00:00')

dtype=str로 전부 문자열로 읽으면 0은 지켜지지만, 날짜가 '2026-01-01 00:00:00'이라는 문자열이 됩니다. 코드처럼 지켜야 할 열만 이름으로 지정하는 쪽이 가장 깔끔했습니다. 읽는 시간은 3.70초로 기본과 차이가 거의 없었습니다.

sheets = pd.read_excel("sales.xlsx", sheet_name=None, dtype={"거래처코드": str})

다만 열 이름을 미리 알아야 하고, 시트마다 열 구성이 다르면 지정이 번거로워집니다. 구성을 모르는 파일을 통째로 변환하는 용도라면 위의 openpyxl 스크립트가 손이 덜 갑니다.

시트 이름과 행 수에서 걸리는 함정

시트 이름을 그대로 파일 이름으로 쓰면 문제가 되는 경우를 따로 시험했습니다.

시트 이름 거부: '1/2분기' -> Invalid character / found in sheet title
시트 이름 거부: '보고서?' -> Invalid character ? found in sheet title
  견적<최종>         2행 -> 견적_최종_.csv
  견적_최종_         2행 -> 견적_최종__2.csv
  "따옴표"          2행 -> _따옴표_.csv
  A|B            2행 -> A_B.csv

/와 ?는 openpyxl이 시트 이름으로 아예 받아 주지 않았습니다. 반면 < > " |는 시트 이름으로는 만들어지지만 윈도우 파일 이름에는 쓸 수 없어서 _로 바꿨습니다. 그런데 이렇게 바꾸고 나니 견적<최종>이 실제로 있는 다른 시트 견적_최종_과 이름이 같아졌습니다. 겹침 처리가 없으면 한 파일이 다른 파일을 조용히 덮어씁니다. 스크립트가 _2를 붙여 둘 다 살렸고, 다시 읽었을 때 코드 007, 010도 그대로였습니다.

변환 결과를 검증할 때 줄 수를 세는 것도 주의해야 합니다.

sales_csv\1월 매출.csv 줄 수 37 / csv 행 수 31
sales_csv\2월 매출.csv 줄 수 42 / csv 행 수 31
sales_csv\로그.csv 줄 수 100001 / csv 행 수 100001

1월 시트는 31행인데 텍스트 줄 수는 37줄이었습니다. 비고 칸에 들어 있는 줄바꿈 때문입니다. 원본과 행 수를 비교하려면 줄 수가 아니라 csv.reader로 읽은 행 수를 세야 합니다.

마무리

pandas 다섯 줄은 오류 없이 끝났지만 앞자리 0, 수식 칸, 헤더가 조용히 바뀌었습니다. 변환이 끝났다는 것과 내용이 같다는 것은 다른 이야기입니다.

가지고 있는 엑셀 파일 하나에 python sheets2csv.py 파일.xlsx --check를 먼저 돌려 보세요. 경고가 없고 코드 열의 0이 살아 있다면 그대로 쓰면 되고, 경고가 나오면 그 칸의 값이 CSV에 빠졌다는 뜻이니, 원본을 만든 프로그램이나 엑셀에서 계산값이 저장되도록 다시 저장한 뒤 변환하는 편이 안전합니다.

다음 글에서는 이렇게 만든 스크립트가 고친 뒤에도 계속 제대로 동작하는지 확인하는 방법으로, pytest로 테스트를 붙이는 기초를 다룹니다.

댓글 남기기