從試算表取得資料而不失去理智

有人寄給你一個 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 實際想要的

網頁應用程式偏好 JSON:

[
  {"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 需要巢狀陣列,你要手動工作。

常見工作流程

1. 從 Excel/Sheets 匯出為 CSV
2. 在文字編輯器中打開,檢查明顯的問題
3. 轉換為 JSON
4. 驗證 JSON 結構
5. 抽查幾筆記錄

抽查很重要。自動轉換可能會靜默地損壞資料。日期變成數字。郵遞區號失去前導零。「O'Brien」變成「O'Brien」。在信任之前驗證。

處理奇怪的資料

日期。 Excel 在內部將日期儲存為數字。匯出可能產生「44927」而不是「2023-01-15」。轉換前知道你的日期格式。

作為文字的數字。 郵遞區號、電話號碼、ID——這些看起來像數字但不應該被視為數字。前導零很重要。明確轉換為字串。

Unicode。 帶重音符號的名字、非拉丁文字的資料。確保你的管道從頭到尾處理 UTF-8。

反向轉換

JSON 到 CSV 會丟失資訊。巢狀物件平面化很笨拙。陣列變成...什麼,確切地說?多行?連接的字串?

對於具有一致結構的平面 JSON,轉換工作正常。對於複雜的巢狀資料,你要決定如何平面化它。記錄那些決定。

YAML 和 XML

YAML 是 JSON 的時髦表親。更可讀,相同的資料結構,容易在兩者之間轉換。

XML 是企業版本。冗長,廣泛支援舊系統,需要更多的解析工作。

兩者都可以轉換為 JSON 並返回。主要的頭痛是 XML 中的屬性 vs 元素——JSON 沒有這種區別。

---

資料轉換是關於知道什麼可能出錯並檢查它。自動化轉換,驗證結果,永遠不要相信「它就是能用」。