# Read a file with several record layouts

Acquire / file

A variant of [Read a CSV or delimited file](https://meridian.online/operations/read-a-csv-or-delimited-file).

Read a delimited or fixed-width file whose lines follow different record structures, sending each structure to its own output.

Page: <https://meridian.online/operations/read-a-csv-or-delimited-file/read-a-file-with-several-record-layouts>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

read delimited and fixed-width files with header, detail and trailer records into three tables each

```sql
-- one delimited file and one fixed-width file, each with three record layouts told apart by the first field
COPY (SELECT * FROM (VALUES ('H|B7|2026-02-10'), ('D|1|Alice Ng|120.00'), ('D|2|Bob Okafor|45.25'), ('D|3|Carla Diaz|99.99'), ('T|3|265.24')) t(line))
  TO '<your_scratch_dir>/layouts.txt' (FORMAT csv, HEADER false, QUOTE '', ESCAPE '', DELIMITER '~');
CREATE TEMP TABLE raw AS
SELECT * FROM read_csv('<your_scratch_dir>/layouts.txt', header = false, delim = '|', quote = '', escape = '', null_padding = true, auto_detect = false, strict_mode = false,
                       columns = {'kind': 'VARCHAR', 'a': 'VARCHAR', 'b': 'VARCHAR', 'c': 'VARCHAR'});
CREATE TEMP TABLE batch_header AS SELECT a AS batch_id, b::DATE AS run_date FROM raw WHERE kind = 'H';
CREATE TEMP TABLE detail AS SELECT a::INTEGER AS order_id, b AS customer, c::DECIMAL(8, 2) AS amount FROM raw WHERE kind = 'D';
CREATE TEMP TABLE trailer AS SELECT a::INTEGER AS row_count, b::DECIMAL(8, 2) AS total FROM raw WHERE kind = 'T';
SELECT * FROM batch_header; SELECT * FROM detail; SELECT * FROM trailer;
-- the same three layouts at fixed positions: kind in 1, then H: batch 2-5 and date 6-15; D: id 2-4, customer 5-16, amount 17-24; T: count 2-4, total 5-12
COPY (SELECT line FROM (
        SELECT 0 AS pos, 'H' || rpad('B7', 4, ' ') || '2026-02-10' AS line
        UNION ALL SELECT id, 'D' || lpad(id::VARCHAR, 3, '0') || rpad(name, 12, ' ') || lpad(amount, 8, ' ')
                  FROM (VALUES (1, 'Alice Ng', '120.00'), (2, 'Bob Okafor', '45.25'), (3, 'Carla Diaz', '99.99')) t(id, name, amount)
        UNION ALL SELECT 99, 'T' || lpad('3', 3, '0') || lpad('265.24', 8, ' ')) ORDER BY pos)
  TO '<your_scratch_dir>/layouts_fixed.txt' (FORMAT csv, HEADER false, QUOTE '', ESCAPE '', DELIMITER '~');
CREATE TEMP TABLE fixed_lines AS
SELECT unnest(arr) AS line FROM (SELECT string_split_regex(regexp_replace(content, '\r?\n$', ''), '\r?\n') AS arr FROM read_text('<your_scratch_dir>/layouts_fixed.txt'));
CREATE TEMP TABLE fixed_header AS SELECT trim(substr(line, 2, 4)) AS batch_id, trim(substr(line, 6, 10))::DATE AS run_date FROM fixed_lines WHERE substr(line, 1, 1) = 'H';
CREATE TEMP TABLE fixed_detail AS SELECT substr(line, 2, 3)::INTEGER AS order_id, trim(substr(line, 5, 12)) AS customer, trim(substr(line, 17, 8))::DECIMAL(8, 2) AS amount FROM fixed_lines WHERE substr(line, 1, 1) = 'D';
CREATE TEMP TABLE fixed_trailer AS SELECT substr(line, 2, 3)::INTEGER AS row_count, trim(substr(line, 5, 8))::DECIMAL(8, 2) AS total FROM fixed_lines WHERE substr(line, 1, 1) = 'T';
SELECT * FROM fixed_header; SELECT * FROM fixed_detail; SELECT * FROM fixed_trailer;
SELECT CASE WHEN ((SELECT count(*) FROM batch_header) = 1 AND (SELECT count(*) = 3 AND sum(amount) = 265.24 FROM detail) AND (SELECT row_count = (SELECT count(*) FROM detail) AND total = (SELECT sum(amount) FROM detail) FROM trailer) AND (SELECT list(column_name) FROM (DESCRIBE batch_header)) = ['batch_id', 'run_date'] AND (SELECT list(column_name) FROM (DESCRIBE detail)) = ['order_id', 'customer', 'amount'] AND (SELECT list(column_name) FROM (DESCRIBE trailer)) = ['row_count', 'total']) THEN 'as expected' ELSE error('the delimited file did not split into a header row, three detail rows and a trailer whose count and total match them') END AS verdict;
SELECT CASE WHEN ((SELECT batch_id = 'B7' AND run_date = DATE '2026-02-10' FROM fixed_header) AND (SELECT list(customer ORDER BY order_id) = ['Alice Ng', 'Bob Okafor', 'Carla Diaz'] AND sum(amount) = 265.24 FROM fixed_detail) AND (SELECT row_count = 3 AND total = 265.24 FROM fixed_trailer)) THEN 'as expected' ELSE error('the fixed-width file did not split into a header row, three detail rows and a trailer whose count and total match them') END AS verdict;
```

Replace `<your_scratch_dir>` with a directory of your own that you can write to before running the statement.

The statement writes `layouts.txt` and `layouts_fixed.txt` itself, in `<your_scratch_dir>`.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Talend: [`tFileInputMSDelimited`](https://help.qlik.com/talend/en-US/components/8.0/ms-delimited/ms-delimited-component) (read 2026-09-27)
- Talend: [`tFileInputMSPositional`](https://help.qlik.com/talend/en-US/components/8.0/ms-positional/ms-positional-component) (read 2026-09-27)