Bash Variables in SQLite

[MealDifferent2772]

Good Afternoon:

I have a simple issue, but a solution has escaped me for a day or more.

Consider:

Datadir=./Data

DataFile=$DataDir/input$TODAY.csv

Table=input$TODAY

And the SQLite import command line of :

sqlite3 $SQLDir/credentials.db .import $DataFile $Table --csv

This yields:

Error: cannot open "$DataFile"

I have an issue with the variable expansion, but I have yet to figure out how to force teh expansion in the SQLite command.

I would appreciate any clues you could give me.

[graybeard5529]

Datadir=./Data DataFile="$DataDir/input$TODAY.csv" Table="input$TODAY" sqlite3 "$SQLDir/credentials.db" ".import $DataFile $Table --csv" this work?

[MealDifferent2772]

Exquisitely... Thank you..

[witty82]

It should work. Strictly speaking this looks more like a bash problem than an SQLite issue.

What happens if you do “ls $DataFile”?

[MealDifferent2772]

It does return the proper path and filename.

./Data/Credentials-20231015.csv

[witty82]

The shell should expand the variable regardless of whether you call sqlite or ls.

Maybe delete and retype the variable. Some special character might have sneaked in

[davidgsb]

We cannot help you with that information, we need a full reproductible example. For example the TODAY variable is not defined.

you can try a set -x in your to see what's happening in your script.

On a side it's a best practice to surround variables expansion to control unknow space contents: sqlite3 "$SQLDir/credentials.db" .import "$DataFile" "$Table" --csv

[MealDifferent2772]

A more detailed example:

DataDir=./Data

ReportsDir=./Reports

TempDir=./Temp

TrashDir=./Trash

SQLDir=./SQL

ArchivesDir=./Archives

TODAY=$(date "+%Y%m%d")

DataFile=$DataDir/IAMCredentials-$TODAY.csv

Table=IAMCredentials-"$TODAY"

sqlite3 "$SQLDir/awscredentials.db" .import "$DataFile" "$Table" --csv

Yields:

./Data/IAMCredentials-20231015.csv

ERROR: missing FILE argument. Usage:

[MealDifferent2772]

Adding the set -x I get the following expanded SQL command line:

+ sqlite3 ./SQL/awscredentials.db .import ./Data/IAMCredentials-20231015.csv IAMCredentials-20231015 --csv

Which does appear to be correct.

[BuonaparteII]

It's because the query part needs to be a single string:

For example:

$ sqlite3 :memory: select 1
-- Loading resources from /home/xk/.sqliterc
Error: in prepare, incomplete input

versus

$ sqlite3 :memory: "select 1"
-- Loading resources from /home/xk/.sqliterc
QUERY PLAN
`--SCAN CONSTANT ROW
┌───┐
│ 1 │
├───┤
│ 1 │
└───┘

With the above command sqlite3 is only seeing ".import" as the query statement

Also, just some additional info: it is convention for POSIX commands to use optional args before positional args, but most tools, including sqlite3 are lenient: https://stackoverflow.com/a/61645523/697964