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％の顧客電話番号が破損し、意味のない形で住所欄が統合され、丁寧に維持していた日付形式が認識できない混乱に変わりました。

回復には2週間かかり、数千のレコードを手動で確認する必要があり、CRMの発売を1ヶ月遅らせる必要がありました。その経験は、「CSVとして保存」を単なるボタンクリックだと思っていた私を、ExcelからCSVへの変換のあらゆるニュアンスを理解することに執着する人間に変えました。この11年間で、私は40社以上が同様の災害を避ける手助けをしてきましたし、このプロセスが間違う可能性があるほぼすべての方法を見てきました。

ほとんどの人が気づかないのは、ExcelとCSVファイルは根本的に異なるものであり、ExcelのCSVエクスポート機能は、あなたのデータを静かに破損させる多くの仮定をしています。私は、私が遭遇した最も一般的な7つの落とし穴をお伝えし、それを避けるための実績のある戦略を提供します。

文字エンコーディングの罠: データが間違った言語を話すとき

文字エンコーディングは、CSV変換の静かな殺人者です。私のコンサルティング業務では、私が調査する「破損したCSV」問題の約60％がエンコーディングの問題に起因しています。これはなぜ重要かと言うと、Excelは通常、CSVファイルをシステムのデフォルトエンコーディング（Windowsでは多くの場合Windows-1252またはANSI）で保存します。しかし、ほとんどの現代のウェブアプリケーション、データベース、データ処理ツールはUTF-8エンコーディングを期待しています。

探すべき症状は確実です。顧客名の「José García」は「José García」になります。通貨記号は疑問符やボックスに変わります。ヨーロッパ言語のアクセント付き文字は意味不明になります。私は以前、47カ国からの名前を含む患者記録を持つヘルスケアプロバイダーと仕事をしました。Excelのデフォルト設定でCSVにエクスポートしたとき、約8,000件の患者名から破損した文字が含まれていました。

この修正には、Excelの「CSVとして保存」オプションがエンコーディングの制御を与えないという理解が必要です。代わりに、「名前を付けて保存」を使用し、ファイルタイプのドロップダウンから「CSV UTF-8（カンマ区切り）」を選ぶ必要があります。このオプションはExcel 2016で追加されたもので、それ以前のExcelバージョンでは回避策を使用する必要があります: ユニコードテキストとして保存し、テキストエディタまたはスクリプト言語を使用して適切なUTF-8 CSV形式に変換します。

しかし、ここに経験豊富なユーザーでさえつまずく落とし穴があります: ExcelのUTF-8 CSVオプションは、ファイルの先頭にBOM（バイトオーダーマーク）を含みます。これにより一部のアプリケーションがエンコーディングを認識するのに役立ちますが、他のアプリケーションには問題を引き起こします。私はUnixベースのシステムがBOM付きファイルに対処できず、最初の3バイトを実際のデータとして扱って苦しむのを見たことがあります。BOMを適切に処理できないシステムで作業している場合、エンコーディング操作をサポートするテキストエディタを使用してそれを削除する必要があります。または、簡単なスクリプトを使用する必要があります。

私の推奨は: まず、小さなサンプルファイルでCSVインポートをテストしてください。100件のレコードをインポートし、特殊文字が正しく表示されることを確認してから、フルデータセットを進めてください。この5分間のテストは、私のクライアントの数え切れない時間のクリーンアップ作業を保存しました。

先頭ゼロの大虐殺: Excelがデータが何であるべきかを決めるとき

Excelの積極的なデータ型解釈は、おそらく他のどの機能よりもデータの整合性を破壊してきました。問題は単純ですが厄介です: Excelはデータを見て、何の型であるべきかを決めるため、しばしばテキストとして希望するものを数字に変換します。最も一般的な犠牲者は? 先頭ゼロです。

「Excelの『CSVとして保存』ボタンはデータエクスポートツールではありません; それはあなたのエンコーディング、区切り文字、フォーマットに関する静かな仮定を行い、ミリ秒で数千のレコードを破損させるデータ変換の地雷原です。」

