How to insert CSV data into an SQLite table via a shell pipe?

Question

I have written a program that outputs the result to the standard output in strict pure CSV form (every line represents a single record and contain the same set of comma-separated fields, fields only contain lowercase English letters, numbers and dots, no spaces, no quotes and no symbols that might need to be escaped/encoded).

How do I redirect this output into an SQLite table that it fits into perfectly?

It would be great if I could control whether I want constraint-breaking (e.g. having the same primary/secondary key as the records already in the table) replace existing records or be discarded silently.

Of course I might build direct SQLite database output support in the program itself but I would prefer going the unix-way if possible.

Solution 1

I found an even simpler solution that still uses sqlite3 .import, but that doesn't read /dev/stdin or a temporary named pipe. Instead, it uses .import with the pipe operator to invoke cat - to read directly from standard input.

#!/bin/bash

function csv_to_sqlite() {
  local database_file_name="$1"
  local table_name="$2"
  sqlite3 -csv $database_file_name ".import '|cat -' $table_name"
}

database_file_name=$1
table_name=$2

csv_to_sqlite "$database_file_name" "$table_name"

Note that this wouldn't work in a Windows command shell.

Solution 2

Two approaches:

Sample test.csv file:

GroupName,Groupcode,GroupOwner,GroupCategoryID 
System Administrators,sysadmin,13456,100
Independence High Teachers,HS Teachers,,101
John Glenn Middle Teachers,MS Teachers,13458,102
Liberty Elementary Teachers,Elem Teachers,13559,103
1st Grade Teachers,1stgrade,,104
2nd Grade Teachers,2nsgrade,13561,105
3rd Grade Teachers,3rdgrade,13562,106
Guidance Department,guidance,,107

---

1) With csvkit (a suite of command-line tools for converting to and working with CSV)

Import into sqlite3 database:

csvsql --db sqlite:///test_db --tables test_tbl --insert test.csv

If no input csv file was specified it'll accept csv data from stdin:

... | csvsql --db sqlite:///test_db --tables test_tbl --insert

To extract data from sqlite database:

sql2csv --db sqlite:///test_db --query 'select * from test_tbl limit 3'

The output:

GroupName,Groupcode,GroupOwner,GroupCategoryID 
System Administrators,sysadmin,13456,100
Independence High Teachers,HS Teachers,,101
John Glenn Middle Teachers,MS Teachers,13458,102

---

2) With sqlite3 command-line tool (allows the user to manually enter and execute SQL statements against an SQLite database)

Use the ".import" command to import CSV (comma separated value) data into an SQLite table. The ".import" command takes two arguments which are the name of the disk file from which CSV data is to be read and the name of the SQLite table into which the CSV data is to be inserted.

Note that it is important to set the "mode" to "csv" before running the ".import" command. This is necessary to prevent the command-line shell from trying to interpret the input file text as some other format.

$ sqlite3
sqlite> .mode csv
sqlite> .import test.csv test_tbl
sqlite> select GroupName,Groupcode from test_tbl limit 5;
"System Administrators",sysadmin
"Independence High Teachers","HS Teachers"
"John Glenn Middle Teachers","MS Teachers"
"Liberty Elementary Teachers","Elem Teachers"
"1st Grade Teachers",1stgrade
sqlite> 

Solution 3

On Unix-like systems, the sqlite3 command .import can read CSV content from standard input from special file /dev/stdin:

$ printf "col1,col2\na,1\nb,2\n" | sqlite3 test.db ".import --csv /dev/stdin test"
$ sqlite3 -csv -header test.db "SELECT * FROM test"
col1,col2
a,1
$ sqlite3 -version
3.32.3 2020-06-18 14:00:33 7ebdfa80be8e8e73324b8d66b3460222eb74c7e9dfd655b48d6ca7e1933cc8fd
b,2

Solution 4

The following bash function csv_to_sqlite reads CSV content from standard input and imports it into an SQLite database table using the sqlite3 command .import and a temporary named pipe (FIFO):

#!/bin/bash

function csv_to_sqlite() {
  local database_file_name="$1"
  local table_name="$2"
  local temp_input_fifo=$(mktemp -u)
  mkfifo $temp_input_fifo
  sqlite3 -csv $database_file_name ".import $temp_input_fifo $table_name" &
  cat > $temp_input_fifo
  rm $temp_input_fifo
}

database_file_name=$1
table_name=$2

csv_to_sqlite "$database_file_name" "$table_name"

$ printf "col1,col2,col3\na,1,2.6\nb,2,5.4\n" | ./csv_to_sqlite test.sqlite test
$ sqlite3 -csv -header test.sqlite "SELECT * FROM test"
col1,col2,col3
a,1,2.6
b,2,5.4

View more solutions

Comments

[RomanPerekhrest]

Has outputted csv a header columns as the 1st line?

[Ivan]

@RomanPerekhrest Not really. But of course covering both cases will make this Q&A more interesting.

[user3390906]

csvkit uses agate's from_csv, which, for reasons which are unclear to me, reads in the entire contents of stdin github.com/wireservice/agate/blob/…

[Derek Mahar]

See simpler solution unix.stackexchange.com/a/642364/5783.