CSV 파일이 Excel에서 잘못 열리는 이유

Excel은 .csv 파일이 어떻게 구분되어 있는지 알아내려고 파일 안을 들여다보지 않습니다. 파일을 두 번 클릭하면 운영체제 지역 설정의 목록 구분 기호를 사용하며, 그 값이 파일과 맞지 않으면 모든 행이 한 열에 몰려 들어갑니다.

영국과 미국의 목록 구분 기호는 쉼표입니다. 소수점을 쉼표로 쓰는 유럽 대륙 대부분과 라틴아메리카 상당 지역에서는 목록 구분 기호가 세미콜론입니다. 그래서 런던에서 보낸 정상적인 쉼표 구분 파일이 암스테르담이나 베를린이나 마드리드에서는 한 열에 뭉개진 채로 열리고, 세미콜론 파일은 반대 방향에서 똑같은 일을 겪습니다. 파일이 손상된 것은 없습니다. 잘못된 전제로 읽히고 있을 뿐입니다.

제대로 여는 세 가지 방법

1. 열지 말고 가져오십시오. Excel에서 데이터 탭으로 가서 데이터 가져오기 또는 텍스트/CSV에서 가져오기를 고르고, 대화 상자에서 구분 기호와 인코딩을 지정합니다. 완전한 통제권을 주는 유일한 경로이며, 파일을 다른 나라로 보내도 살아남는 유일한 방법입니다.
2. 파일 확장자를 .txt로 바꾸십시오. Excel은 .txt 파일에 대해서는 추측하기를 거부하고 가져오기 마법사를 띄워 구분자가 무엇인지 물어봅니다.
3. 보내기 전에 받는 쪽이 기대하는 구분자로 파일을 변환하십시오. 수신자가 세미콜론 로캘을 쓴다는 사실을 알고 있다면 세미콜론 파일을 보내십시오.

sep= 줄

Excel과 LibreOffice는 모두 로캘 설정을 덮어쓰는 특별한 첫 줄을 이해합니다.

sep=;
name;email;country
Ada;ada@example.com;UK

이 방법은 잘 동작하며 "고객이 내 내보내기 파일을 열지 못한다"는 문제의 흔한 해결책입니다. 다만 이것은 스프레드시트의 관례일 뿐 어떤 CSV 명세의 일부도 아니라는 점을 알아두십시오. 같은 파일을 스크립트나 데이터베이스 가져오기에 넣으면 sep=;는 평범한 첫 행으로 읽히고, 대개 엉뚱한 열 머리글이 되어버립니다. 스프레드시트를 쓰는 사람에게 보낼 파일에는 쓰되, 기계 사이의 데이터 교환에는 쓰지 마십시오.

따옴표와 이스케이프

CSV의 비공식 참조 문서는 RFC 4180이며, 이 문서는 규칙을 지시하기보다 일반적인 관행을 기술합니다. 규칙은 짧습니다.

• 필드에 구분자, 큰따옴표, 줄바꿈이 들어 있으면 반드시 큰따옴표로 감싸야 합니다.
• 따옴표로 감싼 필드 안의 큰따옴표는 두 번 겹쳐 씁니다.

id,name,note
1,"Smith, Ada","She said ""hello"" twice"
2,"Multi
line note",fine

첫 번째 데이터 행의 필드는 네 개가 아니라 세 개입니다. 쉼표가 따옴표 안에 있기 때문입니다. 겹친 따옴표는 파싱될 때 하나로 줄어듭니다. 두 번째 행에는 따옴표로 감싼 필드 안에 실제 줄바꿈이 들어 있는데, 이는 적법하며 CSV를 줄 수로 세어 나누는 방법이 믿을 수 없는 이유이기도 합니다.

