正気を保ちながらスプレッドシートからデータを取り出す
CSV、JSON、Excel——フォーマット間の変換で全てを壊さない方法。
誰かがExcelファイルをメールしてきます。アプリケーションはJSONが必要です。これはシンプルなはず。
そうは行きません。
スプレッドシートには結合セルがあります。日付が変な形式だったり。数値フィールドのはずの列に誰かが「N/A」と入力していたり。あらゆる変換がデバッグセッションになります。
もう少し楽にする方法がこちらです。
CSV：万能翻訳機
ほぼすべてのスプレッドシートアプリはCSVにエクスポートできます。ほぼすべてのシステムがCSVを読めます。データフォーマットの最小公倍数です。
CSVはただのテキスト：値をカンマで区切り、行を改行で区切ります。
name,email,age
John,john@example.com,32
Jane,jane@example.com,28
シンプル。そうでない時まで。
カンマ問題。 値にカンマが含まれていたら？名前として「Smith, John」があるとパースが壊れます。解決策：値を引用符で囲む。良いCSVツールはこれを自動で処理します。悪いツールはしません。
改行問題。 値に改行が含まれていたら？同じ解決策：引用符。同じ注意点：すべてのツールが処理するわけではない。
エンコーディング問題。 WindowsのExcelとMacのExcelはデフォルトの文字エンコーディングが異なります。特殊文字が壊れます。可能なら「CSV UTF-8」としてエクスポートしましょう。
JSON：APIが実際に欲しいもの
Webアプリケーションはこっちが好きです：
[
{"name": "John", "email": "john@example.com", "age": 32},
{"name": "Jane", "email": "jane@example.com", "age": 28}
]
CSVからJSONへの変換は通常簡単です。最初の行がプロパティ名になり、後続の行がオブジェクトになります。
問題が起きるのは：
型が失われる。 CSVはすべて文字列。「32」は数値かもしれないし、テキストかもしれない。JSON変換は推測しなければなりません。ほとんどのツールは：数値に見えたら、たぶん数値だろうと。
Nullが曖昧。 空セル = null？空文字列？プロパティを完全に省略？ツールによって決定が異なります。
配列が存在しない。 CSVはフラット。JSONにネストされた配列が必要なら、手作業です。
一般的なワークフロー
- Excel/SheetsからCSVとしてエクスポート
- テキストエディタで開き、明らかな問題をチェック
- JSONに変換
- JSON構造を検証
- いくつかのレコードをスポットチェック
スポットチェックが重要です。自動変換は静かにデータを破壊することがあります。日付が数値になります。郵便番号が先頭のゼロを失います。「O'Brien」が「O'Brien」になります。信じる前に検証しましょう。
変なデータの扱い
日付。 Excelは日付を内部的に数値として保存します。エクスポートは「2023-01-15」ではなく「44927」を生成することがあります。変換前に日付フォーマットを把握しましょう。
テキストとしての数値。 郵便番号、電話番号、ID——これらは数値に見えますが、数値として扱うべきではありません。先頭のゼロが重要です。明示的に文字列に変換しましょう。
Unicode。 アクセント付きの名前、非ラテン文字のデータ。パイプライン全体でUTF-8を確実に処理するようにしましょう。
逆方向
JSONからCSVは情報が失われます。ネストされたオブジェクトは不格好にフラット化されます。配列は...何に？複数行？連結された文字列？
一貫した構造のフラットなJSONなら、変換は問題ありません。複雑なネストデータでは、フラット化の方法について決定を下すことになります。その決定をドキュメント化しましょう。
YAMLとXML
YAMLはJSONのヒップスターいとこ。より読みやすく、同じデータ構造、相互変換が簡単。
XMLはエンタープライズ版。冗長で、古いシステムに広くサポートされ、パース労力がより必要。
両方ともJSONと相互変換できます。主な頭痛の種はXMLの属性 vs 要素——JSONにはその区別がありません。
データ変換は何が間違う可能性があるかを知り、それをチェックすることです。変換を自動化し、結果を検証し、「ただ動く」と信じてはいけません。