'깨진' CSV(CSV 파일)의 대부분은 사실 깨진 것이 아닙니다. CSV는 텍스트 파일이고, 텍스트 파일은 좀처럼 망가지지 않습니다. 거의 항상 파일은 한 가지 전제로 만들어졌는데 다른 전제로 읽히고 있을 뿐입니다. 구분자가 다르거나, 인코딩이 다르거나, 내보내기 도구가 따옴표 규칙을 잘못 적용한 경우입니다.
그래서 수리 전에 진단부터 해야 합니다. 증상을 보면 여섯 가지 원인 중 무엇인지 알 수 있고, 각각 정해진 해결법이 있습니다.
빠른 답
스프레드시트로 열어 보기 전에 먼저 원본 바이트를 확인하세요. 스프레드시트는 증거를 숨겨 버리는 도구입니다.
head -5 broken.csv # the actual separators and quotes
file -I broken.csv # the real encoding
wc -l broken.csv # how many lines it thinks there are
그런 다음 증상에 맞는 원인을 찾습니다.
| 증상 | 원인 | 해결 |
|---|---|---|
| 모든 내용이 한 열에 들어감 | 구분자가 다름 | 구분자를 지정해 다시 열기 |
é, ’, ü | UTF-8을 Latin-1로 읽음 | UTF-8로 다시 열기 |
첫 번째 헤더에 가 붙음 | 바이트 순서 표시(BOM) | BOM 제거 |
| 레코드 중간에서 행이 갈라짐 | 필드 안의 줄바꿈 | 따옴표 처리 수정 |
| N행 이후 열 개수가 어긋남 | 이스케이프되지 않은 따옴표 | N행을 찾아 수정 |
| 행 중간에서 파일이 끝남 | 쓰기가 중간에 끊김 | 다시 내보내기 |
1. 모든 내용이 한 열에 들어가는 경우
단연 가장 흔한 경우이며, 파일의 잘못이 아닙니다.
유럽 대륙의 많은 지역에서는 쉼표를 소수점으로 쓰기 때문에 세미콜론을 구분자로 씁니다. 또 확장자는 .csv인데 실제로는 탭으로 구분된 내보내기 파일도 많습니다. head -5를 실행하면 어느 쪽인지 바로 알 수 있습니다.
해결법은 찾아 바꾸기가 아니라, 읽는 쪽에 구분자가 무엇인지 알려 주는 것입니다. 세미콜론을 쉼표로 찾아 바꾸면 따옴표로 묶인 값 안의 세미콜론까지 바뀌어서 정말로 깨진 파일이 됩니다.
- Excel: 더블클릭하지 마세요. 데이터 → 텍스트/CSV에서를 선택하고 미리 보기에서 구분 기호를 고릅니다.
- Google 스프레드시트: 파일 → 가져오기 → 구분자에서 맞춤을 선택합니다.
- 명령줄:
duckdb -c "SELECT * FROM read_csv('broken.csv', delim=';')" - 스마트폰: 한 번 추측하고 포기하는 앱이 아니라, 파싱 설정을 직접 지정해 파일을 다시 열 수 있는 앱을 사용하세요.
구분자, 헤더, 인코딩을 자동으로 감지하고, 추측이 틀렸을 때 파싱 설정을 직접 지정해 파일을 다시 열 수 있습니다. 여기서는 이 점이 중요합니다.
기능2. 문자 깨짐: é가 있어야 할 곳에 é
전형적인 인코딩 불일치입니다. 파일은 UTF-8인데 무언가가 Latin-1이나 Windows-1252로 읽었습니다. 바이트는 멀쩡하고, 해석이 틀린 것입니다.
file -I broken.csv
# broken.csv: text/plain; charset=utf-8
해결법: UTF-8을 지정해 다시 여세요. Excel의 가져오기 대화상자에는 원본 파일 드롭다운이 있습니다. 65001: Unicode (UTF-8)로 설정하세요. 명령줄에서는 다음과 같이 합니다.
iconv -f WINDOWS-1252 -t UTF-8 broken.csv > fixed.csv
변환 방향에 주의하세요. 파일이 정말로 Windows-1252(오래된 시스템, 일부 ERP 내보내기)라면 이 명령으로 변환됩니다. 하지만 이미 UTF-8인데 표시만 잘못되는 것이라면 변환할수록 더 나빠집니다. 깨진 문자를 다시 인코딩하게 되기 때문입니다. 무엇이든 실행하기 전에 file -I로 확인하세요.
3. 첫 번째 열 이름이 order_id인 경우
UTF-8 바이트 순서 표시(BOM)입니다. 파일 맨 앞에 붙는 눈에 보이지 않는 3바이트로, Excel과 여러 Windows 도구가 기록합니다. 스크립트에서 헤더 매칭을 망가뜨리고, 단순한 파서에서는 깨진 글자로 보입니다.
sed -i '' '1s/^\xEF\xBB\xBF//' broken.csv # macOS
sed -i '1s/^\xEF\xBB\xBF//' broken.csv # Linux
뒤쪽 처리 단계에서 문제가 생길 때만 제거하세요. Excel은 오히려 BOM을 필요로 합니다. 더블클릭했을 때 Excel이 CSV를 UTF-8로 인식하는 근거가 BOM이어서, 이것을 지우면 파일을 받은 동료의 화면에서 한글이나 악센트 문자가 깨집니다.
4. 행이 중간에서 갈라지는 경우
10,000행이어야 하는데 10,340행이 나왔고, 주소가 있어야 할 자리에 조각이 들어가 있습니다. 주소, 메모란, 붙여 넣은 문단 같은 필드 안에 실제 줄바꿈이 들어 있는 것입니다.
필드가 따옴표로 묶여 있다면 이것은 올바른 CSV입니다.
id,name,notes
1,"Alice","Called back.
Wants a refund."
RFC 4180에서 허용하며, 제대로 된 파서는 모두 처리합니다. 문제는 도구가 따옴표를 고려하기 전에 줄바꿈으로 먼저 나눌 때 생깁니다. 대부분의 셸 한 줄 명령, 일부 스프레드시트 가져오기 기능, 직접 짠 코드 상당수가 여기에 해당합니다.
대개 고쳐야 할 것은 파일이 아니라 읽는 쪽입니다. 따옴표를 이해하는 파서를 사용하세요. DuckDB, Python의 csv 모듈, csvkit, 또는 제대로 된 스프레드시트 가져오기가 있습니다. 필드가 정말로 따옴표로 묶여 있지 않다면 내보내기 자체가 잘못된 것이므로, 수리하기보다 다시 내보내는 편이 빠릅니다.
어느 쪽인지 확인하려면 다음을 실행하세요.
csvclean -n broken.csv # reports rows with the wrong field count
5. 4,812행 이후가 전부 틀린 경우
중간부터 열 개수가 어긋나고 끝까지 돌아오지 않습니다. 이스케이프되지 않은 따옴표 하나가 원인이며, 그 뒤의 모든 내용은 닫히지 않는 문자열 안에서 읽히고 있습니다.
id,product,price
1,"6"" pipe",4.50
이 6"는 6""로 써야 했습니다. 대신 파서는 값이 6에서 끝난다고 보고, 이어지는 " 하나가 새 값을 열어 파일의 나머지가 그 안에 들어가 버립니다.
찾는 방법:
csvclean -n broken.csv
awk -F',' 'NF != 3 {print NR": "$0}' broken.csv # rough, ignores quoting
믿을 만한 것은 csvclean입니다. awk 명령은 따옴표 안에 쉼표가 없는 파일에 쓸 수 있는 빠른 근사치입니다. 행 번호를 알았다면 그 한 행만 직접 고치세요. 안쪽 따옴표를 두 번 쓰거나, in.으로 바꾸고 넘어가면 됩니다.
스마트폰에서는 특정 행으로 이동해 셀 하나만 편집할 수 있는 앱이 없으면 이 작업이 번거롭습니다.
파일을 통째로 열어 검색할 수 있으므로, 구조가 틀어지는 행을 찾은 다음 수리할지 다시 내보낼지 판단할 수 있습니다.
기능6. 파일이 그냥 끝나 버리는 경우
마지막 줄이 반쪽짜리 레코드이거나, 파일 크기가 이상하게 딱 떨어지는 숫자입니다. 쓰기 작업이나 다운로드가 중간에 끊긴 것입니다.
tail -3 broken.csv
여기서는 고칠 것이 없습니다. 데이터가 애초에 기록되지 않았기 때문입니다. 불완전한 마지막 줄을 삭제하고 손실을 받아들이거나, 다시 내보내세요. 다운로드였다면 파일을 탓하기 전에 Content-Length와 실제 크기를 비교해 보세요.
수리 규칙
위의 문제 대부분을 별일 아닌 것으로 만드는 네 가지 습관입니다.
- 복사본으로 작업하세요.
cp broken.csv broken.backup.csv. 이 페이지의 해결법은 확인하기 전까지 모두 추측이고, 되돌릴 수 없는 추측은 손해 보는 거래입니다. - 파일보다 읽는 쪽을 먼저 고치세요. 여기 나온 증상의 대부분은 설정 오류입니다. 잘못 설정된 파서에 맞춰 데이터를 고쳐 쓰면 문제가 두 개가 됩니다.
- 구분자를 찾아 바꾸기로 바꾸지 마세요. 따옴표 안의 구분자까지 바뀌어서 읽을 수 있던 파일이 손상된 파일이 됩니다.
- 전후로 행 수를 세세요. 모든 수리에서 그렇게 하세요. 의도하지 않았는데 숫자가 바뀌었다면 수리가 무언가를 망가뜨린 것입니다.
포기하고 다시 내보내야 할 때
구조가 잘못된 행이 몇 개를 넘는다면 수리를 멈추세요. 내보내기가 깨졌다는 것은 대개 내보내는 쪽에 따옴표 처리 버그가 있다는 뜻이고, 지금은 멀쩡해 보이는 행을 포함해 그 도구가 만든 모든 행이 의심스럽습니다. 파일을 다시 요청하고, 따옴표를 붙여서 내보내 달라고 하세요.