Import and export

Importing a file

Open a table and use Import / export › Import file…. Importing needs a table that already exists, has a primary key and is on a writable connection.

1. Choose file…. Lily reads the format from the extension:

   Format	Notes
   CSV	Comma-separated, standard quoting
   TSV	Tab-separated
   Excel (.xlsx)	Pick the sheet; formulas import their last calculated value
   JSON	An array of objects, or one object
   NDJSON	One object per line
   Parquet	Flat files with plain encoding, uncompressed or Snappy

   Files are read as UTF-8. For JSON, every key found becomes a column, and nested values are imported as JSON text.

2. Map the columns. First row is a header uses the first row as names. Auto-match pairs columns with the same name; pick — skip — for columns you do not want.

3. Click Import N rows.

Empty cells become NULL. The whole import runs in one transaction: if a row fails, nothing is written. ClickHouse has no transactions, so Lily does not import into it this way.

Quick CSV import… skips the mapping step. The first row must match the table's column names exactly.

Fake data

Import / export › Generate fake data… fills a table with up to 10,000 made-up rows. Each column gets a generator (names, emails, dates, Brazilian CPF, CNPJ, CEP and phone numbers, and more); foreign-key columns use real values from the referenced table. With AI set up, Lily picks the generators from the column names. Preview SQL shows the inserts before they run, in one transaction.

Exporting

For results, pages and selections, see Results, charts and export.

A whole table

In a table tab, Import / export has:

• Entire table → CSV and Entire table → JSON;
• SQL dump…, with CREATE TABLE and INSERT statements.

All three stream the rows in batches, so table size is not a problem.

A whole schema

Create… › Export schema as SQL… writes the CREATE TABLE statements of a schema to a .sql file. Views are skipped. Include data (INSERTs) adds every row; it is meant for small and medium schemas. For big tables, use SQL dump… on each table, or move the data straight to another database.