⌘+k ctrl+k
1.2.0 (stable)
Search Shortcut cmd + k | ctrl + k
Loading Duckbox Tables

The scripts provided in this page work on Linux, macOS, and WSL.

By default, the DuckDB CLI client renders query results in the duckbox format, which uses rich, ASCII-art inspired tables to show data. These tables are often shared verbatim in other documents. For example, take the table used to demonstrate new CSV features in the DuckDB v1.2.0 release blog post:

┌─────────┬───────┐
│    a    │   b   │
│ varchar │ int64 │
├─────────┼───────┤
│ hello   │    42 │
│ world   │    84 │
└─────────┴───────┘

What if we would like to load this data back to DuckDB? This is not supported by default but it can be achieved by some scripting: we can turn the table into a │-separated file and read it with DuckDB's CSV reader. Note that the separator is not the pipe character |, instead it is the “Box Drawings Light Vertical” character │.

Loading Duckbox Tables to DuckDB

First, we save the table above as duckbox.csv. Then, we clean it using sed:

echo -n > duckbox-cleaned.csv
sed -n "2s/^│ *//;s/ *│$//;s/ *│ */│/p;2q" duckbox.csv >> duckbox-cleaned.csv
sed "1,4d;\$d;s/^│ *//;s/ *│$//;s/ *│ */│/g" duckbox.csv >> duckbox-cleaned.csv

The script is compatible with both BSD sed (which is the default on macOS) and GNU sed (which is the default on Linux and available on macOS as gsed).

The duckbox-cleaned.csv file looks as follows:

a│b
hello│42
world│84

We can then simply load this to DuckDB via:

FROM read_csv('duckbox-cleaned.csv', delim = '│');

And export it to a CSV:

COPY (FROM read_csv('duckbox-cleaned.csv', delim = '│')) TO 'out.csv';

a,b
hello,42
world,84

This approach only works if the table does not have long pipe │ characters.

Using shellfs

To implement this in a single read_csv without any temporary files, we can use the shellfs Community Extension:

INSTALL shellfs FROM community;
LOAD shellfs;
FROM read_csv(
    '(sed -n "2s/^│ *//;s/ *│$//;s/ *│ */│/p;2q" duckbox.csv; ' ||
    'sed "1,4d;\$d;s/^│ *//;s/ *│$//;s/ *│ */│/g" duckbox.csv) |',
    delim = '│');
About this page