Excel to CSV Conversion: Common Pitfalls and How to Avoid Them - CSV-X.com

아직도 Excel에서의 무해해 보이는 CSV 내보내기 때문에 제 회사에 $47,000을 잃었던 날이 기억납니다. 2016년이었고, 저는 중간 규모의 금융 서비스 회사에서 데이터 통합 전문가로서 3년을 보낸 상태였습니다. 우리는 고객 기록을 새로운 CRM 시스템으로 이전하고 있었고, 저는 180,000개의 고객 기록을 가져오기 위해 준비하는 업무를 맡았습니다. Excel 파일은 완벽해 보였습니다. Notepad에서 열었을 때 내보낸 CSV도 완벽해 보였습니다. 그러나 토요일 오전 3시에 가져오기 작업이 실행될 때, 우리 고객 전화번호의 23%가 손상되었고, 주소 필드는 의미 없는 방식으로 병합되었으며, 우리가 신중하게 유지해온 날짜 형식은 알아볼 수 없는 혼란으로 변했습니다.

복구하는 데에는 두 주가 걸렸고, 수천 개의 기록을 수동으로 검증해야 했으며, CRM 출시를 한 달 지연해야 했습니다. 그 경험은 "CSV로 저장"이 단순한 버튼 클릭이라고 생각하는 사람에서 Excel-CSV 변환의 모든 뉘앙스를 이해하는 데 집착하게 만든 사람으로 저를 변화시켰습니다. 지난 11년 동안, 저는 40개 이상의 회사가 유사한 재앙을 피하도록 도왔고, 이 과정이 잘못될 수 있는 거의 모든 방법을 보았습니다.

대부분의 사람들이 깨닫지 못하는 것은: Excel과 CSV 파일은 근본적으로 다른 존재이며, Excel의 CSV 내보내기 기능은 여러분의 데이터를 조용히 손상시킬 수 있는 수십 가지 가정을 합니다. , 제가 만난 가장 일반적인 함정을 통해 여러분을 안내하고 이를 피할 수 있는 전투 검증된 전략을 제공하겠습니다.

문자 인코딩 함정: 데이터가 잘못된 언어로 말할 때

문자 인코딩은 CSV 변환의 조용한 살인자입니다. 제 컨설팅 업무에서, 제가 조사한 "손상된 CSV" 문제의 60%가 인코딩 문제로 돌아간다고 추정합니다. 이것이 중요한 이유는: Excel은 일반적으로 CSV 파일을 시스템의 기본 인코딩으로 저장하는데, Windows에서는 흔히 Windows-1252 또는 ANSI입니다. 그러나 대부분의 현대 웹 애플리케이션, 데이터베이스 및 데이터 처리 도구는 UTF-8 인코딩을 예상합니다.

원인을 알게 되면 증상은 분명합니다. "José García"와 같은 고객 이름은 "José García"로 변하고, 통화 기호는 물음표나 박스로 변형됩니다. 유럽 언어의 악센트가 있는 문자는 헛소리가 됩니다. 저는 47개국에서 온 이름이 포함된 환자 기록을 보유한 의료 제공자와 작업한 적이 있습니다. Excel의 기본 설정을 사용하여 CSV로 내보낼 때, 95,000명의 환자 이름 중 대략 8,000개가 손상된 문자를 포함하고 있었습니다.

문제를 해결하려면 Excel의 "CSV로 저장" 옵션이 인코딩 제어를 제공하지 않는다는 것을 이해해야 합니다. 대신 "다른 이름으로 저장"을 선택하고 파일 형식 드롭다운에서 "CSV UTF-8(쉼표로 구분됨)"을 선택해야 합니다. 이 옵션은 Excel 2016에 추가되었으며, 이전 Excel 버전에서는 우회 방법을 사용해야 합니다: 유니코드 텍스트로 저장한 다음 텍스트 편집기 또는 스크립팅 언어를 사용하여 적절한 UTF-8 CSV 형식으로 변환합니다.

그러나 여기에는 심지어 경험이 있는 사용자도 걸려드는 함정이 있습니다: Excel의 UTF-8 CSV 옵션은 파일의 시작 부분에 BOM(Byte Order Mark)을 포함합니다. 이는 일부 애플리케이션이 인코딩을 인식하는 데 도움이 되지만 다른 애플리케이션에서는 문제를 일으킵니다. BOM 접두사가 붙은 파일에서 Unix 기반 시스템이 오류를 발생시키는 것을 보았습니다. 이러한 첫 세 바이트를 실제 데이터로 간주합니다. BOM을 잘 처리하지 않는 시스템에서 작업하는 경우, 인코딩 조작을 지원하는 텍스트 편집기를 사용하여 이를 제거하거나 간단한 스크립트를 사용해야 합니다.

제 추천은: 항상 작은 샘플 파일로 CSV 가져오기를 테스트하세요. 100개의 기록을 가져와서 특수 문자가 올바르게 표시되는지 확인한 다음 전체 데이터 세트로 진행하세요. 이 5분 테스트로 클라이언트가 수많은 시간의 정리 작업을 절약했습니다.

선행 제로 학살: Excel이 데이터의 유형을 결정할 때

Excel의 주도적인 데이터 유형 해석은 아마도 다른 어떤 기능보다 데이터 무결성을 더 많이 파괴했을 것입니다. 문제는 간단하지만 교활합니다: Excel은 여러분의 데이터를 보고 어떤 유형이어야 할지 결정하는데, 종종 여러분이 텍스트로 원한 것을 숫자로 변환합니다. 가장 흔한 희생자는? 선행 제로입니다.

