Acquire / file · All operations

Read fixed-width text

A variant of Read a CSV or delimited file

Read text whose columns sit at fixed character positions, with no separator between them.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Read text whose columns sit at fixed character positions

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
-- a fixed-width file: id in 1-3, customer in 4-15, amount in 16-23, the amount blank when it is missing
COPY (SELECT lpad(order_id::VARCHAR, 3, '0') || rpad(customer, 12, ' ') || coalesce(lpad(format('{:.2f}', amount::DOUBLE), 8, ' '), repeat(' ', 8)) AS line
      FROM orders ORDER BY order_id) TO '<your_scratch_dir>/fixed.txt' (FORMAT csv, HEADER false, QUOTE '', ESCAPE '', DELIMITER '~');
CREATE TEMP TABLE result AS
SELECT substr(line, 1, 3)::INTEGER AS order_id, trim(substr(line, 4, 12)) AS customer, nullif(trim(substr(line, 16, 8)), '')::DECIMAL(8, 2) AS amount
FROM (SELECT unnest(string_split_regex(regexp_replace(content, '\r?\n$', ''), '\r?\n')) AS line FROM read_text('<your_scratch_dir>/fixed.txt'));
SELECT * FROM result ORDER BY order_id;
SELECT CASE WHEN ((SELECT count(*) = 10 AND count(amount) = 9 AND sum(amount) = 1900.74 AND max(customer) FILTER (WHERE order_id = 3) = 'Bob  Okafor' AND max(customer) FILTER (WHERE order_id = 4) = 'Carla Díaz' FROM result)) THEN 'as expected' ELSE error('the fixed-width lines were not cut into 10 orders with 9 amounts totalling 1900.74') 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 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 fixed-width lines were not cut into 10 orders with 9 amounts totalling 1900.74”.
  • 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).

Duckle (1)
  • Fixed-widthread 2026-09-25

    read fixed-width text as SUBSTR projections over one-column lines

EasyMorph (1)
KNIME Analytics Platform (1)
OpenRefine (1)
Pentaho Data Integration (1)
Talend (2)
VisiData (1)
  • fixed (loader)read 2026-09-23

    Load fixed-width text, columns detected from the first rows

Background

In a fixed-width text file, each field has a fixed length, with extra spaces added as needed.[1] KNIME's Fixed Width File Reader reads files where a column is defined by the number of characters and not by a column delimiter.[2]

When to use it

Reach for a fixed-width reader when the fields sit at set positions in each line and nothing separates them. (Meridian's view) With predefined lengths, fixed-width fields are limited to a maximum length, and workarounds for a longer value include abbreviating phrases or splitting the file into multiple files.[1]

In pandas, read_fwf infers column positions from the first 100 rows of the data by default.[3] In pandas, a value wider than anything in the rows read_fwf inferred from can be truncated, and passing np.inf as infer_nrows infers the positions from the entire file.[3] In KNIME, the Fixed Width File Reader's preview shows only the first couple of lines, so the preview can look fine while running the reader fails.[2] In EasyMorph, the Import fixed width text action drops characters that extend past the width of the final column unless its Put remaining symbols into column option is on.[4] Take the column positions from the file's written layout where one exists, and check the last column for characters that fall past its edge. (Meridian's view)

The method

In EasyMorph, the width and name of every column have to be defined explicitly, and clicking a character in the preview creates a column splitter.[4] In pandas, read_fwf takes the column extents as half-open intervals in colspecs, or as a list of field widths when the intervals are contiguous.[3] VisiData's fixed-width loader finds where columns start from the header line when there is one, and otherwise from positions that are spaces in all the rows it checks.[5] VisiData's fixed_rows option, 1000 by default, sets the number of rows checked for fixed-width columns.[5]

Compared with the alternatives

Finding field boundaries in a delimiter-separated file requires finding the delimiters, which incurs computational overhead that fixed-width formats do not need.[1] Fixed-width formats can lead to unnecessarily large files if fields tend to be shorter than the lengths reserved for them.[1] Comma-separated lists were easier to type into punched cards than fixed-column-aligned data, and less prone to wrong results if a value was punched one column off.[6]

Sources

  1. [1]Flat-file database - Wikipediasecondary source
  2. [2]Fixed Width File Readerdocumentation
  3. [3]pandas.read_fwf — pandas 3.1.1 documentationdocumentation
  4. [4]IMPORT FIXED WIDTH TEXT [EasyMorph Help]documentation
  5. [5]visidata/visidata/loaders/fixed_width.py at 7609212ec2febafa9caba7183cbebfaf49627909 · saulpw/visidata · GitHubdocumentation
  6. [6]Comma-separated values - Wikipediasecondary source