9

How would you modify function csv_to_sqlite so that sqlite3 command .import reads directly from standard input instead of from a temporary named pipe?

#!/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
9
  • 4
    Would .import /dev/stdin $table_name work? (Depending on your OS, you might need to replace /dev/stdin with /dev/fd/0 or something like that.) Mar 26, 2021 at 4:18
  • @GordonDavisson Yes, /dev/stdin would work, but I wanted to avoid referring to files that might be non-standard. Mar 26, 2021 at 22:38
  • @fra-san Since they are using bash, the existence of /dev/stdin is guaranteed (it's provided by the shell if it does not already exist). Care to write up an answer?
    – Kusalananda
    Mar 27, 2021 at 12:09
  • I found another solution that doesn't read /dev/stdin or a temporary named pipe. Mar 27, 2021 at 12:31
  • 2
    @fra-san Also, since it's the sqlite process accessing the file, not bash, there is no guarantee about /dev/stdin existing (although, there is very few systems that does not have it).
    – Kusalananda
    Mar 27, 2021 at 12:41

1 Answer 1

14

I found another 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"
5
  • 1
    well, I should have read the manual better the first time.
    – ilkkachu
    Mar 27, 2021 at 12:35
  • 3
    @ilkkachu This solution wasn't obvious because, unfortunately, the .import documentation doesn't mention the | operator. As you discovered, you have to read the .read documentation to discover the | operator. Mar 27, 2021 at 12:40
  • 1
    @ilkkachu I moved your link to the sqlite3 .read command to the first paragraph as an inline link. Mar 27, 2021 at 12:53
  • @ilkkachu It is very subtle in the docs! The source argument is the name of a file to be read or, if it begins with a "|" character, specifies a command which will be run to produce the input CSV data. Ref: sqlite.org/cli.html#importing_csv_files
    – kevinarpe
    Dec 24, 2021 at 12:39
  • 2
    @kevinarpe An SQLite contributor added this reference to the pipe character to the documentation for the .import command on 2021-04-01, just a few days after @ikkachu and I made our comments. See sqlite.org/cgi/docsrc/info/7652db76ff667908. Dec 25, 2021 at 14:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .