JSON به Excel: چهار رویکرد و جای از دست رفتن اصالت هر یک

جمع‌بندی: آرایه تخت از objectها یا objectی که آرایه را می‌پیچد سریع‌ترین مسیر را از طریق ابزار آنلاین طی می‌کنند؛ وقتی داده هفتگی تازه می‌شود از Power Query، وقتی فیلدهای زیادی برای پاک‌سازی هست از pandas و فقط وقتی تبدیل باید به یک خط لوله بپیوندد از اسکریپت Node استفاده کنید — اما پیش از دست زدن به داده تودرتو یا کلیدهای ناهمگون مشخص کنید «یک سطر» یعنی چه.

خود تبدیل دشوار نیست؛ دشوار این است که بعداً کشف کنید عددها متن شده‌اند و چند سطح تودرتویی در یکی فروپاشیده‌اند. پیش از انتخاب روش ساختار داده را بخوانید تا از دوباره‌کاری نجات یابید.

۱. نخست تشخیص دهید چه نوع JSON دارید

فایل را باز کنید و به بیرونی‌ترین سطح نگاه کنید:

دو مورد نخست با نرخ موفقیت نزدیک به ۱۰۰٪ مستقیم تبدیل می‌شوند؛ برای سه مورد آخر نخست مشخص کنید «یک سطر» یعنی چه، وگرنه شیت حاصل قابل استفاده نیست.

۲. رویکرد ۱: ابزار آنلاین (۱ دقیقه)

JSON به جدول را باز کنید، داده را بچسبانید → جداکننده و سرستون را انتخاب کنید → CSV را صادر کنید → در Excel باز کنید. مناسب چند ده تا چند هزار سطر داده خوش‌ساختار.

سه چیز که باید مراقبشان باشید:

• شما CSV می‌گیرید، نه xlsx. وقتی Excel یک CSV باز می‌کند رمزگذاری را از locale سیستم حدس می‌زند، پس متن چینی ممکن است mojibake شود. استفاده از «Data → From Text/CSV» و انتخاب صریح UTF-8 مطمئن‌تر است.
• شناسه‌هایی که با 0 شروع می‌شوند صفرهایشان را از دست می‌دهند. "007" در CSV متن است، اما Excel آن را خودکار به عدد ۷ تبدیل می‌کند. آن ستون را هنگام ورود متن تنظیم کنید یا در JSON پیشوندی به آن بیفزایید.
• عددهای بلند به نماد علمی تبدیل می‌شوند. شناسه‌های ملی و شماره‌های سفارش بلندتر از ۱۵ رقم دقت را از دست می‌دهند؛ چنین فیلدهایی باید از پیش در JSON رشته باشند و پس از ورود هم در قالب متن بمانند.

۳. رویکرد ۲: Power Query داخلی Excel (قابل استفاده مجدد)

وقتی داده هر هفته تازه می‌شود، از Power Query داخلی Excel استفاده کنید: یک بار پیکربندی کنید، سپس فقط «Refresh» را کلیک کنید:

۱. «Data» → «Get Data» → «From File» → «From JSON»؛ ۲. پس از انتخاب فایل به ویرایشگر Power Query می‌رسید و ساختارهای List / Record را می‌بینید؛ ۳. برای گسترش فیلدهای تودرتو سطح‌به‌سطح، آیکون گسترش در سمت راست سرستون را کلیک کنید؛ ۴. وقتی نام ستون‌ها و نوع‌ها درست به نظر رسیدند، «Close & Load».

مزیتش این است که تودرتویی لایه‌به‌لایه به چند ستون گسترش می‌یابد و نوع‌ها را می‌توان دستی تعیین کرد؛ هزینه‌اش این است که پیکربندی اولیه نیاز دارد تفاوت List و Record را بفهمید — List آرایه است، Record object است و در جهت‌های متفاوت گسترش می‌یابند.

۴. رویکرد ۳: 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 راحت‌تر است. آرایه‌های آرایه (مانند tags) به یک مرحله explode اضافه نیاز دارند:

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

این مرحله یک سطر را به چند سطر می‌شکند، تنها راه حفظ رابطه یک‌به‌چند — به قیمت تکرار فیلدهای باقی‌مانده.

۵. رویکرد ۴: اسکریپت 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 یوتی‌اف‌هشت را بشناسد، و گیومه‌های دوتایی درون سلول‌ها را به‌صورت "" escape کنید، وگرنه ستون‌ها جابه‌جا می‌شوند.

۶. سه چیزی که تبدیل همیشه از دست می‌دهد

همچنین null و «فیلد غایب» در شیت یکسان به نظر می‌رسند (هر دو سلول خالی‌اند) اما در حوزه کسب‌وکار معنای متفاوتی دارند. وقتی باید آن‌ها را از هم تشخیص دهید، در JSON صریحاً null را به رشته "null" تبدیل کنید یا ستون نشانگر _has_field بیفزایید.

۷. بررسی پس از تبدیل: فقط به سطر اول نگاه نکنید

پس از تبدیل حداقل سه چیز را نمونه‌برداری کنید:

۱. تعداد رکورد: تعداد خطوط CSV باید برابر طول آرایه + ۱ (سرستون) باشد؛ یک خط جاافتاده معمولاً یعنی خط جدید انتهایی بلعیده شده است؛ ۲. ستون با بالاترین نرخ null: سلول‌های خالی زیاد نشانه نام‌های کلید ناهمگون است — برگردید و ببینید JSON اصلی دو املای متفاوت دارد (userId و user_id)؛ ۳. بلندترین سطر: آرایه‌های تودرتو معمولاً همان‌جا فرود می‌آیند؛ تأیید کنید چیزی بریده نشده است.

وقتی به عمل معکوس نیاز دارید (Excel/CSV به JSON) از CSV به JSON استفاده کنید و یادتان باشد سرستون به‌عنوان نام کلیدها رفتار می‌شود — تغییر سرستون یعنی تغییر نام فیلدها.

پرسش‌های متداول

چگونه میان ابزار آنلاین، Power Query و pandas انتخاب کنم؟

برای چند ده تا چند هزار سطر داده خوش‌ساختار که یک بار تبدیل می‌کنید از ابزار آنلاین استفاده کنید. وقتی داده هر هفته تازه می‌شود از Power Query استفاده کنید، چون یک بار پیکربندی و با یک کلیک تازه می‌شود. وقتی فیلدهای زیادی برای پاک‌سازی هست از pandas با json_normalize و وقتی تبدیل باید به خط لوله بپیوندد از اسکریپت Node استفاده کنید.

چرا متن چینی در CSV به mojibake تبدیل می‌شود؟

خروجی CSV است نه xlsx و Excel رمزگذاری را از locale سیستم حدس می‌زند، بنابراین یوتی‌اف‌هشت بدون BOM به‌صورت mojibake نمایش داده می‌شود. در ابتدای فایل BOM (\uFEFF) بنویسید یا آن را از طریق «Data → From Text/CSV» باز کنید و صریحاً UTF-8 انتخاب کنید.

objectهای تودرتو باید تخت شوند یا به سطرهای اضافه شکسته شوند؟

آن‌ها را سطح‌به‌سطح تخت کنید نه اینکه به‌صورت رشته بچسبانید: Power Query فیلدهای تودرتو را به ستون گسترش می‌دهد و json_normalize با sep همین کار را در pandas می‌کند. آرایه‌های آرایه به مرحله explode اضافه نیاز دارند که یک سطر را به چند سطر می‌شکند. سپردن یک object عمیق به String(obj) فقط [object Object] می‌دهد.