SQLite – log4code

From time to time I have a need create a table in Markdown format. Usually the original source is some kind of data sitting in a CSV file. There are quite a few converters out there, and of course, I could write my own. However, with my deepening interest in SQLite, I wanted to write a utility that used its power to do the work for me.

In late 2020, SQLite released 4 new output modes for the CLI tool. One of great interest to me was ‘markdown’

.mode markdown

Knowing how easy it is to import a CSV file into SQLite, I felt that a conversion from CSV to Markdown was probably just a few lines away.

First Try

$ sqlite3
sqlite> .headers on
sqlite> .mode csv
sqlite> drop table if exists temp_csvFile;
sqlite> .import input_file.csv temp_csvFile
sqlite> .mode markdown
sqlite> .headers on
sqlite> .output output_file.md
sqlite> select * from temp_csvFile;

Let’s go through each line to understand what is happening.

Once the SQLite CLI is launched, we make sure headers are on. This will ensure the CSV import to SQLite goes as expected.

 .headers on

Next, we ensure that the mode is set to ‘csv’ as that will be the format of the file being imported.

.mode csv

Third, as a safety measure, drop the table we want to import to if it exists.

drop table if exists temp_csvFile;

Now perform the CSV file import.

.import input_file.csv temp_csvFile

At this point the entire contents of the CSV should be sitting in the ‘temp_csvFile’ table.

We want to have our data in a nicely formatted Markdown table once this is all done. To do this we change the SQLite mode to ‘markdown.

 .mode markdown

As a safety check, we make sure that column headers are turned on. We want them included in our output!

 .headers on

By default, the output from a SQL statement comes to the command line. In our case we want to write the output to a file. Let’s specify that now.

.output output_file.md

The last step is to run our select statement and grab everything from the table that holds the contents from the CSV file.

select * from temp_csvFile;

Now we should have a file in Markdown table format that represents the contents of the CSV file!

This works great, but its quite a bit of typing. Fortunately the SQLite CLI tool has a command that will take in a file with multiple commands and execute them for you. It will even accept .(dot) commands!

Second Try

Let’s create a file with all of the above commands and then let the SQLite CLI tool read it in and execute it for us.

csv2markdown.sql

.headers on
.mode csv
drop table if exists temp_csvFile;
.import input_file.csv temp_csvFile
.mode markdown
.headers on
.output output_file.md
select * from temp_csvFile;

Now we can use this SQL file and let SQLite read and execute the contents.

sqlite3 '' '.read csv2markdown.sql'

In the above line we are opening a nonexistent database signified by ”. The second parameter is the most interesting:

'.read csv2markdown.sql'

This tells the SQLite CLI to read and execute all commands in the file named ‘csv2markdown.sql’.

Third Try

At this point, we have a handy SQL file we can pass to SQLite and make a CSV file to Markdown table conversion.

One downside to the SQL file though is that the name of our input file is hard coded. Let’s fix that.

csv2md.sh

#!/bin/bash
 

outputFile="$1.md"
rm -f outputFile
 

cmd=$({ grep -v '^#' <<EOF
.headers on
.mode csv
drop table if exists temp_csvFile;
.import $1 temp_csvFile
.mode markdown
.headers on
.output $outputFile
select * from temp_csvFile;
EOF
})
 

echo -e "$cmd" | sqlite3 ''

This is a bash shell script that accepts a file name as an input parameter, creates the list of commands needed by SQLite, and then executes them.

Let’s walk through the code.

First, we take the name of the input file (our CSV file), and create the name of the file to be output (by adding the .md extension). We also delete any existing output file by the same name if it already exists.

So, if our input file is myFile.csv, the output file will be myFile.csv.md

outputFile="$1.md"
rm -f outputFile

The next section of the script creates the same contents as originally existed in our csv2markdown.sql file, except now the input and output file names are based on the file that is passed to our script.

(Hint: This took me a bit of time to get right and understand. Read up on bash heredoc for a better understanding.)

cmd=$({ grep -v '^#' <<EOF
.headers on
.mode csv
drop table if exists temp_csvFile;
.import $1 temp_csvFile
.mode markdown
.headers on
.output $outputFile
select * from temp_csvFile;
EOF
})

Now that we have dynamically created the commands, pass them to SQLite for execution.

