How to import CSV file using PowerShell?

Question

I want to import a CSV File (comma delimited with double quote) into SQLite using PowerShell script. I tried:

echo ".import export.csv mytable" | sqlite3.exe

I get this error:

export.csv:327: unescaped " character

I get this error for all lines. On SQLite's command line shell same command works:

sqlite > .import export.csv mytable

How can I make this command work using a PowerShell script?

Answer 1

This works for me in both PowerShell 5.1 and v7.0.

$params = @"
.mode csv
.separator ,
.import export.csv mytable
"@

$params | .\sqlite3.exe mydatabase.db

Answer 2

The following single command line works in both

• cmd.exe (version 10.0.x.x via ver) and
• powershell.exe (version 5.1.x.x via $PSVersionTable)

.\sqlite3.exe my.db ".import file1.csv table1 --csv"

This loads the contents of csv file file1.csv into table table1 within the sqlite database file my.db. The --csv flag will import the file and create the table structure based on the header column names in that file, and will approximately infer the data types.

You can import multiple files this way. i.e.

.\sqlite3.exe my.db ".import file1.csv table1 --csv" ".import file2.csv table2 --csv"

You can chain commands together to immediately open the database for querying by appending ; .\sqlite3.exe my.db.

i.e.

.\sqlite3.exe my.db ".import file1.csv table1 --csv" ".import file2.csv table2 --csv; .\sqlite3.exe my.db"