엑셀이 CSV를 망가뜨리는 이유와 막는 법
CSV 파일은 텍스트입니다. 그런데 엑셀로 열면 더 이상 텍스트가 아닙니다. 엑셀은 필드를 하나씩 읽으면서 그 값이 무엇이어야 하는지 판단하고, 그 판단에 맞게 값을 고쳐 씁니다. 대개는 추측이 맞아서 아무도 눈치채지 못합니다. 틀렸을 때는 조용히 망가지고, 저장하는 순간 되돌릴 수 없게 되며, 대개 파일을 보낸 사람 탓이 됩니다.
가장 먼저 사라지는 것은 앞자리 0
007이라는 값은 엑셀에게 앞에 쓸데없는 글자 두 개가 붙은 숫자 7로 보입니다. 그래서 정말 7이 됩니다. 우편번호, 은행 지점 코드, 부품 번호처럼 자릿수 자체에 의미가 있는 식별자가 모두 같은 일을 겪습니다.
이건 그나마 알아채기 쉽습니다. 열이 눈에 띄게 오른쪽 정렬로 바뀌고 0이 없어지니까요. 그래서 값 앞에 작은따옴표를 붙여 손으로 고치는 경우가 많은데, 다음 내보내기 전까지만 버티는 방법입니다.
엑셀은 유효숫자 15자리까지만 저장합니다
실제로 피해가 큰 것은 이쪽입니다. 겉보기에 이상한 구석이 전혀 없기 때문입니다. 엑셀에서 숫자로 저장된 값은 유효숫자 15자리까지만 유지됩니다. 16자리 주문 참조번호, 긴 거래 ID, 카드번호 형태의 값을 붙여넣으면 마지막 자리가 0으로 바뀝니다.
셀에는 여전히 긴 숫자가 보입니다. 강조 표시도, 경고도 없습니다. 그저 값이 원래 값이 아닐 뿐이고, 그 값을 기준으로 하는 모든 조회가 이제부터 실패합니다. 원인을 찾는 데 반나절이 걸리는 종류의 문제입니다. 15자리를 넘는 값은 텍스트로 저장해야 하고, 이미 저장된 뒤에는 표시 형식을 아무리 바꿔도 복구되지 않습니다. 디스크에 기록된 값에서 이미 자릿수가 사라졌기 때문입니다.
날짜처럼 생긴 코드는 날짜가 됩니다
3-1을 입력하면 3월 1일이 돌아옵니다. SEPT1을 입력하면 9월 1일이 됩니다. 억지로 만든 예가 아니라 유전학 분야에서 대규모로 벌어진 일입니다. SEPT1, MARCH1, DEC1 같은 유전자 기호가 날짜 해석기가 찾는 형태와 정확히 일치했기 때문입니다.
문제가 워낙 광범위해서 2020년에 인간 유전자 명명을 담당하는 위원회는 스프레드시트와의 싸움을 계속하는 대신 해당 기호들의 이름을 바꿔버렸습니다. 유전자 이름을 바꾸는 쪽이 더 쉬운 길이었다면, 여러분의 상품 코드가 특별할 이유도 없습니다.
인코딩 문제는 별개이고, 똑같이 성가십니다
바이트 순서 표시(BOM) 없이 UTF-8로 저장한 CSV는 엑셀에서 시스템의 구형 인코딩으로 해석되어, 한글이나 악센트가 붙은 문자가 전부 깨집니다. 같은 파일에 BOM만 붙이면 멀쩡하게 열립니다.
같은 파일을 두고 어떤 동료는 정상인 표를 보고 다른 동료는 깨진 글자를 보는 이유가 이것입니다. 파일이 달라진 게 아니라 두 컴퓨터가 서로 다른 인코딩으로 추측했을 뿐입니다.
구분자조차 고정되어 있지 않습니다
엑셀이 항상 쉼표로 나누는 것은 아닙니다. 소수점 구분자가 쉼표인 지역, 그러니까 유럽 상당 부분에서는 엑셀이 필드 사이에 세미콜론을 기대하고, 쉼표로 구분된 파일은 한 열짜리 긴 텍스트로 들어옵니다.
파일이 잘못된 것이 아닙니다. 작성될 때와 다른 지역 설정으로 읽히고 있을 뿐이고, 인코딩 문제와 같은 부류이며 똑같은 불평을 낳습니다. 제 컴퓨터에서는 되는데요.
실제로 막는 방법
CSV를 더블클릭으로 여는 습관을 버리세요. 대신 데이터 탭의 가져오기 경로를 쓰고, 데이터가 시트에 들어오기 전에 위험한 열을 전부 텍스트로 지정하세요. 그 가져오기 창이야말로 판단이 엑셀이 아니라 여러분의 몫이 되는 유일한 순간입니다.
최근 버전은 추측 동작 자체를 설정으로 열어두었습니다. 옵션의 데이터 항목에서 날짜, 긴 숫자, 앞자리 0의 자동 변환을 각각 끌 수 있습니다. 내보낸 데이터를 자주 다루는 컴퓨터라면 셋 다 꺼두는 편이 좋습니다.
더 확실한 해결은 엑셀로 열 사람에게 CSV를 보내지 않는 것입니다. 진짜 .xlsx 통합 문서에서는 셀마다 형식이 함께 저장되므로, 텍스트로 기록한 열은 누가 어떤 컴퓨터의 어떤 지역 설정으로 열든 텍스트로 남습니다.
이미 엑셀을 거친 파일을 점검하는 법
엑셀에서 저장된 파일이라면 손상은 표시가 아니라 저장된 값에 있습니다. 그래서 열 너비를 넓혀봐야 아무것도 증명되지 않습니다. 원래보다 짧아진 식별자, 끝자리가 수상하게 0인 긴 숫자, 코드 열에 조용히 들어앉은 날짜를 찾아보세요.
가장 빠른 확인법은 원본 파일을 스프레드시트가 아니라 일반 텍스트로 열어서, 알고 있는 값 몇 개를 스프레드시트가 보여주는 값과 대조하는 것입니다. 서로 다르다면 그 스프레드시트는 데이터를 보여주는 창이 아닙니다. 다른 데이터셋이고, 남겨야 할 것은 텍스트 파일 쪽입니다.