Data Converter

Most data files hold the same thing, a table of rows and columns, written down in different ways. A spreadsheet keeps it in cells with formatting and several sheets. CSV writes each row on one line with a separator between values. JSON and XML describe each row as a set of named fields. SQL turns the rows into statements a database runs. Because the content is the same, converting between them is usually lossless for the values themselves, as long as a few details are set right: the separator, the text encoding, whether the first row holds column names, and how dates are written.

Some things do not travel. Formulas, cell colors, charts, comments and frozen panes belong to spreadsheets and are left behind when the data goes to CSV, JSON or SQL; only the calculated values come across. Excel also has hard limits: Microsoft lists a maximum of 1,048,576 rows and 16,384 columns per worksheet in its specifications and limits. A CSV export from a database can be longer than that, so very large files are better kept as CSV or converted to Parquet, which is built for big tables.

First row is header decides what the first line means. When it is on, the values in that row become JSON keys, XML tag names, SQL column names and the field mapping for vCard. Turn it off when row 1 already holds data, and the columns get generic names instead. Trim spaces removes stray spaces at the start and end of values, a common reason why lookups and imports fail after a copy and paste.

Inputs are not limited to clean data files. HTML pages and Markdown documents with tables, and PDF files made from spreadsheets, are read table by table, which saves copying a report into Excel cell by cell. The PDF needs real text for this; a scanned page is only a picture of a table, and Image to Excel is the tool for reading those. For whole documents rather than tables, such as a Word report or a set of notes, use the Document Converter.

Dates is the setting people most often forget. Spreadsheets display dates in the format of the computer that made them, so the same cell may read 04/05 in one country and 05/04 in another. Leave it on Keep as is to copy dates exactly as they appear, choose ISO for anything a database or a script will read, pick day-month-year or month-day-year to match a local system, or write them as a Unix timestamp for code that counts seconds.

Structured outputs have a few settings of their own. For JSON, JSON structure chooses between an array of objects, where every row repeats the column names as keys, and an array of arrays, which stores only the values and keeps the header as the first entry. Objects are easier to read and use in code; arrays make smaller files. Pretty print indents JSON, XML and YAML so people can read it, and switching it off writes the most compact file. For XML, XML root tag names the outer element, and each row becomes a row element inside it. For SQL, set the SQL table name and the SQL dialect, because quoting and column types differ slightly between MySQL, PostgreSQL, SQLite and SQL Server.

Base64 is different from the other targets: it does not convert a table, it encodes any file, such as an image, a font or a PDF, as plain text. That text can be pasted into JSON, an email or an HTML page where a binary file cannot go. The encoded version is about a third larger than the original, because every three bytes become four characters. Switch on Data URI prefix to add the media type in front, so the result can go straight into an HTML src attribute or a CSS url(). For a PNG, the start of the result looks like this:

Two targets have a narrower audience. vCard files hold one card per contact, with fields for names, phone numbers, emails and addresses; with headers such as Name, Phone and Email in the first row, each column lands in the matching field. Parquet is a columnar format used by data warehouses and analytics tools; it stores large tables compactly and keeps the column types, so it suits exports that are too big for Excel. Both are also accepted as input, which makes it easy to turn an exported address book or a Parquet file back into a spreadsheet you can read. The other document tools cover what comes after, such as turning a finished sheet into a PDF.