Converting a CSV file to Markdown using SQLite

Feb 04, 2021

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.