Reading a fixed-width file in DuckDBSQL

Fixed-width files can be little difficult but IF you consider each line of data as a string which you can attack with duckdb and substr() its not that difficult ;)

Execute this SQL

CREATE OR REPLACE TABLE example_table AS
SELECT 
    CAST(substr(line, 1, 4) AS INTEGER) AS activity_year,
    CAST(substr(line, 5, 10) AS VARCHAR(10)) AS lei_or_respondent_id,
    CAST(substr(line, 15, 1) AS CHAR(1)) AS agency_code,
    CAST(substr(line, 16, 1) AS CHAR(1)) AS loan_type,
    CAST(substr(line, 17, 1) AS CHAR(1)) AS loan_purpose,
    CAST(substr(line, 18, 1) AS CHAR(1)) AS occupancy_type
FROM
    (SELECT column0 AS line FROM read_csv('data.txt', AUTO_DETECT=TRUE, skip=1));
Copy code

Chetan Amrao

Edited 10/02/24