Tables and trees: why some conversions lose something
These formats come in two families, and moving between the families is where information can be lost.
- Tables: CSV, TSV and Excel are rows and columns. CSV and TSV store every value as text, with no types. Excel stores numbers, true and false and dates as real values, and can hold several sheets.
- Trees: JSON, YAML and XML can nest values inside values. JSON and YAML also have numbers, true and false, and null. XML has only text, plus attributes.
Going from a tree to a table means deciding what a row is and how to spread nested values into columns. Going from a table to a tree means deciding which text cells are numbers. This converter makes those decisions in the open, lets you change them, and lists what it did in the notes under the result.
What happens to nested data and lists
Nested objects are spread into columns named by their path. {"name": "Ana", "address": {"city": "Lisbon"}, "tags": ["a", "b"]} becomes the columns name, address.city, tags.0 and tags.1. The columns are the union of the keys of all records, so a record that lacks a key gets an empty cell.
Lists can become separate columns, one joined cell such as a;b, or JSON text in one cell. Turn flattening off to keep every nested value as JSON text.
Going back, columns with dots are rebuilt into nested objects and numbered columns into a list, so JSON to CSV and back gives you the same structure. A missing value and an empty cell look the same in a table, so they come back as null unless you choose to leave empty cells out.
If your JSON is an API response such as {"status": "ok", "data": {"items": [...]}}, the converter finds the list of records, uses it as the rows and names the keys it left out. You can also type the path yourself under More options.
XML and YAML
XML has attributes, repeated elements and exactly one root element, none of which JSON has. Attributes become keys that start with @, such as @id. Repeated elements become a list, but a single element stays a plain value, because XML cannot tell a list with one item from a value. When XML becomes a table, an element that appears once in one row and twice in another is written as a list in every row, so the columns line up. Going to XML, a list at the top becomes repeated item elements inside a root element, and you choose both names. Names that are not valid in XML, such as one with a space, are repaired with an underscore.
YAML anchors and aliases are resolved into copies and merge keys (<<) are applied, so the result has no references. Comments are dropped. Words like yes, no and on stay text, as the current YAML 1.2 specification says, and a file with several documents separated by --- becomes a list. When writing YAML, text that an older YAML 1.1 reader would take for something else, such as the country code no or the date 2001-12-14, is put in quotes.
Numbers, true and false, and empty cells
CSV has no types, so you choose how to read the text. With detection on, a column becomes numbers only when every filled cell in it is a clean number: digits with an optional minus sign and decimal point, no leading zeros and at most 15 digits. One stray word keeps the whole column as text, so a column never mixes numbers and words. A column of true and false becomes booleans the same way, and with all types detected, empty cells and the exact words null and NULL become null (a note says how many words were changed). Detection only applies when the result is JSON, YAML or Excel: CSV, TSV and XML hold nothing but text, so converting between them leaves every value exactly as written.
Values such as 007, ZIP codes with a leading zero and long IDs stay text on purpose, because turning them into numbers would change them. Numbers with thousands separators or decimal commas are not converted either, since the same text means different things in different countries. Dates are left alone for the same reason: 03/04/2024 is the 3rd of April in some countries and the 4th of March in others, so a date in a CSV stays text, also when the result is an Excel file.