How can SQLite command ".import" read directly from standard input?

Question

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

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.) — Gordon Davisson, Mar 26 '21 at 04:18
@GordonDavisson Yes, `/dev/stdin` would work, but I wanted to avoid referring to files that might be non-standard. — Derek Mahar, Mar 26 '21 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 '21 at 12:09
I found another [solution](https://unix.stackexchange.com/a/642362/5783) that doesn't read `/dev/stdin` or a temporary named pipe. — Derek Mahar, Mar 27 '21 at 12:31
@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 '21 at 12:41
@fra-san Not sure if this is a corner case one should care about, but Bash dealing with `/dev/stdin` etc. itself seems to be a compile time option. So if you have a system that should support have it, but the file is broken, Bash won't help you there. — ilkkachu, Mar 27 '21 at 13:27
@ilkkachu I wouldn't know either, but, as Kusalananda pointed out, it is indeed not relevant in this case (good point in general, though). -- I'll start removing up my comments here, they're not helpful anymore (and possibly misleading...). — fra-san, Mar 27 '21 at 14:00
`/dev/stdin` blows up memory if the file is too large for memory unfortunately. — Ciro Santilli OurBigBook.com, Jul 03 '23 at 15:13

Derek Mahar · Answer 1 · 2021-03-27T12:47:34.263

11

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"

edited Mar 27 '21 at 12:47

answered Mar 27 '21 at 12:29

Derek Mahar

505
3
13

1

well, I should have read the manual better the first time. – ilkkachu Mar 27 '21 at 12:35
3

@ilkkachu This solution wasn't obvious because, unfortunately, the [`.import`](https://sqlite.org/cli.html#importing_csv_files) documentation doesn't mention the `|` operator. As you discovered, you have to read the [`.read`](https://www.sqlite.org/cli.html#reading_sql_from_a_file) documentation to discover the `|` operator. – Derek Mahar Mar 27 '21 at 12:40
1

@ilkkachu I moved your link to the `sqlite3` [`.read`](https://www.sqlite.org/cli.html#reading_sql_from_a_file) command to the first paragraph as an inline link. – Derek Mahar Mar 27 '21 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: https://sqlite.org/cli.html#importing_csv_files – kevinarpe Dec 24 '21 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 https://www.sqlite.org/cgi/docsrc/info/7652db76ff667908. – Derek Mahar Dec 25 '21 at 14:00

How can SQLite command ".import" read directly from standard input?

1 Answers1