Excel to JSON with the types intact

Each cell of the chosen header row becomes the key for its column, trimmed of surrounding whitespace. An empty header cell falls back to the column letter, so a stray gap gives you "D" rather than an empty key, and a header that repeats gets _2, _3 and so on appended — an object cannot carry the same name twice with any predictable outcome, and RFC 8259 explicitly leaves the behaviour undefined. Set the header row to 0 when the sheet has no header at all and every key becomes its column letter.

Use null when the JSON feeds a schema or a database and every record must have the same shape, and omit the key when payload size matters or when your consumer distinguishes 'absent' from 'known to be empty'. There is a real difference between the two in the file as well: a cell that was never touched does not exist in the worksheet XML at all, while a cell someone cleared may still be there carrying an empty string. All three choices — null, the empty string, or leaving the key out — are one dropdown away, and the default is null because it is the one that keeps records rectangular.

Because the cell holds a very small serial number and a date format, and serial 1 is 1 January 1900. It is the classic sign of a column whose real content is a duration, a time of day or a zero: 0 with a date format shows as 1900-01-00 in Excel and comes out here as a time-only value, 0.5 is midday with no date at all, and 1 is the first day of the epoch. If the source cell should have held a real date, what usually went wrong upstream is that a blank or a 0 was written into a date-formatted column — switch the Dates dropdown to Excel serial to see the raw number and confirm it.

Yes — leave "Rebuild nesting from dotted headers" ticked and a column called user.address.city produces {"user":{"address":{"city":"…"}}} rather than one long key. Numeric path segments make arrays, so items.0.sku and items.1.sku come back as a two-element list, and gaps in the indexes are filled with null so the positions still line up. Untick it when your headers legitimately contain dots — a column named 3.5mm jack should stay a single key — and the status line reports any path where a header is used both as a value and as the parent of another header.

Set Output to 'Object keyed by a column' and pick the column whose values should become the property names. That gives you {"1001":{…},"1002":{…}}, the shape a lookup table wants and the shape pandas calls orient="index". The key column stays inside each record as well, because removing it would make the export lossy if two rows happened to share a key — and if any do, the status line counts them so you can pick a column that is actually unique.

Yes, and it keeps the literal the file stored rather than a re-printed approximation of it. A cell holding 0.1 comes out as 0.1, not 0.1000000000000000055511151231257827; a cell holding 1.5e+21 keeps its exponent. Booleans come out as true and false rather than the strings "TRUE" and "FALSE", and the eight Excel error values — #N/A, #DIV/0!, #REF! and the rest — come out as strings, because there is nothing else in JSON to map them to. Untick 'Keep numbers and booleans typed' when the consumer wants every field as a string.

Because .xls is a completely different format — an OLE2 compound document full of binary BIFF records, with no XML anywhere in it — and this page reads the XML-based .xlsx that replaced it in Office 2007. Rather than producing garbage, the file is identified by its magic bytes (D0 CF 11 E0 A1 B1 1A E1) and named. The same check catches .xlsb workbooks, OpenDocument .ods files, password-protected packages and CSVs that were renamed to .xlsx; in every case the fix is to open the file once and re-save it as .xlsx.