JSON เป็น Excel: สี่วิธีและจุดที่แต่ละวิธีสูญเสียความสมบูรณ์

สรุปสั้น: array ของ object แบน ๆ หรือ object ที่ห่อ array แปลงเร็วที่สุดผ่านเครื่องมือออนไลน์; ใช้ Power Query เมื่อข้อมูลรีเฟรชทุกสัปดาห์ ใช้ pandas เมื่อมีฟิลด์ต้องทำความสะอาดเยอะ และใช้สคริปต์ Node เฉพาะเมื่อการแปลงต้องเข้าร่วมไปป์ไลน์ — แต่ต้องตัดสินว่า "หนึ่งแถว" หมายถึงอะไรก่อนแตะข้อมูลที่ซ้อนหรือคีย์ที่ไม่สม่ำเสมอ

การแปลงเองไม่ยาก สิ่งที่ยากคือการค้นพบทีหลังว่าตัวเลขกลายเป็นข้อความและการซ้อนหลายระดับยุบเหลือระดับเดียว อ่านโครงสร้างข้อมูลก่อนเลือกวิธีจะช่วยให้ไม่ต้องทำงานซ้ำ

1. ก่อนอื่น ดูว่าคุณมี JSON ชนิดไหน

เปิดไฟล์แล้วดูระดับนอกสุด:

สองแบบแรกแปลงตรง ๆ ได้ด้วยอัตราความสำเร็จเกือบ 100%; สำหรับสามแบบหลัง ต้องตัดสินก่อนว่า "หนึ่งแถว" หมายถึงอะไร ไม่อย่างนั้นชีตที่ได้จะใช้งานไม่ได้

2. วิธีที่ 1: เครื่องมือออนไลน์ (1 นาที)

เปิด JSON เป็นตาราง วางข้อมูล → เลือกตัวคั่นและส่วนหัว → ส่งออก CSV → เปิดใน Excel เหมาะกับข้อมูลที่มีโครงสร้างดีตั้งแต่ไม่กี่สิบจนถึงไม่กี่พันแถว

สามสิ่งที่ต้องระวัง:

• คุณได้ CSV ไม่ใช่ xlsx เมื่อ Excel เปิด CSV มันเดาการเข้ารหัสจาก locale ของระบบ ข้อความภาษาจีนอาจกลายเป็นตัวขยะ การใช้ "Data → From Text/CSV" แล้วเลือก UTF-8 อย่างชัดเจนเชื่อถือได้กว่า
• ตัวระบุที่ขึ้นต้นด้วย 0 สูญเสียศูนย์ "007" เป็นข้อความใน CSV แต่ Excel แปลงเป็นตัวเลข 7 โดยอัตโนมัติ กำหนดคอลัมน์นั้นเป็นข้อความตอนนำเข้า หรือเติมคำนำหน้าให้ใน JSON
• ตัวเลขยาวกลายเป็นสัญกรณ์วิทยาศาสตร์ บัตรประชาชนและเลขคำสั่งซื้อที่ยาวกว่า 15 หลักสูญเสียความแม่นยำ ฟิลด์แบบนี้ควรเป็นสตริงใน JSON อยู่แล้ว และต้องคงรูปแบบข้อความหลังนำเข้า

3. วิธีที่ 2: Power Query ที่มากับ Excel (ใช้ซ้ำได้)

เมื่อข้อมูลรีเฟรชทุกสัปดาห์ ให้ใช้ Power Query ที่มีใน Excel: ตั้งค่าครั้งเดียวแล้วคลิก "Refresh" ได้ตลอด:

1. "Data" → "Get Data" → "From File" → "From JSON";
2. หลังเลือกไฟล์คุณจะเข้าสู่ Power Query editor ซึ่งเห็นโครงสร้าง List / Record;
3. คลิกไอคอนขยายทางด้านขวาของหัวคอลัมน์เพื่อขยายฟิลด์ที่ซ้อนทีละระดับ;
4. เมื่อชื่อคอลัมน์และชนิดข้อมูลดูถูกต้องแล้ว "Close & Load"

ข้อดีคือการซ้อนขยายเป็นหลายคอลัมน์ได้ทีละชั้น และกำหนดชนิดข้อมูลด้วยมือได้; ราคาที่ต้องจ่ายคือการตั้งค่าครั้งแรกต้องเข้าใจความต่างระหว่าง List กับ Record — List คือ array, Record คือ object และขยายไปคนละทิศทาง

4. วิธีที่ 3: Python + pandas (ฟิลด์เยอะ ต้องทำความสะอาด)

复制import pandas as pd

# Object wrapping an array: first reach the list level
rows = pd.read_json('data.json')['data']

# Expand nested fields into multiple columns
frame = pd.json_normalize(rows, sep='_')

frame.to_excel('out.xlsx', index=False)

พารามิเตอร์สำคัญของ json_normalize คือ sep ซึ่งกำหนดรูปแบบการตั้งชื่อคอลัมน์หลังขยายฟิลด์ที่ซ้อน; user_name ใช้งานใน Excel ง่ายกว่า user.name array ของ array (เช่น tags) ต้องมีขั้นตอน explode เพิ่ม:

