DuckDB SQL to create CSV from a list of files

README.md

This SQL snippet takes a list of newline-separated filenames, for example from the output of find or fd, and creates a CSV file with the columns defined below. This is useful for creating a spreadsheet that can be imported into Excel or other tool for filtering or analysis.

• path: original filename
• dirname: the directory component of path, NULL if the file has no directory
• basename: the filename component of path
• extension: the extension component of the path, NULL for no extension

The dirname and basename columns are similar to the output of dirname(1) and basename(1) general commands.

files.sql

COPY (
    SELECT
        path,
        CASE
            WHEN instr(path, '/') > 0 THEN
                left(path, length(path) - length(reverse(split_part(reverse(path), '/', 1))) - 1)
        END AS dirname,
        reverse(split_part(reverse(path), '/', 1)) AS basename,
        CASE
            WHEN instr(basename, '.') > 0 THEN
                reverse(split_part(reverse(basename), '.', 1))
        END AS extension
    FROM read_csv(
        'files.txt',
        header = FALSE,
        column_names = ['path'],
        delim = '\t'
    )
) TO 'files.csv' (HEADER, DELIMITER ',');