JSON sang Excel: bốn cách tiếp cận và chỗ mỗi cách mất độ trung thực

Kết luận: một mảng object phẳng hoặc một object bọc mảng chuyển nhanh nhất qua công cụ trực tuyến; dùng Power Query khi dữ liệu làm mới hàng tuần, pandas khi có nhiều trường cần làm sạch, và script Node chỉ khi chuyển đổi buộc phải tham gia pipeline — nhưng hãy quyết định "một dòng" nghĩa là gì trước khi chạm vào dữ liệu lồng nhau hoặc khóa không đồng nhất.

Bản thân việc chuyển đổi không khó; khó ở chỗ sau đó mới phát hiện rằng số đã thành văn bản và vài cấp lồng đã sụp thành một. Đọc cấu trúc dữ liệu trước khi chọn cách làm sẽ giúp bạn tránh được một lần làm lại.

1. Trước tiên, xác định bạn đang có loại JSON nào

Mở tệp và nhìn cấp ngoài cùng:

Hai loại đầu chuyển trực tiếp với tỷ lệ thành công gần 100%; với ba loại sau, hãy quyết định "một dòng" nghĩa là gì trước, nếu không bảng kết quả sẽ không dùng được.

2. Cách 1: công cụ trực tuyến (1 phút)

Mở JSON sang bảng, dán dữ liệu → chọn dấu phân cách và header → xuất CSV → mở trong Excel. Phù hợp với dữ liệu có cấu trúc tốt từ vài chục đến vài nghìn dòng.

Ba điều bạn buộc phải chú ý:

• Bạn nhận được CSV, không phải xlsx. Khi Excel mở CSV, nó đoán mã hoá từ locale hệ thống, nên văn bản tiếng Trung có thể biến thành mojibake. Dùng "Data → From Text/CSV" và chỉ định rõ UTF-8 sẽ đáng tin hơn.
• Định danh bắt đầu bằng 0 sẽ mất số 0. "007" là văn bản trong CSV, nhưng Excel tự chuyển nó thành số 7. Hãy đặt cột đó là text khi import, hoặc thêm tiền tố cho nó trong JSON.
• Số dài trở thành ký hiệu khoa học. CMND và mã đơn hàng dài hơn 15 chữ số mất độ chính xác; những trường như vậy vốn đã phải là chuỗi trong JSON, và phải giữ định dạng text sau khi import.

3. Cách 2: Power Query tích hợp trong Excel (dùng lại được)

Khi dữ liệu được làm mới mỗi tuần, hãy dùng Power Query có sẵn trong Excel: cấu hình một lần, sau đó chỉ cần bấm "Refresh":

1. "Data" → "Get Data" → "From File" → "From JSON";
2. Sau khi chọn tệp, bạn vào trình soạn Power Query, nơi bạn thấy các cấu trúc List / Record;
3. Bấm biểu tượng mở rộng ở bên phải tiêu đề cột để mở rộng trường lồng từng cấp;
4. Khi tên cột và kiểu đã đúng, chọn "Close & Load".

Ưu điểm là có thể mở rộng lồng nhau thành nhiều cột theo từng lớp, và gán kiểu thủ công; cái giá là lần cấu hình đầu cần hiểu khác biệt giữa List và Record — List là mảng, Record là object, và chúng mở rộng theo các hướng khác nhau.

4. Cách 3: Python + pandas (nhiều trường, cần làm sạch)

复制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)

Tham số then chốt của json_normalize là sep, quyết định kiểu đặt tên cột sau khi mở rộng trường lồng; user_name dễ dùng trong Excel hơn user.name. Mảng của mảng (như tags) cần thêm một bước explode:

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

Bước này tách một dòng thành nhiều dòng, cách duy nhất để giữ quan hệ một-nhiều — đổi lại các trường còn lại bị trùng lặp.

5. Cách 4: script Node (khi buộc phải tham gia pipeline)

复制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');

Hai chi tiết bạn không nên bỏ qua: ghi \uFEFF (một BOM) ở đầu để Excel nhận ra UTF-8, và escape dấu ngoặc kép bên trong ô thành "", nếu không các cột sẽ bị lệch.

6. Ba thứ chuyển đổi luôn làm mất

Ngoài ra, null và "trường không tồn tại" trông giống nhau trong bảng (đều là ô rỗng) nhưng mang nghĩa khác nhau trong nghiệp vụ. Khi cần phân biệt chúng, hãy chuyển null thành chuỗi "null" một cách tường minh trong JSON, hoặc thêm cột đánh dấu _has_field.

7. Kiểm tra sau khi chuyển: đừng chỉ nhìn dòng đầu

Sau khi chuyển đổi, hãy kiểm tra ít nhất ba thứ:

1. Số bản ghi: số dòng CSV phải bằng độ dài mảng + 1 (header); thiếu một dòng thường có nghĩa dòng xuống cuối bị nuốt;
2. Cột có tỷ lệ null cao nhất: nhiều ô rỗng cho thấy tên khóa không đồng nhất — quay lại xem JSON gốc có dùng hai cách viết (userId và user_id) không;
3. Dòng dài nhất: mảng lồng thường rơi vào đó; xác nhận không có gì bị cắt.

Khi cần thao tác ngược (Excel/CSV về JSON), dùng CSV sang JSON, và nhớ rằng header được coi là tên khóa — thay đổi header là thay đổi tên trường.

FAQ

Làm sao chọn giữa công cụ trực tuyến, Power Query và pandas?

Dùng công cụ trực tuyến cho dữ liệu có cấu trúc tốt từ vài chục đến vài nghìn dòng mà bạn chỉ chuyển một lần. Dùng Power Query khi dữ liệu làm mới hàng tuần, vì nó được cấu hình một lần và làm mới bằng một cú bấm. Dùng pandas với json_normalize khi có nhiều trường cần làm sạch, và script Node khi chuyển đổi buộc phải tham gia pipeline.

Vì sao văn bản tiếng Trung lại thành mojibake trong CSV?

Bản xuất là CSV, không phải xlsx, và Excel đoán mã hoá từ locale hệ thống, nên UTF-8 không có BOM sẽ hiển thị mojibake. Hãy ghi BOM \uFEFF ở đầu tệp, hoặc mở qua "Data → From Text/CSV" và chọn UTF-8 một cách tường minh.

Nên làm phẳng object lồng nhau hay tách thành thêm dòng?

Hãy làm phẳng từng cấp thay vì dán chúng thành chuỗi: Power Query mở rộng trường lồng thành cột, và json_normalize với sep làm điều tương tự trong pandas. Mảng của mảng cần thêm bước explode tách một dòng thành nhiều dòng. Đưa một object sâu vào String(obj) chỉ cho ra [object Object].