Importing data from JSON, XML, HTML or a spreadsheet¶

Import a file into a new or existing table. Four kinds are read: JSON, XML, HTML pages, and spreadsheets — ODS, XLSX and XLS.

Unlike a text file, each of these describes itself, so there is nothing to tell SQLite Expert about delimiters or encodings. Instead the dialog shows you what it found in the file before anything is written — the columns, their types, and how many rows there are — so you can check the reading before you commit to it.

To import a file:

Choose the matching entry under Import/Export on the main menu: Import JSON File, Import XML File, Import HTML File or Import Spreadsheet (ODS, XLSX, XLS). The same four are on the database tree's pop-up menu, under Import/Export. They all open the same dialog, and it reads whichever of the four you give it, whatever you opened it as.
Select the source file. The Detected panel fills in as soon as you do.
If the file holds more than one table, pick the one you want from Table. See Files holding more than one table.
Choose the destination:
- Existing table — pick a table from the list to add the rows to. Values go into the columns whose names match; a column the file says nothing about is left null.
- New table — enter a name, and SQLite Expert creates the table from the columns it detected.
For a new table, choose under Column types how those types should be written into the schema — descriptive names, with or without sizes, or the names a STRICT table allows. The four choices are the same ones the text import offers, and are described under How the columns are declared.
Tick Delete existing data to clear the table before importing.
Click Start.

What the Detected panel is telling you¶

It says one of two things, and the difference matters.

Columns, as the file states them — the file carries its own schema, so nothing was guessed. A JSON file exported by SQLite Expert carries a columns list; an XML file in DATAPACKET form — which is what the XML export writes, and what a Delphi client dataset writes — carries a METADATA block. A file exported by SQLite Expert therefore comes back exactly as it went out.

HTML pages and spreadsheets never state their columns: there is no place in either format to say what a column is. Their columns are always worked out.

Columns, worked out from the values — the file is just data, so each column's type was worked out by looking at every value under that name:

What the column holds	Type
Whole numbers only	`INTEGER`
Numbers, any of them with a fraction	`REAL` (`FLOAT` with the legacy column types)
`true` and `false` only	`BOOLEAN`
A nested object or array (JSON)	`TEXT`, holding the JSON
Anything else, or a mixture	`TEXT`

Text is where every disagreement ends, because it is the one type that cannot lose anything: SQLite stores what it is given, and a column read back as text still holds every character that arrived.

Note

For XML, where every value is characters, the reading is deliberately strict: 007 is a code rather than the number seven, so a column of values like that stays TEXT and keeps its leading zeros. The same goes for anything with a space, a currency symbol or a thousands separator in it.

Which files can be read¶

JSON. The rows can be arranged in any of these ways:

an array at the top level — [{...}, {...}];
an object with a rows array, which is what the JSON export writes;
an object with exactly one array in it under any name at all, which is the shape most web APIs return;
a single object, read as one row.

An object with more than one array in it holds more than one table — see below.

HTML. Every <table> on the page, however deeply nested. The columns come from the first row when that row says it is a heading row — <th> cells, or any row inside <thead>. Without that signal the columns are named Column1, Column2, and no row is treated as headings, because a table whose first row is data would otherwise lose it. That is why the HTML export writes its heading row as <th>: so the file it produces reads back as the table it exported.

A table nested inside a cell belongs to that cell rather than to the rows of the table around it — and is offered as a table in its own right.

Spreadsheets. ODS, XLSX and XLS. Every worksheet is a table, named by its title, and the first row with anything in it names the columns. A spreadsheet has no way to say "these are headings", so this is a convention rather than something the file states — but it is the convention spreadsheets follow, and it is what the ODS and Excel exports write.

A cell reading x'…' — an x, then the bytes in hexadecimal between single quotes — is read as a BLOB rather than as those characters. That is the form the Excel and ODS exports write when Include BLOB data is ticked, and the same one the CSV export has always written, so a spreadsheet this program wrote comes back with its BLOB columns intact.

A column is only read that way when every value in it is one. A column that is bytes in some rows and words in others is a column of text, because only one of the two readings can be right for the whole column and the words are not bytes.

XML. Any of three shapes:

a DATAPACKET, which is what the XML export's Data option writes and what a Delphi client dataset writes;
a CACHE, which is what the XML export's Formatted option writes — its first row is the column headings and the rest are rows. That file is a rendering of the grid rather than its data, so what comes back is what the grid was showing, RecNo column and all, with no types stated;
a root element with the same element repeated once per row, with the values as attributes, as simple child elements, or both:

<people>
  <person id="1"><name>Ada</name></person>
  <person id="2"><name>Grace</name></person>
</people>

A root whose children have different names — a <header> and a hundred <row> elements, or two tables side by side — holds more than one table. See below.

Files holding more than one table¶

Plenty of files hold more than one list of rows: a JSON object with an array under each of several names, or an XML root with a header element and then the rows. When that happens, a Table box appears at the top of the Detected panel listing what was found, and the rest of the panel describes whichever one is selected. Pick another and everything follows it — the columns, the row count, and the name suggested for a new table.

One is chosen to start with, so the panel is never blank:

for JSON, the first array in the object;
for XML, HTML and spreadsheets, the one with the most rows — a header element, a page's navigation table and a sheet of notes are all short, and the data is not.

A file with only one table shows no Table box at all, and neither does a JSON file this program exported: rows means the rows, whatever else is in the file alongside it.

What each format can and cannot carry¶

A heading that is blank, or that repeats one already used, becomes Column1, Column2 and so on, so that every column can be named and none is lost.

Values arrive as text in every format but JSON, so their types are worked out from what the characters look like, and that reading is deliberately strict: 007 is a code rather than the number seven, and so is anything with a space, a currency symbol or a thousands separator in it.

An HTML cell keeps its text and loses its markup: tags are removed, entities such as & are resolved, and runs of spaces and line breaks become single spaces. A spreadsheet cell that is empty arrives as null, because a spreadsheet has no way to tell an empty cell from an empty string and null is the truthful reading of the two.