CSV 파일을 더블클릭하면 Excel이 "도와줍니다". 우편번호 01234는 1234가 됩니다. 주문번호 4012888888881881은 4.01289E+15가 되고, 마지막 자리는 0으로 바뀝니다. 제품 코드 3-5는 5월 3일이 됩니다. 이런 내용은 파일에 전혀 없습니다. Excel이 파일을 여는 과정에서 바꾼 것이고, 저장하면 그대로 굳어집니다.
해결책은 매번 다른 방식으로 열어 보며 운에 맡기는 것이 아닙니다. Excel이 추측하지 못하게 막으면 되고, 1분 정도면 됩니다.
빠른 답
- CSV를 더블클릭하지 마세요. 데이터 → 텍스트/CSV에서를 사용하고, 깨지기 쉬운 열을 불러오기 전에 텍스트로 지정합니다.
- Microsoft 365에서는 파일 → 옵션 → 데이터 → 자동 데이터 변환에서 원하지 않는 변환을 끕니다.
- 더블클릭으로 연 뒤에는 원본을 덮어쓰지 마세요. 손상은 그 순간 파일에 기록됩니다.
- Google 스프레드시트: 파일 → 가져오기에서 텍스트를 숫자, 날짜, 수식으로 변환의 체크를 해제합니다.
Excel이 바꾸는 것과 그 이유
CSV에는 자료형이 없습니다. 모든 값이 텍스트입니다. Excel은 숫자와 날짜를 원하기 때문에 열마다, 셀마다 추측합니다. 문제가 되는 추측은 다음과 같습니다.
| 원래 값 | Excel 표시 | 일어난 일 |
|---|---|---|
01234 | 1234 | "숫자"의 앞자리 0이 사라짐 |
4012888888881881 | 4.01289E+15 | 지수 표기. 15번째 자리 뒤는 0이 됨 |
3-5, 1/2 | 5-Mar, 2-Jan | 날짜로 읽음 |
MARCH1, SEPT2 | 1-Mar, 2-Sep | 날짜로 읽음(유전자 이름에서 유명한 문제) |
1E5 | 100000 | 지수 표기로 읽음 |
=1+1 | 2 | 수식으로 읽음 |
TRUE | TRUE(불리언으로) | 대개 무해하지만, 이름일 때는 문제 |
이 중 두 가지는 보기에만 그치지 않고 되돌릴 수 없는 손상입니다. 16자리 숫자가 지수 표기가 되면 마지막 자리가 사라집니다. Excel은 숫자를 유효 숫자 15자리로 저장하기 때문입니다. 그리고 날짜로 읽힌 값은 날짜 일련번호로 저장됩니다. 서식을 텍스트로 되돌려도 3-5가 아니라 46086 같은 일련번호가 보입니다.
중요한 순간은 저장입니다. CSV를 열면 보이는 모습만 바뀝니다. CSV로 저장하면 보이는 내용이 파일에 다시 기록되고, 원래 값은 사라집니다.
해결 1: 열지 말고 가져오기
2016 이후의 모든 Excel 데스크톱 버전에서 작동합니다.
- 빈 통합 문서를 엽니다.
- 데이터 → 텍스트/CSV에서를 선택하고 파일을 고릅니다.
- 미리 보기에서 데이터 변환(불러오기가 아닙니다)을 클릭합니다.
- Power Query에서 깨지기 쉬운 열을 각각 선택합니다. ID, 우편번호, 전화번호, 제품 코드, 계좌번호 등입니다. 그다음 변환 → 데이터 형식 → 텍스트를 선택합니다. 물어보면 현재 항목 바꾸기를 선택합니다.
- 닫기 및 로드를 클릭합니다.
값이 적힌 그대로 들어옵니다. 실제로 숫자인 열은 그대로 계산할 수 있습니다. 문제가 되는 곳에서만 추측을 껐기 때문입니다.
예전 마법사를 쓰고 싶다면 파일 → 옵션 → 데이터 → 레거시 데이터 가져오기 마법사 표시 → 텍스트에서(레거시)를 선택합니다. 그러면 데이터 → 데이터 가져오기 → 레거시 마법사에 나타나며, 마법사의 3단계에서 열을 텍스트로 지정할 수 있습니다.
해결 2: 자동 변환 끄기
최신 Microsoft 365용 Excel에는 바로 이 용도의 설정이 있습니다. 파일 → 옵션 → 데이터 → 자동 데이터 변환에 있습니다.
- 앞의 0을 제거하고 숫자로 변환
- 긴 숫자의 처음 15자리만 유지하고 지수 표기로 표시
- 문자 "E" 앞뒤의 숫자를 지수 표기 숫자로 변환
- 연속된 문자와 숫자를 날짜로 변환
문제가 되는 항목만 체크를 해제하세요. CSV를 열 때 Excel이 무언가를 변환하면 알려 주는 옵션도 있는데, 켜 두는 편이 좋습니다. 조용히 일어나던 변경이 눈에 보이게 됩니다.
이 설정은 더블클릭으로 열 때도 적용되므로 가장 편리한 해결책입니다. 다만 이식성은 가장 떨어집니다. 파일을 받는 사람에게는 그 사람의 설정이 따로 있기 때문입니다.
해결 3: Google 스프레드시트와 Numbers
Google 스프레드시트도 기본적으로 변환합니다. 파일 → 가져오기 → 업로드를 사용하고, 가져오기 대화상자에서 텍스트를 숫자, 날짜, 수식으로 변환의 체크를 해제하세요. 모든 값이 적힌 그대로 텍스트로 들어옵니다.
Mac과 iPhone의 Numbers도 앞자리 0을 없애고 날짜처럼 보이는 값을 날짜로 읽습니다. 열을 선택하고 서식을 텍스트로 지정하면 새로 입력하는 값에는 적용되지만, 열 때의 변환은 이미 끝난 뒤입니다. 데이터가 중요하다면 알고 있는 값 몇 개를 원본 파일과 비교해 보세요.
영리해 보이지만 소용없는 방법
- CSV 안에서 값을
="01234"로 감싸기. Excel에는01234로 보입니다. 하지만 파일을 읽는 다른 모든 도구에는 수식으로 보이고, 내보낸 데이터가 들어가는 다음 시스템에서도 마찬가지입니다. - 작은따옴표를 붙여
'01234로 만들기. 같은 문제입니다. Excel 외의 모든 곳에서 작은따옴표가 데이터의 일부가 됩니다. - 연 다음에 열 서식을 텍스트로 바꾸기. 너무 늦었습니다. 0은 이미 사라졌고, 서식으로는 되살릴 수 없습니다.
휴대폰에서
휴대폰도 같은 문제가 있는데 설정은 더 적습니다. iPhone의 Numbers, 그리고 Sheets와 Excel 앱은 모두 열 때 변환하며, 거기서 다시 저장한 CSV에는 변경 사항이 그대로 남습니다.
모든 셀을 텍스트로 다루는 CSV 앱은 변환 자체를 하지 않으므로 이 문제가 없습니다. Smart CSV Viewer에서는 열에 숫자, 통화, 날짜가 들어 있다고 지정해 올바르게 정렬하고 필터링할 수 있습니다. 하지만 자료형은 열이 표시되는 방식만 바꿉니다. 파일 안의 텍스트는 그대로입니다.
열을 숫자, 통화, 날짜로 보여 주되 내용은 다시 쓰지 않으므로, 앞자리 0과 긴 ID, 코드가 파일에 있는 그대로 유지됩니다.
기능들여 두면 좋은 습관
스프레드시트를 거친 CSV를 믿기 전에, 깨지기 쉬운 값 세 가지를 확인하세요. 앞자리가 0인 ID 하나, 파일에서 가장 긴 숫자, 날짜처럼 보이는 코드 하나입니다. 이 세 가지가 무사하다면 나머지도 거의 확실히 무사합니다. CSV와 XLSX 비교 가이드에서 Excel만이 아니라 모든 스프레드시트에서 이런 일이 생기는 이유를 설명합니다.
