Acquire / file · All operations

Read a file with several record layouts

A variant of 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.

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “the delimited file did not split into a header row, three detail rows and a trailer whose count and total match them; then the fixed-width file did not split into a header row, three detail rows and a trailer whose count and total match them”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Talend (2)
  • tFileInputMSDelimitedread 2026-09-27

    Read a delimited file holding several record structures, one output schema per structure

  • tFileInputMSPositionalread 2026-09-27

    Read a fixed-width file holding several record structures, one output schema per structure

Background

In Talend, tFileInputMSDelimited reads the schemas of a multi-structured delimited file and sends the fields defined in each schema on to the next components.[1] Talend's tFileInputMSPositional reads the schemas of a multi-structured positional file, sending the fields defined in each schema to the next components.[2]

When to use it

In the United States, the ACH Network is the national automated clearing house (ACH) for electronic funds transfers.[3] An ACH file is a fixed-width file with each line exactly 94 characters in length.[4] Each record type in an ACH file is identified by a single-digit type code, so the record can always be determined by the first number of the line.[4] An ACH file begins with a single File Header Record and ends with a File Control Record, and each batch inside it starts with a Batch Header Record and is closed by a Batch Control Record.[4] Reach for a multi-layout reader when a code in each line says what kind of record it is and the kinds carry different fields, so each kind lands in a table of its own. (Meridian's view)

ACH files are padded with lines of 9s, so that the number of lines in the file is a multiple of 10.[4] An ACH Batch Control Record sums up the count and dollar amount of all entries in the batch, and includes a hash total to ensure the batch is valid.[4]

The method

Each line of an ACH file is a record made up of fields at specific positions within that line, and in a properly formatted file the records follow a specific order.[4] An ACH File Control Record contains counts such as the number of batches and entries, debit and credit totals, and another hash total to ensure the file was generated correctly.[4]

One way to read such a file is to read every line into enough text columns for the widest layout, then route each line by its record code to a table typed for that layout. (Meridian's view) In DuckDB, read_csv's null_padding option pads the remaining columns on the right with NULL values when a line lacks columns.[5] Setting DuckDB's strict_mode to false lets read_csv attempt to read structurally incorrect files, which can cause ambiguity, so the option should be used with caution.[5]

Sources

  1. [1]MS Delimited components | Talend Components for Jobs Helpdocumentation
  2. [2]MS Positional components | Talend Components for Jobs Helpdocumentation
  3. [3]ACH Network - Wikipediasecondary source
  4. [4]ACH Filesdocumentation
  5. [5]CSV Import – DuckDBdocumentation