내보낸 CSV가 엑셀에서 깨지는 이유

관리자 화면에 내보내기 기능을 붙이면 문의가 따라옵니다. 한글이 깨져 보인다, 전화번호가 이상해졌다, 주문번호가 지수 표기로 바뀌었다는 내용입니다. 파일 자체는 정상인 경우가 대부분이고, 원인은 세 가지로 나뉩니다.

1. 인용 규칙을 직접 만들지 않는다

CSV는 쉼표로 나누는 형식이지만, 값 안에 쉼표가 들어갈 수 있습니다. RFC 4180이 정한 규칙은 간단합니다.

값에 쉼표, 큰따옴표, 줄바꿈이 있으면 값 전체를 큰따옴표로 감쌉니다
값 안의 큰따옴표는 두 번 반복해서 적습니다

실제로 생성해 보면 이렇게 나옵니다.

name,memo,phone,zip
"Kim, YM","say ""hi""",010-1234-5678,03187
multi,"line1
line2",+82 10,00123

세 번째 행의 값에는 줄바꿈이 들어 있는데도 한 행으로 취급됩니다. 따옴표 안의 줄바꿈은 값의 일부입니다. 그래서 CSV를 줄 단위로 잘라 읽으면 안 됩니다. 직접 split(",")으로 파싱하는 코드는 이런 데이터에서 어긋납니다.

각 언어의 표준 CSV 모듈을 쓰면 이 처리가 다 들어 있습니다. 만드는 쪽도 마찬가지입니다.

줄바꿈은 \r\n이 표준입니다. \n만 써도 대부분 읽히지만, 일부 환경에서 마지막 열에 문제가 생깁니다.

2. 한글이 깨지는 것은 BOM 문제

파일을 UTF-8로 저장해도 엑셀은 시스템 기본 인코딩(한국어 윈도에서는 CP949)으로 읽으려 합니다. 그래서 한글이 깨집니다.

대응은 파일 앞에 BOM 세 바이트를 붙이는 것입니다.

EF BB BF

엑셀은 이 바이트를 보고 UTF-8로 읽습니다. 대부분의 언어에서 인코딩을 utf-8-sig로 지정하면 자동으로 붙습니다.

다만 BOM에는 부작용이 있습니다. 다른 프로그램이 그 파일을 읽으면 첫 번째 열 이름 앞에 보이지 않는 문자가 붙습니다. id가 id가 되어 키 조회가 실패합니다. 내보내기 파일에 BOM을 붙였다면 가져오기 쪽에서 BOM을 제거하는 처리를 함께 넣습니다.

3. 자동 변환은 파일이 아니라 엑셀이 하는 일

CSV에는 타입이 없습니다. 전부 텍스트입니다. 엑셀은 열면서 값의 모양을 보고 타입을 추측하는데, 여기서 값이 바뀝니다.

파일에 있는 값	엑셀이 보여 주는 값	이유
`03187`	`3187`	숫자로 보고 앞의 0을 제거
`1234567890123456`	`1.23457E+15`	16자리 이상은 지수 표기
`1-2`	`1월 2일`	날짜로 인식
`+82 10`	수식 오류	`+`로 시작하면 수식으로 해석

파일에는 원래 값이 그대로 있습니다. 텍스트 편집기로 열면 확인됩니다. 바뀐 것은 화면에 보이는 값이고, 그 상태로 저장하면 파일에도 반영됩니다.

값 앞에 작은따옴표나 탭을 붙여 텍스트로 강제하는 방법이 알려져 있지만, 그 문자가 다른 도구에서는 값의 일부로 읽힙니다. 확실한 대응은 형식을 바꾸는 것입니다.

엑셀에서 여는 것이 주 용도라면 xlsx로 내보냅니다. 셀마다 타입을 지정할 수 있어 변환이 일어나지 않습니다
CSV를 유지해야 한다면 안내에 "가져오기 마법사에서 해당 열을 텍스트로 지정" 절차를 함께 적습니다

구분자가 쉼표가 아닐 때

지역 설정에 따라 엑셀이 세미콜론을 구분자로 기대하는 경우가 있습니다. 소수점에 쉼표를 쓰는 지역(독일, 프랑스 등)이 여기 해당합니다. 그런 환경에서 쉼표 CSV를 열면 모든 값이 한 열에 들어갑니다.

파일 첫 줄에 구분자를 선언하면 엑셀이 이를 따릅니다.

sep=,
name,memo,phone

다만 이 줄은 표준이 아니라서 다른 파서에서는 데이터 행으로 읽힙니다. 대상이 엑셀로 한정될 때만 씁니다.

수식 인젝션

=, +, -, @로 시작하는 값은 엑셀에서 수식으로 해석됩니다. 사용자 입력이 그대로 CSV에 들어가면 다른 사람이 그 파일을 열었을 때 수식이 실행될 수 있습니다.

내보내기에 사용자 입력이 포함된다면 이 네 문자로 시작하는 값 앞에 작은따옴표를 붙이거나 값을 거부합니다. 관리자용 내보내기라고 해서 예외로 두지 않습니다.

확인 순서

파일을 텍스트 편집기로 엽니다. 값이 정상이면 파일이 아니라 엑셀의 표시 문제입니다
한글이 깨진다면 파일 첫 세 바이트가 EF BB BF인지 봅니다
값이 잘리거나 지수로 바뀐다면 자동 변환입니다. xlsx를 검토합니다
열이 밀렸다면 인용 규칙을 봅니다. 값에 쉼표나 줄바꿈이 들어 있을 수 있습니다
가져오기에서 첫 열 이름이 매칭되지 않으면 BOM을 의심합니다

tools.onuel.dev의 JSON을 CSV로 변환하면 인용 규칙이 적용된 결과를 그대로 볼 수 있습니다. 파일 앞에 BOM이 붙었는지는 헥스 덤프로 첫 바이트를 확인하면 됩니다.