The Format step tells the Lake how to read the files of a datasource: the CSV or TSV dialect, the shape of JSON documents, workbook options and how column types are read. Open it while you create a file datasource, or open it again later to change how the source reads its files.
The step is the third of five in the file datasource wizard (General > Source > Format > Tables > Review), and it appears for the CSV, TSV, JSON, Apache Parquet and Excel Workbook source types. The rest of the flow is on Load files.
Each format shows its own options under a heading named after it, for example CSV format, with the preview below. Parquet files carry their own schema and types, so there is nothing to configure for them. The settings take effect when you save; how they apply to files you already stored is covered in When your settings apply.
The standalone Amazon S3 source has a Format step of its own, with a different list of file types; it is described on Connect Amazon S3.
The dialect settings shape how delimited text is read. TSV starts from a tab delimiter; everything else below is shared with CSV. A new source starts with Auto-detect for the delimiter, header and encoding, no skipped rows, a double quote character, the 1,234.56 number format and the Day / Month / Year date order.
Using {label} (detected) once the preview has a sample; if you set a delimiter yourself and the sample looks separated by another one, a line offers The preview looks separated by {label}. with a Use it action.Delimiter, e.. While the field is empty, the step notes Type the delimiter; {label} is used until then. A custom delimiter can be up to 16 characters and cannot contain line breaks or quote characters.Detected: the first row is the header or Detected: no header row.How ambiguous dates like 01/Null values lists the strings that mean "no value": type a value and press Enter to add it, or pick one of the suggestions, (empty), NULL, N/ and -. The empty string appears as (empty). Press Backspace on an empty input to remove the last chip, or use the remove control on a chip to remove that one.
TSV sources offer Strict TSV: Read files as ClickHouse TSV: values escaped with backslashes and never quoted. While it is on, the delimiter is fixed to tab, the step shows Strict TSV: tab separated, backslash escapes, no quoting. in place of the delimiter field, and the quote character field is hidden.
Every format except Parquet ends with Load all columns as String: Skip type detection; every value is kept exactly as written. Turn it on when you do not want the Lake to guess types for the files. A table group can change the choice for itself on the Tables step; see Group files into tables.
JSON sources show a shape editor instead of a dialect:
Looks like: {shape}.data.. Click Use as records on an array in the document tree, or type the path; the field takes the value on blur or Enter, and clearing it returns to the shape default. The help text under the field reads Click an array in the document to load each of its items as a row.[n], objects as {n}, and the top is named document. The chosen array carries a Records badge; tick a scalar value to carry it into every record. Until a document is ready, the panel reads The first document appears here once a file is ready._). Rename a column in place or stop carrying the value._. A value deeper than the chosen depth, or a nested container such as an array, is kept as text rather than split further. The range is 1 to 5 and the default is 3._raw column instead of separate columns; Flatten depth is disabled while it is on.These settings are the defaults for every table group, as the step notes: These settings are the defaults for every table group; a group can override them on the Tables step. A group that overrides them shows (custom for this group) and can go back with Reset to format defaults; see Group files into tables.
An Excel Workbook source gets two switches and a list of per-workbook overrides. Both switches start off.
Rows become columns.Convert datetime header values to time format when possible. It needs Transpose table data; while transpose is off, it stays disabled and reads Available when the table data is transposed.Matched against the workbook file name, ignoring case; use * and ? as wildcards.sales_*.. Each row takes a name or pattern (placeholder book.) and its own Transpose and Header datetimes switches; rows move with Move up and Move down, or go with Remove row.Matches none of the current workbooks., Matches {names}, but {rows} already apply first., Applies to {names}. or Same options as above, so this row changes nothing.Keep it under 255 characters when it is longer). A row with no pattern reads Enter a workbook file name or a pattern; a pattern that carries a folder part is flagged with Match the file name only, without folders; a pattern that repeats an earlier one reads Same as row {n}, so this row never applies.Workbook names appear here once files are added., and the picker reads No other workbooks yet. Once files are there, a summary says how many workbooks use the options above.These options apply when you save. In edit mode the note adds that only the workbooks whose options change are read again.The preview sits below the settings and shows what the Lake will read from the current files:
Preview: {table}. With more than one table, a Table picker switches between them; each entry reads {title} ({n} files/, and Excel sheets are grouped under their workbook. The line under the table reads {n} file(s) in this table; showing up to 20 rows.NULL. Up to 20 rows show per table.Loading preview. while it loads, Add files on the Source step to see a preview when the source has no files yet, Waiting for files to finish processing while files are being prepared, and No rows to preview when the ready files produced no rows.Preview failed.Files are converted to UTF-8 when stored.A change applies to newly added files only; stored files keep their current footer handling. for footer rows, and Files are converted to UTF-8 when stored. for encoding.The delimiter and header are detected from the preview, which has not loaded.For the messages you may see while you set up a file source, read Lake troubleshooting.
Auto-detect, Comma ( , ), Semicolon ( ; ), Pipe ( | ), Tab, or Other... to type your own. In Auto-detect, the preview sample decides. A custom delimiter can be up to 16 characters and cannot contain line breaks or quote characters.
Set Skip rows before header for rows above the header and Skip footer rows for rows at the end. Each takes 0 to 1000. On an existing source, a footer change applies to newly added files only; stored files keep their current footer handling.
Add them to Null values: type the value and press Enter, or pick a suggestion ((empty), NULL, N/, -). The empty string shows as (empty). Press Backspace on an empty input to remove the last chip.
A TSV setting that reads files as ClickHouse TSV: values escaped with backslashes and never quoted. While it is on, the delimiter is fixed to tab and the quote character field is hidden.
Pick the file's real encoding under Encoding, or leave Auto-detect to guess it. Files are converted to UTF-8 when they are stored.
In a new source, files you already uploaded are read again with the new encoding after you save. On an existing source, the change applies to newly added files only; stored files keep their current conversion.
JSON Lines (one record per line) reads one JSON object per line. Top-level array reads one array whose items are the records. Object with a records array reads a wrapper object and needs the array's path (the next question covers it). Object map (one record per key) turns each key's value into a record. Single object per file reads the whole file as one record. Auto-detect guesses and reports Looks like: {shape}.
It points at the array that holds the records inside a JSON document, for example data.. Click Use as records on an array in the document tree, or type the path yourself; the value is taken on blur or Enter, and clearing it returns to the shape default.
It sets how deep nested objects become their own columns; nested names join with _. A value deeper than the chosen depth, or a nested container such as an array, is kept as text at the cut. Choose 1 to 5; the default is 3, and the control is disabled while Schemaless is on.
It keeps each record in a single _raw column instead of separate columns. Flatten depth is disabled while it is on.
Read the line under the row. Matches {names}, but {rows} already apply first. means an earlier row already claims those workbooks, and the first matching row wins. Same as row {n}, so this row never applies means the pattern repeats an earlier row. Move the row up, or change or remove its pattern.
Four states. Loading preview. means it is still loading. Add files on the Source step to see a preview means the source has no files yet. Waiting for files to finish processing means the files are uploaded but not ready. No rows to preview means the ready files produced no rows; recheck the format settings and the file itself.