SQLite: How to simultaneously read data from stdin and table name from variable?

Question

From bash, I want to pass to sqlite3 my TSV table from stdin while also passing the name of the table to import into from a variable. How can this be accomplished? For example:

#!/bin/bash

output_sql_db="$1"
input_tsv="$2"
table_name="$3

tail -n +2 "$input_tsv" | sqlite3 "$output_sql_db" '.import "/dev/stdin" ${table_name}'

Of course in this example, ${table_name} does not get expanded correctly due to the use of ' in the sqlite3 command. How should this be done? It seems all the answers I find only show to hand handle one or the other (data import OR passing table name).

tenken · Accepted Answer · 2016-12-30 22:27:11Z

1

Use a bash heredoc, and decide when/where you escape input. At variable definition or command creation.

EDIT:

My point is to simplify the syntax/approach you're using, to something like.

output_sql_db="$1"
input_tsv="$2"
table_name="$3
importfile=$(tail -n +2 $input_tsv)
sqlite3 "$output_sql_db .import ${importfile} ${table_name}"

edited Dec 30, 2016 at 22:27

answered Dec 28, 2016 at 4:42

tenken

2305 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5359531 Over a year ago

Do you have an example of how to do this? I have been using heredocs for other steps as well but could not figure out how to make it work in this case. I thought that both the heredoc and the piped file would be sent through stdin, which would cause conflicts, no?

mivk · Accepted Answer · 2018-11-15 22:30:35Z

The main problem in your example is the use of single-quotes around your command, which prevents variable interpolation. If you want to use variables, you need to use double-quotes.

Another (unrelated) possible problem is your use of tail -n +2. If what you wanted was the 2 last lines of your $input_tsv file, the proper syntax is tail -2 $input_tsv.

Finally, the name of your $input_tsv variable suggests that your input is Tab-separated. If that is the case, you need to tell sqlite that you don't use it's default separator | but a Tab character instead, which can be written as $'\t' in Bash.

So this rewritten version of your script should work :

#!/bin/bash

output_sql_db="$1"
input_tsv="$2"
table_name="$3

tail -2 "$input_tsv" | sqlite3 -separator $'\t' "$output_sql_db" ".import /dev/stdin $table_name"

Derek Mahar · Accepted Answer · 2024-09-05 22:56:17Z

1

Use Bash process subsitution and SQLite 3.44.0 to import a CSV file into an SQLite database table:

#!/bin/bash

# Uncomment to enable statement tracing.
#set -o xtrace

output_sql_db="$1"
input_tsv="$2"
table_name="$3"

# Check whether the table exists in the database.
table_exists="$(sqlite3 $output_sql_db << SQL
  SELECT name
  FROM sqlite_master
  WHERE type = 'table'
    AND name = '$table_name';
SQL
)"

if [ "$table_exists" == "$table_name" ]
then
  skip_count="1"
else
  skip_count="0"
fi

cat "$input_tsv" |
  sqlite3 "$output_sql_db" ".import --csv --skip $skip_count "<(cat)" $table_name"

Note that I replaced /dev/stdin with process substitution command <(cat).

Change --csv to --ascii if $input_tsv is a tab separated file.

edited Sep 5, 2024 at 22:56

answered Sep 5, 2024 at 22:49

Derek Mahar

28.5k46 gold badges128 silver badges182 bronze badges

Collectives™ on Stack Overflow

SQLite: How to simultaneously read data from stdin and table name from variable?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related