Trình Chuyển Đổi JSON Sang CSV

Tại sao chuyển đổi JSON sang CSV là một quy trình rất phổ biến

JSON là định dạng payload chiếm ưu thế cho các API web hiện đại; CSV là định dạng mẫu số chung thấp nhất cho dữ liệu dạng bảng. Excel, Google Sheets, SQL LOAD DATA, R, Pandas, Tableau, Power BI và hầu như mọi đường ống ETL đều có thể nhập CSV ngay từ đầu. Vì vậy khi đầu ra API cần xuất hiện trong bảng tính cho một bên liên quan, hoặc trong cơ sở dữ liệu để phân tích, ai đó phải làm phẳng JSON thành các hàng. Công cụ này thực hiện việc làm phẳng đó trong trình duyệt của bạn, không có chuyến đi vòng máy chủ và không tải lên dữ liệu có khả năng nhạy cảm.

Định dạng CSV (RFC 4180)

Không có tiêu chuẩn CSV chính thức duy nhất nào, nhưng RFC 4180 (tháng 10 năm 2005, "Định dạng chung và Loại MIME cho các Tệp Giá trị được phân tách bằng dấu phẩy (CSV)") là điều gần nhất ngành công nghiệp có với một tham chiếu chính tắc. Các quy tắc mọi người đồng ý:

• Một bản ghi mỗi dòng, được phân tách bằng các dấu ngắt dòng. RFC chỉ định CRLF; hầu hết các trình phân tích cú pháp hiện đại cũng chấp nhận LF.
• Các trường trong một bản ghi được phân tách bởi một ký tự phân tách duy nhất, dấu phẩy trong dạng chính tắc, nhưng dấu chấm phẩy, tab hoặc thanh dọc phổ biến trong các biến thể vùng và kế thừa.
• Mỗi bản ghi phải có cùng số trường. Các giá trị bị thiếu trở thành ô trống; các dấu phân tách thừa là lỗi.
• Hàng tiêu đề là tùy chọn; nếu có, đó là dòng đầu tiên.
• Các trường chứa dấu phẩy, dấu ngắt dòng hoặc dấu nháy kép phải được bao quanh bằng dấu nháy kép.
• Dấu nháy kép được nhúng bên trong một trường được trích dẫn được thoát bằng cách nhân đôi: "She said ""hello""".
• Loại MIME được đăng ký cho CSV là text/csv.

Tại sao bốn dấu phân tách thay vì một

Mặc định là dấu phẩy, nhưng mỗi dấu phân tách giải quyết một vấn đề cụ thể:

• Dấu phẩy (,), tiêu chuẩn ở Mỹ, Anh và các vùng nói tiếng Anh khác. Excel đọc CSV với dấu phẩy khi cài đặt vùng OS là tiếng Anh. Mặc định cho application/csv trong hệ sinh thái công cụ rộng hơn.
• Dấu chấm phẩy (;), mặc định trên thực tế ở châu Âu lục địa, Mỹ Latinh và các vùng khác nơi dấu phẩy là dấu phân tách thập phân. Excel sử dụng dấu phân tách danh sách của OS, vì vậy Excel với cài đặt vùng Pháp hoặc Đức tạo ra CSV được phân tách bằng dấu chấm phẩy.
• Tab, TSV (giá trị được phân tách bằng tab) phổ biến trong dữ liệu khoa học, tin sinh học và bất kỳ bối cảnh nào mà dữ liệu chứa dấu phẩy (các trường văn bản tự do, địa chỉ).
• Thanh dọc (|), được ưa chuộng trong một số hệ thống doanh nghiệp kế thừa nơi dữ liệu có thể chứa dấu phẩy, dấu chấm phẩy VÀ tab. Hiếm khi là lựa chọn đầu tiên đúng đắn nhưng hữu ích khi không có gì khác hoạt động.

Làm phẳng JSON lồng nhau: ba chiến lược