复制frame = frame.explode('tags')

ขั้นนี้แยกหนึ่งแถวเป็นหลายแถว เป็นทางเดียวที่จะรักษาความสัมพันธ์แบบหนึ่งต่อหลาย — โดยแลกกับการที่ฟิลด์ที่เหลือซ้ำกัน

5. วิธีที่ 4: สคริปต์ Node (เมื่อต้องเข้าร่วมไปป์ไลน์)

复制import { readFileSync, writeFileSync } from 'node:fs';

const rows = JSON.parse(readFileSync('data.json', 'utf8'));
const headers = [...new Set(rows.flatMap((row) => Object.keys(row)))];
const escapeCell = (value) => `"${String(value ?? '').replaceAll('"', '""')}"`;
const lines = [
  headers.join(','),
  ...rows.map((row) => headers.map((key) => escapeCell(row[key])).join(',')),
];

writeFileSync('out.csv', '\uFEFF' + lines.join('\n'), 'utf8');

สองรายละเอียดที่ห้ามข้าม: เขียน \uFEFF (BOM) ที่ต้นไฟล์เพื่อให้ Excel รู้จัก UTF-8 และ escape เครื่องหมายคำพูดคู่ในเซลล์เป็น "" ไม่อย่างนั้นคอลัมน์จะเลื่อน

6. สามสิ่งที่การแปลงสูญเสียเสมอ

นอกจากนี้ null กับ "ฟิลด์ไม่มีอยู่" ดูเหมือนกันในชีต (ทั้งคู่เป็นเซลล์ว่าง) แต่ต่างความหมายในเชิงธุรกิจ เมื่อต้องแยกให้ชัด ให้แปลง null เป็นสตริง "null" อย่างชัดเจนใน JSON หรือเพิ่มคอลัมน์เครื่องหมาย _has_field

7. ตรวจสอบภายหลัง: อย่าดูแค่แถวแรก

หลังแปลงเสร็จ ให้สุ่มตรวจอย่างน้อยสามอย่าง:

1. จำนวนเรกคอร์ด: จำนวนบรรทัดของ CSV ควรเท่ากับความยาวของ array + 1 (ส่วนหัว); บรรทัดที่หายมักหมายถึงการขึ้นบรรทัดใหม่ท้ายไฟล์ถูกกลืน;
2. คอลัมน์ที่มีอัตรา null สูงสุด: เซลล์ว่างจำนวนมากบ่งชี้ว่าชื่อคีย์ไม่สม่ำเสมอ — กลับไปดูว่า JSON ต้นฉบับใช้สองการสะกด (userId กับ user_id) หรือไม่;
3. แถวที่ยาวที่สุด: array ที่ซ้อนมักตกที่นี่; ยืนยันว่าไม่มีอะไรถูกตัดทอน

เมื่อต้องทำย้อนกลับ (Excel/CSV กลับเป็น JSON) ให้ใช้ CSV เป็น JSON และจำไว้ว่าส่วนหัวถูกถือเป็นชื่อคีย์ — การเปลี่ยนส่วนหัวคือการเปลี่ยนชื่อฟิลด์

คำถามที่พบบ่อย

จะเลือกระหว่างเครื่องมือออนไลน์ Power Query และ pandas อย่างไร

ใช้เครื่องมือออนไลน์กับข้อมูลที่มีโครงสร้างดีตั้งแต่ไม่กี่สิบถึงไม่กี่พันแถวที่แปลงครั้งเดียว ใช้ Power Query เมื่อข้อมูลรีเฟรชทุกสัปดาห์ เพราะตั้งค่าครั้งเดียวและรีเฟรชได้ในคลิกเดียว ใช้ pandas กับ json_normalize เมื่อมีฟิลด์ต้องทำความสะอาดเยอะ และใช้สคริปต์ Node เมื่อการแปลงต้องเข้าร่วมไปป์ไลน์

ทำไมข้อความภาษาจีนถึงกลายเป็นตัวขยะใน CSV

การส่งออกเป็น CSV ไม่ใช่ xlsx และ Excel เดาการเข้ารหัสจาก locale ของระบบ UTF-8 ที่ไม่มี BOM จึงแสดงเป็นตัวขยะ ให้เขียน BOM \uFEFF ที่ต้นไฟล์ หรือเปิดผ่าน "Data → From Text/CSV" และเลือก UTF-8 อย่างชัดเจน

ควรแบน object ที่ซ้อนหรือแยกเป็นแถวเพิ่ม

ให้แบนทีละระดับแทนการวางเป็นสตริง: Power Query ขยายฟิลด์ที่ซ้อนเป็นคอลัมน์ และ json_normalize ที่มี sep ก็ทำเช่นเดียวกันใน pandas array ของ array ต้องมีขั้นตอน explode เพิ่มที่แยกหนึ่งแถวเป็นหลายแถว การส่ง object ลึกเข้า String(obj) ให้เพียง [object Object]