私は340,000件の電話番号のデータベースを持つ通信会社と働いたことがあります。これらの多くは、国際ダイヤルコードや一部の地域形式で一般的に見られるようにゼロで始まりました。彼らがExcelスプレッドシートをCSVにエクスポートすると、すべての先頭ゼロが消えてしまいました。「0412345678」のような電話番号は「412345678」に、「02134」のようなZIPコードは「2134」に、「00456-B」のような商品コードは「456-B」になりました。

その金銭的影響は重大でした。彼らのコールセンターは、電話番号が不完全だったため、顧客基盤の18％に連絡を取ることができませんでした。彼らはバックアップシステムと照合し、データを手動で再構築し、新しい検証手順を実施しなければなりませんでした。このプロジェクトは200人時のリソースを消費し、大規模なマーケティングキャンペーンを遅らせました。

裏で何が起きているかを説明します: CSVファイルをExcelで開くと、自動的にデータが解釈されます。ゼロで始まる数字は数値形式に変換され、先頭のゼロが失われます。そして、再度CSVとして保存すると、これらのゼロは永遠に消えます。クレジットカード番号や口座IDのような長い数値の文字列でも同じことが起こります―Excelはそれらを科学的表記（1.23E+15）に変換し、精度を失います。

解決策には多面的なアプローチが必要です。まず、CSVにエクスポートされるデータをExcelで作成する場合は、データを入力する前にそれらの列をテキストとしてフォーマットしてください。列を右クリックし、「セルの書式設定」を選択して、「テキスト」を選択します。これによりExcelはすべてを文字列として扱い、先頭ゼロを保持し、科学的表記を防ぎます。

次に、既存のCSVファイルをExcelで開いて編集する場合は、ダブルクリックするだけではなく、まずExcelを開いてから、「データ」タブの「テキスト/CSVから」を使ってインポートウィザードを利用してください。これにより、各列の解釈方法を制御できます。特定の列をテキストとして扱うよう指定することができ、元のフォーマットを保持します。

三つ目に、実際にExcelでCSVを開く必要があるかどうかを考えてください。簡単な編集の場合、テキストエディタがより安全かもしれません。複雑な変換が必要な場合、Pythonのようなスクリプト言語や特化したCSVエディタを使用すると、Excelの「便利な」自動変換なしにより多くの制御が得られます。

区切り文字のジレンマ: カンマがカンマでないとき

CSVの「C」は「カンマ」を意味しますが、無限の混乱を引き起こす秘密があります: ExcelはCSVファイルを保存する際に、常にカンマを区切り文字として使用するわけではありません。代わりに、システムのリスト区切り設定を使用しますが、これは地域によって異なります。アメリカではカンマです。多くのヨーロッパ諸国ではセミコロンです。一部の地域ではタブ文字です。

私は12カ国にオフィスを持つ多国籍企業のコンサルティングでこのことに気づきました。彼らのドイツのオフィスがエクスポートしたCSVファイルは、アメリカのオフィスで正しくインポートできませんでした。Excelでファイルを開くと問題ありませんでしたが、データベースシステムにインポートすると、すべての行が1つのフィールドになりました。問題は、ドイツのシステムがセミコロンを区切り文字として使用しているのに対し、アメリカのインポートツールがカンマを期待していたことです。

この問題は、私が取り組んだ国際データ転送の約30％に影響します。症状はさまざまであり、インポートが完全に失敗することもあれば、成功するがすべてのデータが最初の列に配置されることもあります。また、データ内のカンマが区切り文字として解釈される奇妙なフィールド分割が発生することもあります。

根本的な原因は、ExcelのCSVエクスポートがWindowsの地域設定リスト区切りを使用することです。これを確認するには、コントロールパネル > 地域 > 追加設定に移動してください。しかし、このシステム全体の設定を変更することは他のアプリケーションに影響を与え、ほとんどのユーザーにとっては実用的な解決策ではありません。