JSON có cấu trúc phân cấp; CSV phẳng. Không có ánh xạ không mất mát đa năng giữa hai loại. Khi đầu vào chứa các đối tượng hoặc mảng lồng nhau, mỗi bộ chuyển đổi phải đưa ra lựa chọn:

1. Nội tuyến dưới dạng chuỗi JSON. Phần lồng nhau được tuần tự hóa lại thành JSON và đưa vào một ô CSV duy nhất. Cách tiếp cận mặc định của công cụ này. Không mất mát; an toàn cho chuyến đi vòng; người tiêu dùng cần phân tích cú pháp JSON bên trong nếu họ muốn cấu trúc.
2. Làm phẳng khóa-có-dấu-chấm. {address: {street, city}} trở thành các cột address.street và address.city. json_normalize của Pandas hoạt động theo cách này. Sạch hơn cho các nhà phân tích nhưng số lượng cột bùng nổ đối với dữ liệu lồng sâu.
3. Phá hàng. Một mảng trở thành nhiều hàng, với các trường cha được lặp lại. {name: "Alice", tags: ["a", "b"]} trở thành hai hàng, cả hai đều có name=Alice. Tốt nhất cho mối quan hệ một-nhiều; mất mô hình tinh thần "một bản ghi mỗi hàng".

Nếu bạn cần đầu ra khóa-có-dấu-chấm hoặc đầu ra phá hàng, hãy tiền xử lý JSON trước khi dán nó vào, hoặc thông qua một tập lệnh Python / Node nhỏ hoặc json_normalize của Pandas.

Tiêu đề, chuyến đi vòng kiểu và bẫy tự động định dạng của Excel

JSON có một hệ thống kiểu thực sự (số, chuỗi, boolean, null, mảng, đối tượng); CSV chỉ có chuỗi. Quy ước là người tiêu dùng suy ra các kiểu từ ngữ cảnh. Công cụ tạo các hàng tiêu đề từ hợp nhất của tất cả các khóa trên tất cả các đối tượng đầu vào, để các trường bị thiếu là các ô trống, viết boolean là true/false và viết null JSON là một ô trống.

Excel hữu ích theo những cách đôi khi gây hại:

