ConvertBox Logo
ConvertBox

엑셀에서 CSV가 깨지는 이유: 인코딩, 구분자, 사라지는 0

CSV 파일에는 인코딩도, 구분자도, 열의 타입도 적혀 있지 않아서 더블클릭으로 열면 엑셀이 세 가지를 모두 추측합니다. 데이터 탭에서 가져오거나 모든 셀을 텍스트로 저장한 .xlsx로 변환하면 추측이 개입하지 않습니다.

ConvertBox게시

CSV에는 메타데이터가 없어 엑셀이 추측합니다

CSV는 일반 텍스트입니다. 구분자로 나뉜 값이 줄 단위로 이어질 뿐입니다. 어떤 문자 인코딩을 썼는지, 구분자가 쉼표인지 세미콜론인지, 00123이 숫자인지 코드인지 파일 어디에도 적혀 있지 않습니다. RFC 4180이 일반적인 형태를 설명하지만 강제력 있는 표준은 아니어서 실제 내보내기 결과는 제각각입니다. .csv를 더블클릭하면 엑셀은 Windows 국가별 설정과 각 값의 생김새를 근거로 이 질문들을 묻지 않고 추측으로 해결합니다. CSV에 관한 불만은 거의 모두 이 세 가지 추측 중 하나에서 비롯됩니다.

깨진 글자: 인코딩과 바이트 순서 표시(BOM)

요즘 시스템은 대부분 CSV를 UTF-8로 내보냅니다. 그런데 Windows용 엑셀은 더블클릭으로 연 파일이 바이트 순서 표시(BOM)인 EF BB BF 세 바이트로 시작하지 않으면 지역별 레거시 코드 페이지, 한국어 Windows에서는 CP949로 가정합니다. BOM이 없으면 ASCII가 아닌 글자가 모두 잘못 읽힙니다. UTF-8에서 한글 한 글자는 3바이트인데 CP949는 이를 2바이트씩 끊어 읽으므로, '가격'이 '媛寃'과 깨진 기호가 섞인 문자열로 나타납니다.

반대 방향의 문제도 있습니다. 예전 엑셀이 지역 코드 페이지로 저장한 CSV는 유효한 UTF-8이 아니므로, UTF-8을 기대하는 도구에서는 대체 문자가 표시됩니다. 이 사이트의 CSV 뷰어와 CSV to Excel 변환기는 파일을 UTF-8로만 읽으므로, 레거시 파일은 먼저 'CSV UTF-8' 형식으로 다시 저장해야 합니다. 두 도구가 내려 주는 CSV는 BOM 없는 UTF-8이어서 Windows 엑셀에서 더블클릭하면 같은 깨짐이 나타납니다. 반면 .xlsx 출력은 통합 문서 형식이 자체 인코딩을 선언하므로 이 문제가 없습니다.

전부 A열에 들어가는 문제: 구분자와 목록 구분 기호

엑셀은 쉼표를 전제하지 않습니다. Windows 국가별 설정의 목록 구분 기호로 나누는데, 독일·프랑스·스페인처럼 쉼표를 소수점으로 쓰는 지역에서는 이 기호가 세미콜론입니다. 그런 PC에서 쉼표로 구분된 파일을 열면 전체가 A열에 들어가고, 한국이나 미국 설정의 PC에서 세미콜론 파일을 열어도 마찬가지입니다. 소수점 기호도 같은 설정을 따르므로 1,5는 어떤 컴퓨터에서는 1.5이고 다른 컴퓨터에서는 두 개의 필드입니다.

엑셀에만 통하는 우회책으로 첫 줄에 sep=; 또는 sep=,를 적는 방법이 있습니다. 엑셀은 이 줄을 보고 구분자를 정하지만 다른 프로그램은 데이터로 취급하므로, 스크립트가 읽을 파일에는 넣지 않는 것이 좋습니다. 이 사이트의 CSV 뷰어는 처음 몇 줄을 살펴 쉼표, 탭, 세미콜론, 파이프를 자동으로 감지하고, 다운로드는 항상 쉼표로 구분합니다. CSV to JSON 변환기는 같은 네 가지 중에서 직접 고르게 되어 있습니다.

엑셀이 바꿔 버리는 값: 앞자리 0, 긴 숫자, 날짜

기본 서식인 '일반'에서 엑셀은 숫자나 날짜처럼 보이는 것을 모두 변환합니다. 그러면 셀에는 변환된 값이 들어가고, 다시 CSV로 저장하면 그 값이 기록되므로 원래 텍스트는 되찾을 수 없습니다.

이 사이트의 CSV 뷰어는 타입 변환을 하지 않고 각 셀의 텍스트를 그대로 보여 주므로, 파일에 실제로 무엇이 들어 있는지 빠르게 확인할 수 있습니다. CSV to Excel은 모든 셀을 텍스트로 기록해서 00123과 16자리 숫자가 보존됩니다. 대신 숫자 열은 '숫자로 변환'을 해 줘야 SUM에 포함됩니다. CSV to JSON은 기본값이 반대입니다. 자동 타입 변환이 따옴표로 감싼 값이라도 007을 7로 바꾸므로, 코드나 긴 ID가 있으면 이 옵션을 꺼야 합니다.