역슬래시 이스케이프(\, 또는 \")는 일부 데이터베이스 도구가 쓰는 별개의 관례입니다. RFC 4180이 아니므로 엄격한 파서는 이를 이해하지 못합니다. 파일에 역슬래시를 쓴다면 그 파일을 읽는 쪽에 명시적으로 알려야 합니다.

실제 현장에서 가장 흔한 고장은 문자열을 이어 붙여 만든 파일에서, 쉼표나 짝 없는 따옴표가 들어간 값을 아예 따옴표로 감싸지 않은 경우입니다. 그러면 머리글보다 필드가 많은 행이 생기고, 이는 바로 검증 도구가 짚어내는 종류의 문제입니다.

BOM, 그리고 악센트 문자가 깨지는 이유

BOM(바이트 순서 표식)은 파일 맨 앞에 오는 보이지 않는 3바이트로, 그 파일이 UTF-8임을 표시합니다. Windows의 Excel은 오래전부터 이 표식에 의존해 왔습니다. BOM이 없으면 예전 지역 인코딩으로 되돌아갈 수 있고, 그러면 Müller가 Müller로, café가 café로 나타납니다.

대신 다른 파서들이 항상 이 표식을 걷어내지는 않는다는 대가가 따릅니다. 스크립트가 첫 번째 열 이름을 id가 아니라 \ufeffid로 보고하거나, "id" 조회가 유독 첫 번째 열에서만 이유 없이 실패한다면 범인은 BOM입니다. 파일을 utf-8-sig 인코딩으로 읽으면 사라집니다.

경험 법칙은 이렇습니다. Excel에서 열 파일에는 BOM을 넣고, 소프트웨어가 파싱할 파일에는 넣지 마십시오.

줄 끝 문자

Windows 도구는 각 행의 끝에 \r\n을 쓰고 Unix 도구는 \n을 씁니다. 대부분의 파서는 둘 다 처리하지만, \n으로 단순하게 나누면 모든 행의 마지막 필드에 캐리지 리턴이 붙은 채로 남습니다. UK처럼 보이는 값이 비교에서 "UK"와 같지 않다고 나오는 이유가 이것입니다.

파일이 실제로 어떤 구분자를 쓰는지 알아내는 방법

파일을 스프레드시트가 아니라 반드시 일반 텍스트 편집기로 여십시오. 스프레드시트는 이미 나름의 추측을 끝낸 상태이기 때문입니다. 그다음 이렇게 합니다.

1. 처음 두세 줄을 봅니다. 누가 봐도 필드 값인 것들 사이에 어떤 후보 문자(쉼표, 세미콜론, 탭, 파이프)가 놓여 있습니까?
2. 여러 줄에서 각 후보의 개수를 셉니다. 진짜 구분자는 모든 줄에서 개수가 같고, 그 개수는 열 수보다 하나 적습니다. 어떤 줄에서는 일곱 번, 다음 줄에서는 두 번 나오는 문자는 구조가 아니라 데이터입니다.
3. 개수는 따옴표로 감싼 구간 바깥에서만 세십시오. "Smith, Ada"에서 쉼표는 내용이며, 이를 단순하게 세는 바람에 진짜 쉼표 구분 파일에서 쉼표 개수가 들쭉날쭉해 보이는 것입니다.
4. 소수점 쉼표에 주의하십시오. 유럽에서 만든 파일에는 세미콜론 구분자와 숫자 안의 쉼표가 함께 있는 경우가 많습니다. Ada;1.234,56;UK가 그런 예입니다. 쉼표가 오직 숫자 사이에만 나타난다면 그것은 구분자가 아니라 소수점입니다.
5. 결론을 내리기 전에 첫 바이트에 sep= 줄이나 BOM이 있는지 확인하십시오.

탭은 따로 언급할 만합니다. 탭 구분 파일은 대체로 스프레드시트에 건네기에 가장 안전합니다. 실제 값 안에 탭이 들어가는 일이 거의 없고, Excel도 가져오기 경로에서 탭을 잘 처리하기 때문입니다. 함정은 탭이 눈에 보이지 않는다는 점입니다. 누군가 공백으로 열을 맞춰놓은 파일도 겉보기에는 똑같지만 파싱하면 쓸 만한 결과가 하나도 나오지 않습니다.

구분자를 알아내고 나면 파일 변환은 기계적인 작업입니다. 올바른 구분자와 따옴표 규칙으로 읽은 다음 목적지가 기대하는 구분자로 다시 쓰거나, JSON으로 변환해서 구분자 문제 자체를 건너뛰면 됩니다.