How to insert data from JSON file into SQLite Database from Batch file

  batch-file, jq, sqlite, windows

i would like to insert data from a JSON-File into a SQLite-Databse with an existing table from a batch file.

First i filter the flawed JSON-File with jq afterwards i open the database and try to insert the data, but after opening the database the batch file stops at receiving commands from sqlite3.

This how the batch file looks like:

jq -r ".[]" D:pathjson_filesomedata.json > D:pathjson_filesomedata_new.json

sqlite3 "C:pathDB_Folderdb1.sqlite" 

INSERT INTO mytable (col1, col2, col3, col4) SELECT json_extract(value, '$.col1'), json_extract(value, '$.col2'), json_extract(value, '$.col3'), json_extract(value, '$.col4') FROM json_each(readfile('D:pathjson_filesomedata_new.json')); 

But after executing sqlite3 "C:pathDB_Folderdb1.sqlite" the batch file stops at receiving commands for sqlite3 and the INSERT-Statement is ignored.

If i execute the commands directly in the command line everything works fine.

I tried to create a file like here with the INSERT-Statement but mytable remains empty.

Can anybody help me?

Btw it is necessary to input the data automatically without any users actions.

Thanks for help in advance.

Source: Windows Questions

LEAVE A COMMENT