CSV transpose JSON guidelines: Header, separator, quotation marks and data type processing

Hide rules for CSV

Separator does not always have commas

This tool supports commas ,, semicolon ;Tab and vertical lines |。 Excel in different regions may export semicolons because of decimal commas; Copying web tables often brings Tab. The whole file can only select one main separator, and mixing causes column drift.

Whether the quotation commas are content

In the following line, there are three fields, not four:

id,name,note
1001,Zhang San,"Delay 2 hours，AlreadyPatchLet's go."

Comma appears in double quotes and is note content. A line change can also appear in the fields of the quotation-marked parcel. Manual split(',') The analysis often destroys it, and the line-by-line resolution of this tool will address the usual quote rules, but extreme dirty data will still require professional CSV library review.

Table header is data or field name

When there's a watch, first line id,name (a) become object keys; When there is no header, the converter needs to generate or use default listings. If you misdirect the first real record, the first data will be lost and the keys will be mistook for each object in the back.

JSON structural costs

Standard JSON object keys must be double-quoted and string-based; array []object {}。 All cells in CSV are essentially text, and the common policy when converted to JSON is that numbers look like numbers and values are converted into empty strings or empty fields. null, whether the boolean value is converted to true/false It depends on conversions and agreements. Do not consider the inference of converters as a definition of business type.

CSV cannot be naturalized:

• (a) An array of commodities under an order;
• (a) The national, provincial and municipal levels of the client ' s address;
• The same field is a digital and united text;
• Type, constraint, enumeration and time zone outside the original column order.

If there are embedded objects in JSON, the CSV can only be evened (e.g., address.city) or sequence into a JSON string. Turn back and then turn back to JSON, and the structure may be different from the original object.

Thousands of cells

1. Open CSV and JSON converter, first judge whether the left input box is CSV or JSON.
2. Paste a sample of a small, real structure, and do not process the whole million line of data. Validates the number of columns, quotes and codes first.
3. Select a comma, semicolon, Tab or vertical line under the separator. Copying from Excel usually starts with Tab; See if the preview column is up to expectations.
4. Check or delete "include the header" under the first line. When there is a watch, the key is from the first line; When you have no header, confirm whether the page-generated listing strategy is in line with your interface.
5. Check "Beautiful JSON" when reading JSON; Machine transmissions can be shut down to reduce volume. beautification changes only the blanks and does not change the semantics of the data.
6. Click on " CSV → JSON " to check the key, value and number of rows for each object; Don't just watch the first line.
7. A record containing comma, quotations, blank fields and a lead zero is used for border testing, and the results are then copied or downloaded.
8. When you reverse the conversion, insert JSON in the JSON area, click "JSON → CSV" to check how embedded objects, arrays and line breaks are evened.
9. Hand the results over to JSON format or verify tool for a syntax check and then enter the interface connection.

Pages are locally executed in browsers and are suitable for small and medium text. Large files are restricted by browser memory and text frame rendering, and above these limits should be replaced by command lines, scripts or data processing platforms.

Real input output: Keep the lead zero

Input CSV：

sku,qty,price,note
00123,2,9.90,"促销，I'm sorry.Two.Annex半价"
00007,,12.5,""

With a watch and a comma, the ideal JSON structure could be:

[
  {"sku":"00123","qty":2,"price":9.9,"note":"促销，I'm sorry.Two.Annex半价"},
  {"sku":"00007","qty":"","price":12.5,"note":""}
]

Here. sku You should keep the string, otherwise 00123 It's gonna be... 123The inventory system cannot match the original number. Amount 9.90 Show as when it becomes a value 9.9, if the financial requirement requires two decimals, the fixed amount or string should be used at the business level instead of relying on the JSON presentation format.

Type and empty: The conversion must be manually confirmed

It is suggested that the data dictionary should include "list, must be filled, string/number/bul, empty meaning, examples". The converter is responsible for the grammar layer and the data dictionary for the business layer.

Error table: Why is the result wrong?

When there is an inconsistency in the number of lines, the log number of the original CSV is measured, and the length of the JSON array is measured. Records with quotation marks cannot be simply counted by physical line numbers.

Real scene: From Excel to interface to report

Goods imported. Excel export Tab-separated content, SKU, bar code and post code must keep a string. Check the table header, select Tab, switch and check the lead zero, then confirm that each object has the required key with the JSON checker.

Log analysis. Log fields often contain commas and double quotations, and select comma when exporting and correctly transphrase. Converts to JSON to filter by object, and only export necessary fields after analysis, avoiding bringing tokens and original requests into the report.

Interface regression test. Saves dozens of arrays of parameters with CSV, which is converted to the JSON array driver test. The scripts should be clearly identified. "001" When the string, put 1 When the number is; No automatic inference can be relied upon in different operating environments.

Financial reconciliation. JSON's order contains a detailed array, and when returning to CSV, the commodity array is equal to multiple lines and the order number is maintained. If an order is imposed in one line, the array is crushed into a string, and subsequent screening and request for reconciliation are unreliable.

Vendor exchange. The other side requested a semicolon and UTF-8 BOM. Converter can validate field structure, but file encoding, BOM and line break style are explicitly handled in the final export script.

Security and privacy borders

• The pages are converted locally in the browser, which does not mean that the data are not traceable on the computer; Clipboards, browser caches, screen sharing and download directories may still be leaked.
• Production orders, customer mobile phone numbers, access badges should not be posted to public demonstrations or share links. De-sensitization first, and then empty the input box.
• CSV Lee =, +, -, @ The initial cell may trigger a formula in a spreadsheet to be exported to an other person for CSV injection protection.
• The converter does not execute scripts in JSON, but the downstream system may use fields as HTML or SQL, and must continue to perform output encoding and parameterize queries.
• Big documents and sensitive data should be processed in a controlled environment using audited scripts and retained for the Hashi and output versions.

Selection of tools

FAQ