echo -e "$cmd" | sqlite3 ''

After changing the execution permissions on our script file (chmod +x csv2md.sh), the entire conversion is now just a single command.

./csv2md.sh myFile.csv

Wrap up

So there it is, a fun little script to convert the contents of a CSV file to Markdown table format using the power of SQLite.

Here is the full script if you want to use and modify it for your own use: https://gist.github.com/log4code/079d1d1f02ea2c766951b220ce5b23f5

Some great additions to the script could include

Accepting a file output parameters
Verifying parameters
Being able to specify an alternative SELECT statement outside of SELECT * to provide for on-the-fly filtering while also converting.

Main Photo by Mika Baumeister @Unsplash

Only have a minute? Skip down to the tl;dr;.

The command line shell for SQLite provides a variety of ways to output query results to fit the needs of your project. In all, the latest version of the sqlite3 command shell provides 10 different ways to output query results. In this post we will cover the default output mode, list, and see the different options that are available.

All the examples below can be run using the Repl.it link here: https://repl.it/@log4code/SQLiteOutputFormats.

For this example we have a SQLite database with just one table. This table is named companies. It contains a small list of company records which we will query in different ways to show a variety of output formatting available via the .mode list command for the SQLite shell.

As previously mentioned, the list mode for SQLite is the default mode for output for the shell. There is no activation required. To show this, our first query will select all the rows and columns from the companies table.

select * from companies;

Here we can see the output with the default list mode:

1|Southern Tool Company|AL
2|Ohio Valley Tooling|OH
3|Midwest Machining, LLC|IN
4|Pacific Parts|CA
5|ABC Manufacturing|ME
6|Taylor & Sons Manufacturing|FL
7|Backlot Machining, Inc.|AZ

Each row in the result set is on a single line. Each column is separated by the pipe (|) character. For list mode, the pipe character is the default column separator.

Adding Column Headers

Notice in the output that we do not have column headers. This may or may not be preferred based on your needs. But, what if we wanted column headers? Adding column headers is as simple as performing an additional ‘dot command’ for the shell:

.headers on

The .headers command takes an argument (on or off) to allow for changing the output back and forth. To see the previous output with column headers, we will adjust our statements:

.headers on
select * from companies;

Now we have successfully added column headers to the output. Each column is separated by the same delimiter, in this case the | character.

company_id|company_name|company_state
1|Southern Tool Company|AL
2|Ohio Valley Tooling|OH
3|Midwest Machining, LLC|IN
4|Pacific Parts|CA
5|ABC Manufacturing|ME
6|Taylor & Sons Manufacturing|FL
7|Backlot Machining, Inc.|AZ

Changing the delimiter

While the | character is the default delimiter, it can be changed. What if one of the values returned contained a | character?

For example, what if instead of ‘Pacific Parts’ it was ‘Pacific|Parts’? The output for this row would now look like this:

company_id|company_name|company_state
4|Pacific|Parts|CA

Oops! Now we have 4 columns for that row instead of 3. Pacific and Parts now look like values in two separate columns instead of one. This is a good reminder of the importance of knowing about the data you are working with and checking the output for any issues you may need to be aware of.

We can solve this problem multiple ways. One way is to specify the output column delimiter to be different than the default |. We do this with the .seperator command

.headers on
.separator '|-|'
select * from companies;

Now we have specified the column separator to be what we believe should be unique: |-|. Let’s check out the output:

company_id|-|company_name|-|company_state
1|-|Southern Tool Company|-|AL
2|-|Ohio Valley Tooling|-|OH
3|-|Midwest Machining, LLC|-|IN
4|-|Pacific|Parts|-|CA
5|-|ABC Manufacturing|-|ME
6|-|Taylor & Sons Manufacturing|-|FL
7|-|Backlot Machining, Inc.|-|AZ

The column delimiter has indeed been changed from | to |-|. Now the Pacific|Parts value will work just fine with the output.

tl;dr;

The default output for the SQLite shell is .mode list.
This will output each row on a line with the default column delimiter of the | character.
To change the default delimiter character, use the .separator command
By default the .mode list command does not output column headers
To add column headers to the output, use the .headers on command.

Category: SQLite

Converting a CSV file to Markdown using SQLite