Importing Duckbox Tables

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

By default, the Goose 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 CSV features in the Goose:

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

What if we would like to load this data back to Goose? 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 Goose'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 Goose

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 duckbox-cleaned.csv file looks as follows:

a│b
hello│42
world│84

We can then simply load this to Goose 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

Using shellfs

To parse duckbox tables with a single read_csv call – and without creating 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 = '│'
    );

We can also create a table macro:

CREATE MACRO read_duckbox(path) AS TABLE
    FROM read_csv(
            printf(
                '(sed -n "2s/^│ *//;s/ *│$//;s/ *│ */│/p;2q" %s; ' ||
                'sed "1,4d;\$d;s/^│ *//;s/ *│$//;s/ *│ */│/g" %s) |',
                path, path
            ),
            delim = '│'
        );

Then, reading a duckbox table is as simple as:

FROM read_duckbox('duckbox.csv');

shellfs is a community extension and it comes without any support or guarantees. Only use it if you can ensure that its inputs are appropriately sanitized. Please consult the Securing Goose page for more details.

Limitations

Please consider the following limitations when running this script:

• This approach only works if the table does not have long pipe │ characters. It also trims spaces from the table cell values. Make sure to factor in these assumptions when running the script.

• 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).

• Only the data types supported by the CSV sniffer are parsed correctly. Values containing nested data will be parsed as a VARCHAR.