이 글의 목적은 엑셀에서 CSV 형식으로 저장할 때 발생하는 대표적인 데이터 손실 문제를 체계적으로 진단하고, 현장에서 바로 적용 가능한 예방·복구 절차와 표준 작업안을 제공하는 것이다.
1. CSV에서 왜 데이터가 손실되는가
CSV는 서식이 없는 순수 텍스트 기반의 쉼표 구분값 파일이며, 각 셀의 데이터 타입이나 서식을 보존하지 않는다. 따라서 엑셀 통합문서의 표시 방식, 지역 설정, 문자 인코딩, 구분자, 따옴표 이스케이프 방식 등이 맞지 않으면 숫자·날짜·한글 등에서 손실이 발생한다. 엑셀은 CSV를 열거나 저장할 때 내부 규칙에 따라 재해석을 수행하므로, “저장 시”뿐 아니라 “다시 열 때”도 손실이 발생할 수 있다.
| 문제 유형 | 전형적 증상 | 원인 | 고위험 필드 |
|---|---|---|---|
| 선행 0 손실 | 00123 → 123 | 숫자 재해석 | 우편번호, 코드, 사번 |
| 대형 숫자 손상 | 16자리 이상이 1.23E+15 또는 반올림 | 유효 자릿수 제한 | 신용카드번호, 주문번호 |
| 날짜 오인식 | 2024-03-04 → 2024-03-04 00:00로 고정, 1/2 → 2월 1일 | 지역별 날짜 패턴 | 분수, 슬래시 포함 텍스트 |
| 한글 깨짐 | ���로 표시 | 인코딩 불일치 | CP949↔UTF-8, BOM 여부 |
| 필드 분리 오류 | 열 밀림, 줄깨짐 | 구분자·개행·따옴표 처리 미흡 | 주소, 설명, 메모 |
| 수식 손실 | =SUM(A1:A3) → 결과값만 남음 | CSV는 수식 미지원 | 계산열 전부 |
| 너비·서식 손실 | 폰트, 색, 서식 사라짐 | CSV는 서식 미지원 | 모든 서식 데이터 |
2. 저장 전에 해야 할 사전 점검 체크리스트
- 목적 확인: CSV 교환 대상 시스템의 요구사항(인코딩, 구분자, 날짜 포맷, 따옴표 규칙)을 확보한다.
- 필드 설계: 숫자형으로 오인될 위험 필드는 텍스트로 명시한다.
- 서식 고정: 계산열은 값으로 변환하고, 표시 형식 의존 항목은 명시 문자열로 변환한다.
- 인코딩 결정: 한글은 UTF-8 with BOM 또는 CP949 등 수신 시스템 규격에 맞춘다.
- 샘플 검증: 20~50행 샘플을 대상 시스템에 미리 투입해 역수용성 테스트를 수행한다.
주의 : CSV는 수신 시스템의 파서가 최종 해석을 수행하므로, “엑셀에서 보이는 대로 저장되었다”는 가정은 위험하다.
3. 대표 문제 7가지와 확실한 해결 절차
3.1 선행 0이 사라지는 문제
우편번호, 코드형 숫자는 텍스트로 강제해야 한다.
- 방법 A: CSV 저장 전, 해당 열을 별도 열로 변환하여
'(작은따옴표) 접두를 추가한다.
="'" & TEXT(A2,"0")- 방법 B: 자릿수 고정이 필요한 경우 TEXT 함수로 포맷을 명시한다.
=TEXT(A2,"000000")- 방법 C: 가져오기 측에서 스키마를 고정한다면, 엑셀로 열지 않고 수신 시스템이 CSV를 직접 읽도록 한다.
주의 : 엑셀에서 CSV를 다시 열면 선행 0이 재손실될 수 있다. 검증은 메모장 등 텍스트 에디터로 수행한다.
3.2 대형 숫자가 지수표기 또는 반올림되는 문제
16자리 이상의 숫자 ID는 엑셀의 정밀도 한계를 넘어 손상된다. 숫자형이 아닌 텍스트형으로 내보낸다.
="#" & A2필드 시작에 식별 문자(예: #)를 넣고 수신 측에서 제거하는 패턴을 사용하면 엑셀의 숫자화 시도를 회피한다.
3.3 날짜·시간 오인식 문제
날짜는 ISO 8601 텍스트 형태로 고정한다.
=TEXT(A2,"yyyy-mm-dd")시간 포함
=TEXT(A2,"yyyy-mm-ddThh:nn:ss")주의 : 지역 설정이 쉼표 대신 세미콜론을 구분자로 쓰는 경우가 있다. 이때 날짜 포맷에서 쉼표 사용을 피한다.
3.4 한글 깨짐(인코딩) 문제
한글 데이터는 저장 형식을 명확히 선택한다.
- CSV UTF-8: 다양한 시스템과의 호환성이 높다. 일부 구형 시스템은 BOM을 요구한다.
- CSV(쉼표로 분리): Windows 기본 ANSI 코드페이지(CP949 등)로 저장된다. 수신 시스템이 이 인코딩을 요구할 때만 사용한다.
수신 시스템 규격서에 맞춰 UTF-8 with BOM 또는 지정 코드페이지로 고정한다.
3.5 필드 내 쉼표·개행·따옴표로 인한 열 밀림
텍스트 안의 쉼표나 줄바꿈, 따옴표는 RFC 4180 규칙대로 모두 따옴표로 감싸고, 내부 따옴표는 두 번 반복하여 이스케이프한다.
"홍길동, 과장" → ""홍길동, 과장"" "첫째 줄 둘째 줄" → ""첫째 줄 둘째 줄""엑셀 기본 저장은 위 규칙을 준수하나, 사용자 정의 내보내기나 수동 편집 시 누락되기 쉽다.
3.6 수식·서식 의존 데이터의 소거
CSV는 수식·서식을 보존하지 않는다. 보고용 시트는 별도로 보관하고, 내보내기용 시트는 값만 남긴다.
선택 영역 → 복사 → 붙여넣기 옵션(값)3.7 행·열·문자 길이 한계 초과
- 엑셀 행 한계: 1,048,576행이다.
- 셀 문자 한계: 셀 당 32,767자이며, 표시 가능 문자는 1,024자 수준이다.
- CSV 자체는 텍스트 제한이 없으나 엑셀에서 열 때 잘리고 오해석된다.
대용량은 파일을 분할하고, 긴 텍스트는 줄바꿈 대신 공백 등 대체 전략을 수립한다.
4. 안전한 CSV 내보내기 표준 절차(현장용 SOP)
- 원본 보존: .xlsx로 원본을 저장한다.
- 내보내기 시트 생성: 필요한 열만 남기고 계산열은 값으로 변환한다.
- 텍스트화: 선행 0·대형 숫자·날짜 필드를 TEXT 함수로 문자열 고정한다.
- 정리: 필드 내 개행을 제거하거나 따옴표 처리 기준을 점검한다.
- 인코딩 결정: 파일→다른 이름으로 저장→형식에서 “CSV UTF-8(콤마로 분리)” 또는 요구 인코딩을 선택한다.
- 검증: 저장한 CSV를 메모장으로 열어 표본 행을 점검한다.
- 역검증: 대상 시스템에 샘플 업로드 후 필드 무결성을 확인한다.
주의 : 엑셀로 CSV를 다시 열어 점검하지 않는다. 재해석에 의해 문제를 숨기거나 추가 손실이 발생한다.
5. 대안 포맷과 워크플로 전략
- TSV(탭 구분)를 사용하면 쉼표 충돌을 줄일 수 있다. 많은 시스템이 TSV를 지원한다.
- JSON은 데이터 타입 보존과 중첩 구조에 유리하다.
- Excel(xlsx) 그대로 교환하여 형식을 유지한다.
대상 시스템이 CSV만 받는다면, CSV 스펙과 수신 파서 동작을 문서화하고 테스트 케이스를 관리한다.
6. 파워쿼리·가져오기 마법사로 손실 없이 읽기
CSV를 엑셀로 “열기”가 아니라 “데이터 가져오기”로 불러오면 각 열의 형식을 사전에 지정하여 손실을 줄일 수 있다.
- 데이터 탭→데이터 가져오기→텍스트/CSV에서→파일 선택한다.
- 파일 원본(인코딩)과 구분자, 데이터 형식 감지를 확인한다.
- 데이터 변환을 눌러 파워쿼리 편집기에서 문제 열을 “텍스트”로 강제한다.
- 닫기 및 로드로 시트에 배치한다.
주의 : 더블클릭으로 CSV를 바로 열면 자동 추론이 적용되어 선행 0 및 대형 숫자가 손상된다.
7. VBA로 안정적인 CSV 내보내기 자동화
대상 시스템 규격에 맞춘 따옴표·개행·인코딩 정책을 VBA로 고정할 수 있다. 아래 예시는 모든 필드를 따옴표로 감싸고 내부 따옴표를 이스케이프한다.
Option Explicit Sub ExportCsvQuotedUTF8() Dim ws As Worksheet, rng As Range, r As Long, c As Long Dim sb As String, line As String, v, f As Integer Set ws = ThisWorkbook.Worksheets("EXPORT") Set rng = ws.UsedRange f = FreeFile Open ThisWorkbook.Path & "\export.csv" For Output As #f ' UTF-8 BOM Put #f, , ChrW(&HFEFF) For r = 1 To rng.Rows.Count line = "" For c = 1 To rng.Columns.Count v = rng.Cells(r, c).Text v = Replace(v, """", """""") ' 내부 따옴표 이스케이프 v = """" & v & """" If c > 1 Then line = line & "," line = line & v Next c sb = sb & line & vbCrLf If Len(sb) > 80000 Then Put #f, , sb: sb = "" Next r If Len(sb) > 0 Then Put #f, , sb Close #f End Sub위 매크로는 셀 표시값을 그대로 내보내므로 3장 절차대로 TEXT로 고정 후 실행해야 한다.
8. 지역 구분자 및 시스템 설정 이슈
- 일부 로케일에서는 세미콜론이 기본 구분자이다. 외부 시스템이 쉼표를 요구하면 내보내기 매크로나 파워쿼리로 강제한다.
- 소수점 기호가 콤마인 로케일에서는 “1,23”이 열 분리를 유발할 수 있다. 텍스트화 후 따옴표 감싸기로 예방한다.
9. 테스트 케이스(현장 검증용)
| 케이스ID | 입력값 | 예상 CSV | 판정 |
|---|---|---|---|
| T01 | 00123 | "00123" | 선행 0 유지 |
| T02 | 1234567890123456 | "1234567890123456" | 지수표기 금지 |
| T03 | 2024-03-04 | "2024-03-04" | ISO 날짜 |
| T04 | 홍길동, 과장 | "홍길동, 과장" | 쉼표 보호 |
| T05 | "인용구" | """인용구""" | 따옴표 이스케이프 |
| T06 | 한글 텍스트 | UTF-8로 정상 표시 | 인코딩 일치 |
10. 현장에서 자주 하는 실수와 방지 대책
- 실수: CSV를 엑셀로 바로 열어 검증한다. 대책: 메모장 또는 전용 뷰어로 확인한다.
- 실수: 날짜 포맷을 표시 형식에만 의존한다. 대책: TEXT로 문자열 고정 후 저장한다.
- 실수: 수식이 남아 있는 상태로 저장한다. 대책: 값 붙여넣기 후 저장한다.
- 실수: 인코딩 요구사항을 확인하지 않는다. 대책: 규격서 확인 후 UTF-8 with BOM 등으로 고정한다.
- 실수: 필드 내 줄바꿈을 방치한다. 대책: 따옴표 감싸기 또는 개행 제거 규칙을 수립한다.
11. 가져오기 측에서의 방어 전략
CSV를 시스템이 읽을 때 스키마를 고정하면 손실을 줄일 수 있다. 열 타입을 Text로 강제하고, 숫자 검증은 별도 로직으로 수행한다. 날짜는 정규식으로 ISO 형식만 허용한다.
예) yyyy-mm-dd 정규식: ^\d{4}-\d{2}-\d{2}$12. 팀 운영을 위한 표준 템플릿
내보내기 시트 예시 템플릿을 두고, 위험 필드를 미리 문자열화하는 열을 포함한다.
| 원본열 | 내보내기열 | 변환식 | 비고 |
|---|---|---|---|
| 우편번호 | 우편번호_txt | =TEXT(A2,"00000") | 선행 0 고정 |
| 주문ID | 주문ID_txt | ="@"&B2 | 숫자화 회피 |
| 일자 | 일자_txt | =TEXT(C2,"yyyy-mm-dd") | ISO 날짜 |
| 메모 | 메모_sanitized | =SUBSTITUTE(SUBSTITUTE(D2,CHAR(13),""),CHAR(10)," ") | 개행 제거 |
13. 문제 발생 후 복구 가능성
이미 손실된 선행 0이나 반올림된 대형 숫자는 원래 값을 복원하기 어렵다. 시스템 로그나 원본 시스템의 키값 재조회가 유일한 경로인 경우가 많다. 따라서 사전 예방이 핵심이다.
14. 현업 질문에 대한 즉답
- 질문: “CSV UTF-8과 CSV(쉼표로 분리)는 무엇이 다른가” → 답: 전자는 UTF-8 인코딩이며, 후자는 OS 기본 코드페이지이다.
- 질문: “CSV에 서식을 살릴 수 있는가” → 답: 불가능하다. 서식은 xlsx로만 유지된다.
- 질문: “CSV 재검증은 어떻게 하는가” → 답: 텍스트 에디터로 열어 따옴표, 구분자, 인코딩을 확인한다.
FAQ
CSV에서 줄바꿈을 유지하고 싶을 때 방법은 무엇인가?
필드 전체를 따옴표로 감싸고 내부 줄바꿈을 그대로 둔다. 수신 파서가 RFC 4180을 준수해야 한다. 불가하면 개행을 공백으로 치환한다.
특정 열만 텍스트로 강제하려면 어떻게 하는가?
파워쿼리로 가져오면서 해당 열 형식을 텍스트로 지정한다. 또는 내보내기 전에 TEXT 함수로 문자열을 확정한다.
세미콜론 구분 CSV가 필요하면 어떻게 하는가?
VBA로 구분자를 ";"로 생성하거나 파워쿼리에서 변환 후 내보낸다. 시스템 요구사항을 우선한다.
대용량 CSV가 느리게 열릴 때 대처는 무엇인가?
엑셀로 직접 열지 말고 데이터 가져오기로 로드하거나, 파워쿼리로 분할 로드한다. 필요한 열만 불러온다.
CSV에 BOM이 꼭 필요한가?
일부 시스템은 BOM이 있어야 UTF-8을 정확히 인식한다. 규격에 따라 BOM 포함 여부를 결정한다.