파일 내용엑셀에서 더블클릭한 뒤이유
00123123숫자로 읽으며 앞자리 0은 값에 영향이 없음
12345678901234561.23457E+15로 표시, 1234567890123450으로 저장숫자는 유효 숫자 15자리까지만 유지
1E51.00E+05100000의 지수 표기로 해석
1-21월 2일 같은 날짜올해의 월과 일로 보임
MAR1날짜(3월 1일)월 약어 뒤에 숫자가 붙은 형태
03/04/20243월 4일 또는 4월 3일일과 월의 순서는 국가별 설정을 따름

필드 안의 따옴표, 쉼표, 줄바꿈

구분자, 큰따옴표, 줄바꿈이 들어 있는 필드는 큰따옴표로 감싸야 하고, 그 안의 따옴표는 두 번 적습니다. 7,"Smith, Ann","She said ""hi"""라는 레코드는 필드가 세 개입니다. 7, 그다음 Smith, Ann, 그다음 She said "hi"입니다. 따옴표 처리 없이 값을 쉼표로 이어 붙여 만든 파일은 주소나 메모에 쉼표가 처음 등장하는 순간 열이 밀립니다.

따옴표 안의 줄바꿈은 규칙에 맞는 표기이며, 엑셀에서 Alt+Enter로 줄을 바꾼 셀이 있으면 그렇게 저장됩니다. 이때 레코드 하나가 물리적으로 두 줄에 걸치므로, 줄 수를 세거나 스크립트에서 개행 문자로 나누는 것처럼 줄 단위로 처리하는 도구는 깨진 행을 보게 됩니다. 이 사이트의 CSV 뷰어는 그런 필드를 한 셀에 유지합니다. CSV to JSON 변환기는 그렇지 않습니다. 따옴표를 살피기 전에 텍스트를 줄 단위로 나누기 때문에 여러 줄짜리 셀은 어긋난 행으로 나옵니다.

더블클릭 대신 안전하게 가져오기

추측을 피하는 길은 두 가지입니다. 하나는 CSV를 모든 셀이 텍스트인 .xlsx로 변환하는 것입니다. 다른 하나는 엑셀의 가져오기 대화 상자로, 더블클릭이 말없이 답해 버리는 세 가지 질문을 직접 묻습니다. 최신 Windows용 엑셀에서의 순서는 다음과 같습니다.

  • 빈 통합 문서를 열고, 파일을 직접 여는 대신 데이터 > 텍스트/CSV에서를 선택합니다.
  • 파일 원본을 65001: 유니코드(UTF-8)로 지정하고 미리 보기에서 한글이 제대로 보이는지 확인합니다.
  • 파일이 실제로 쓰는 구분 기호를 고릅니다.
  • 데이터 형식 검색을 '데이터 형식 검색 안 함'으로 두거나, 데이터 변환을 눌러 코드·ID·전화번호 열을 텍스트로 지정한 뒤 로드합니다.
  • 원본 CSV는 그대로 두고 작업 결과는 .xlsx로 저장합니다. CSV로 다시 저장하면 엑셀이 현재 셀에 가지고 있는 값이 그대로 기록됩니다.

다른 사람의 소프트웨어에서도 멀쩡한 CSV 만들기

파일을 만드는 쪽이라면 몇 가지 선택만으로 이후의 문제를 대부분 막을 수 있습니다. 먼저 데이터를 정리합니다. 값 앞뒤의 불필요한 공백, 완전히 빈 행, 정확히 중복된 행은 가져온 뒤보다 가져오기 전에 없애는 편이 쉽습니다. 이 사이트의 데이터 클리너는 이 세 가지를 정해진 순서로 처리하며, 중복을 제거할 때는 행 전체를 비교합니다.

  • UTF-8로 인코딩합니다. BOM은 받는 사람이 엑셀에서 더블클릭으로 열 때만 붙입니다. 다른 파서 중에는 BOM을 첫 번째 열 이름 맨 앞의 보이지 않는 문자로 읽는 것이 있습니다.
  • 날짜는 ISO 8601(2024-03-09)로 적습니다. 어떤 국가별 설정에서도 순서가 뒤바뀌어 읽히지 않습니다.
  • 숫자는 천 단위 구분 기호 없이, 소수점은 마침표로 적고 이 규칙을 문서에 남깁니다.
  • 구분자, 따옴표, 줄바꿈이 들어갈 수 있는 필드는 모두 따옴표로 감싸고, 모든 행의 필드 수를 같게 맞춥니다.
  • 받는 쪽이 프로그램이 아니라 엑셀 사용자라면 .xlsx로 보냅니다. 타입과 인코딩이 파일에 명시적으로 저장됩니다.

이 가이드에서 다룬 도구

관련 가이드

전체 가이드