"Excel의 'CSV로 저장' 버튼은 데이터 내보내기 도구가 아닙니다—여러분의 인코딩, 구분 기호 및 형식에 대해 조용한 가정을 만드는 데이터 변환 지뢰밭입니다. 이로 인해 수천 개의 기록이 밀리초 안에 손상될 수 있습니다."

저는 340,000개의 전화번호를 유지하는 통신 회사와 작업했습니다. 이 전화번호 중 많은 부분이 제로로 시작했으며, 국제 다이얼 코드와 일부 지역 형식에서는 흔한 일입니다. 그들이 Excel 스프레드시트를 CSV로 내보낼 때, 모든 선행 제로가 사라졌습니다. "0412345678"과 같은 전화번호는 "412345678"이 되었습니다. "02134"와 같은 ZIP 코드는 "2134"가 되었고, "00456-B"와 같은 제품 코드는 "456-B"가 되었습니다.

재정적 impact는 상당했습니다. 그들의 콜센터는 고객 기반의 18%에 접근할 수 없었습니다. 전화번호가 불완전했기 때문입니다. 그들은 백업 시스템과 크로스 체크를 해야 했고, 수동으로 데이터를 복구해야 했으며, 새로운 검증 절차를 구현해야 했습니다. 그 프로젝트는 200인시간을 소모했으며 주요 마케팅 캠페인을 지연시켰습니다.

무대 뒤에서 일어나는 일은 다음과 같습니다: CSV 파일을 Excel에서 열면, Excel은 데이터를 자동으로 해석합니다. 제로로 시작하는 숫자는 숫자 형식으로 변환되어 선행 제로가 제거됩니다. 그런 다음 CSV로 다시 저장하면 그 제로는 영원히 사라집니다. 신용 카드 번호나 계좌 ID와 같은 긴 숫자 문자열에서 같은 일이 발생합니다—Excel은 과학적 표기법(1.23E+15)으로 변환하며, 정밀도가 손실됩니다.

해결책은 다면적인 접근 방식을 요구합니다. 먼저, CSV로 내보낼 데이터를 Excel에서 생성하는 경우, 데이터를 입력하기 전에 해당 열의 형식을 텍스트로 설정하십시오. 열을 오른쪽 클릭하고, 셀 서식 선택 및 텍스트를 선택합니다. 이렇게 하면 Excel에 모든 것을 리터럴 텍스트로 취급하도록 지시하여 선행 제로를 보존하고 과학적 표기를 방지합니다.

둘째, 편집하기 위해 기존 CSV 파일을 Excel에서 열 때는 단순히 두 번 클릭하지 마십시오. 대신, 먼저 Excel을 열고, 데이터 탭의 '텍스트/CSV에서' 가져오기 마법사를 사용하십시오. 이렇게 하면 각 열이 어떻게 해석되는지를 제어할 수 있습니다. 특정 열을 텍스트로 처리하도록 지정할 수 있어 원래 형식을 보존할 수 있습니다.

셋째, 실제로 CSV를 Excel에서 열어야 할 필요가 있는지 고려하십시오. 간단한 편집의 경우 텍스트 편집기가 더 안전할 수 있습니다. 복잡한 변환의 경우 Python과 같은 스크립팅 언어나 전문 CSV 편집기를 사용하면 Excel의 "도움이 되는" 자동 변환 없이 더 많은 제어를 할 수 있습니다.

구분 기호 딜레마: 쉼표가 쉼표가 아닐 때

CSV의 "C"는 "쉼표"를 의미하지만, 무한한 혼란을 일으키는 비밀이 있습니다: Excel은 CSV 파일을 저장할 때 항상 쉼표를 구분 기호로 사용하지 않습니다. 대신, 지역별로 다르게 설정된 시스템의 목록 구분 기호 설정을 사용합니다. 미국에서는 쉼표입니다. 많은 유럽 국가에서는 세미콜론입니다. 일부 지역에서는 탭 문자입니다.

저는 12개국에 사무소를 둔 다국적 기업의 컨설팅을 하면서 이 사실을 어렵게 알게 되었습니다. 그들의 독일 사무소는 CSV 파일을 내보냈고, 그 파일을 미국 사무소에서는 올바르게 가져올 수 없었습니다. 파일은 Excel에서 잘 열렸지만, 그들의 데이터베이스 시스템에 가져올 때 모든 행이 단일 필드가 되었습니다. 문제는? 독일 시스템은 세미콜론을 구분 기호로 사용했지만, 미국 가져오기 도구는 쉼표를 예상했습니다.

이 문제는 제가 작업한 국제 데이터 전송의 약 30%에 영향을 미칩니다. 증상은 다양합니다: 때때로 가져오기가 완전히 실패하고, 때때로 가져오기가 성공하지만 모든 데이터가 첫 번째 열에 들어가고, 때때로 데이터에서 쉼표가 구분 기호로 해석되어 이상한 필드 분할이 발생합니다.

근본 원인은 Excel의 CSV 내보내기가 Windows 지역 설정 목록 구분 기호를 사용하기 때문입니다. 제 것을 확인하려면 제어판 > 지역 > 추가 설정으로 이동하면 됩니다. 그러나 이 시스템 전반의 설정을 변경하면 다른 애플리케이션에 영향을 미치고 대부분의 사용자에게는 실용적인 솔루션이 아닙니다.