• Loại bỏ số 0 đầu tiên. Một cột mã bưu chính như 01234 mở dưới dạng số 1234. Cách khắc phục: thêm dấu nháy đơn ('01234) bên trong CSV, hoặc sử dụng trình hướng dẫn nhập của Excel và đặt rõ ràng loại cột thành Văn bản.
• Chuyển đổi ngày tự động. Các chuỗi trông giống ngày tháng (3/4/5, Mar-23) được chuyển đổi âm thầm. Cùng các cách khắc phục.
• Ký hiệu khoa học. ID số dài (số thẻ tín dụng, số nguyên lớn) được chuyển đổi thành 1.23E+15 và mất độ chính xác. JSON có thể giữ chính xác các số đến 2⁵³−1; đối với bất cứ thứ gì lớn hơn, hãy truyền giá trị dưới dạng chuỗi trong JSON.
• Không khớp BOM UTF-8. Excel trên Windows mặc định là Windows-1252; mở một CSV UTF-8 mà không có BOM làm hỏng các ký tự không phải ASCII (é, ü, å trở thành mojibake). Thêm BOM UTF-8 () ở đầu tệp sửa chữa điều này. Google Sheets xử lý UTF-8 mà không có BOM một cách chính xác, vì vậy BOM là cụ thể của nền tảng.

Tiêm CSV, một lưu ý bảo mật đáng biết

Nếu một CSV sẽ được mở trong Excel hoặc Google Sheets, một ô bắt đầu bằng =, +, - hoặc @ được hiểu là một công thức. Trang Tiêm CSV của OWASP ghi lại lớp tấn công, một chuỗi độc hại như =cmd|' /c calc'!A0 trong một trường do người dùng gửi có thể thực thi các lệnh khi quản trị viên mở xuất. Thực hành phòng thủ: thêm tiền tố cho bất kỳ ô nào bắt đầu bằng một trong bốn ký tự đó với một dấu nháy đơn, hoặc bao bọc trong dấu ngoặc kép cộng với một dấu nháy đầu. Quan trọng để biết nếu bạn đang xuất nội dung do người dùng tạo.

Các trường hợp biên phổ biến mà bộ chuyển đổi xử lý

• Trường chứa dấu phân tách, được trích dẫn tự động. San Francisco, CA trong CSV-dấu-phẩy trở thành "San Francisco, CA".
• Trường chứa một dòng mới theo nghĩa đen, được trích dẫn; nội dung nhiều dòng là hợp pháp bên trong dấu ngoặc kép theo RFC 4180.
• Trường chứa một dấu nháy kép, được trích dẫn, với dấu ngoặc kép bên trong được nhân đôi: She said "yes" trở thành "She said ""yes""".
• Mảng và đối tượng trống, được tuần tự hóa lần lượt là [] và {} trong các ô của chúng.
• Các bản ghi có khóa khác nhau, hợp nhất của tất cả các khóa tạo thành hàng tiêu đề; các trường bị thiếu là các ô trống.
• Đối tượng đơn so với mảng, một đối tượng JSON đơn lẻ trở thành một CSV một-hàng.

Khi nào sử dụng công cụ này

• Xuất phản hồi API cho các bên liên quan muốn có một bảng tính mà họ có thể sắp xếp và lọc.
• Nhập hàng loạt dữ liệu JSON vào cơ sở dữ liệu SQL thông qua LOAD DATA INFILE (MySQL) hoặc COPY (PostgreSQL). Cả hai đều mong đợi đầu vào có hình dạng CSV.
• Phân tích dữ liệu nhanh trong Excel, Google Sheets, Pandas, R, Tableau hoặc Power BI.
• Tạo danh sách thư từ một phản hồi API JSON của các liên hệ.
• Di chuyển dữ liệu giữa các hệ thống không chia sẻ API nhưng cả hai đều nói CSV.
• Xem xét một tệp JSON bằng mắt khi nó quá lặp đi lặp lại để đọc trực tiếp, chế độ xem bảng tính thường dễ dàng hơn.

Quyền riêng tư

Các xuất CSV hầu như luôn chứa PII, hồ sơ khách hàng, dữ liệu nhân viên, nhật ký giao dịch, danh sách liên hệ. Chuyển đổi phía máy chủ gửi mỗi bản ghi qua cơ sở hạ tầng của bên thứ ba, nơi nó tồn tại trong nhật ký vận chuyển, nhật ký máy chủ và có thể là các phản hồi được lưu trong bộ nhớ cache. Chuyển đổi dựa trên trình duyệt giữ dữ liệu trên máy của bạn. Toàn bộ bước làm phẳng-và-trích-dẫn ở đây xảy ra trong JavaScript bên trong trình duyệt của bạn; không có gì được tải lên.

Những sai lầm phổ biến

1. Dấu phân tách sai cho khán giả. Gửi một CSV-dấu-phẩy cho người dùng Excel Pháp tạo ra một tệp một cột. Khớp dấu phân tách với cài đặt vùng của người nhận.
2. Dán một đối tượng đơn lẻ thay vì một mảng. Một đối tượng đơn chuyển đổi thành một CSV một hàng với các tiêu đề cột từ các khóa. Đối với nhiều bản ghi, hãy bọc chúng trong một mảng.
3. Mong đợi làm phẳng kiểu Pandas. Các đối tượng lồng nhau trong công cụ này được tuần tự hóa thành các chuỗi JSON bên trong các ô. Nếu bạn cần các cột address.street, hãy làm phẳng trước.
4. Sử dụng CSV cho các số rất lớn. JSON đại diện an toàn cho các số nguyên đến 2⁵³−1 (khoảng 9 quadrillion). Vượt qua đó, truyền dưới dạng chuỗi. Excel sẽ phá hỏng các ID số dài ngay cả khi CSV bảo tồn chúng.
5. Mở trong Excel mà không suy nghĩ về mã hóa. Các ký tự không phải ASCII cần BOM UTF-8 cho Excel trên Windows; Google Sheets và Excel hiện đại cho web không cần nó.
6. Xuất văn bản do người dùng tạo mà không làm sạch các tiền tố công thức. Các ô bắt đầu bằng =, +, - hoặc @ là các công thức trong Excel và Sheets. Tiêm CSV là một cuộc tấn công thực sự đối với các quản trị viên mở các tệp được xuất.

Câu hỏi thường gặp

JSON của tôi có các đối tượng lồng nhau. Chúng được xử lý như thế nào?

Mỗi đối tượng hoặc mảng lồng nhau được tuần tự hóa thành một chuỗi JSON bên trong ô CSV của nó. Vì vậy {user: {name: "Alice", role: "admin"}} trở thành một cột được gọi là user chứa {"name":"Alice","role":"admin"}. Điều này đi vòng không mất mát. Nếu bạn cần các cột user.name và user.role riêng biệt, hãy tiền xử lý với json_normalize của Pandas hoặc một tập lệnh làm phẳng nhỏ.

Tại sao các ký tự không phải tiếng Anh của tôi trông bị hỏng trong Excel?

Excel trên Windows mở CSV trong Windows-1252 theo mặc định. CSV UTF-8 không có Dấu Thứ tự Byte trông giống như mojibake (é → é). Cách khắc phục: lưu tệp với BOM UTF-8, sử dụng trình hướng dẫn nhập Dữ liệu → Từ Văn bản của Excel và chọn UTF-8 một cách rõ ràng, hoặc mở tệp trong Google Sheets, xử lý UTF-8 mà không có BOM một cách chính xác.

Công cụ có xử lý các dấu phẩy bên trong các trường không?

Có. Theo RFC 4180, các trường chứa dấu phân tách, dấu ngắt dòng hoặc dấu nháy kép được bao quanh tự động bằng dấu nháy kép. Các dấu nháy kép được nhúng được thoát bằng cách nhân đôi chúng. Vì vậy San Francisco, CA trở thành "San Francisco, CA" và She said "hi" trở thành "She said ""hi""".

JSON của tôi có được gửi đến máy chủ không?

Không. Chuyển đổi chạy hoàn toàn trong trình duyệt của bạn. Nội dung textarea không được truyền, ghi nhật ký hoặc lưu trữ ở bất cứ đâu. Điều này quan trọng vì các bản ghi được xuất thường chứa PII, dữ liệu khách hàng, thông tin nhân viên, lịch sử giao dịch, mà bạn không muốn chảy qua cơ sở hạ tầng của người khác.

Tôi có thể nhận được đầu ra được phân tách bằng tab (TSV) không?

Có, chọn "Tab" trong menu thả xuống dấu phân tách. TSV là lựa chọn đúng đắn khi dữ liệu của bạn chứa dấu phẩy (địa chỉ, các trường văn bản tự do, dữ liệu tài chính với dấu phân tách hàng nghìn) và bạn không muốn bọc mọi ô khác trong dấu ngoặc kép. Nhiều định dạng dữ liệu tin sinh học và khoa học là TSV theo mặc định.

Giới hạn kích thước là gì?

Bất cứ điều gì trình duyệt của bạn có thể chứa. Không có giới hạn phía máy chủ vì không có máy chủ liên quan. Hàng chục megabyte JSON chuyển đổi trong một hoặc hai giây trên thiết bị hiện đại; payload trăm megabyte bắt đầu chậm lại. Đối với các bộ dữ liệu rất lớn, hãy chia chúng thành lô hoặc chuyển sang một bộ chuyển đổi truyền phát ở dòng lệnh.

Công cụ liên quan