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?

You may not need the echo (which is really an alias for Write-Output) in PowerShell; try ".import export.csv mytable" | sqlite3.exe. — Jeff Zeitlin
– Jeff Zeitlin, Commented Apr 28, 2017 at 13:55
Is the "327" in the error message supposed to represent a line number in the file, or is it an error code that sqlite is reporting? If it is a line number, how many lines does the file have - and how many lines are getting imported when you do it manually? — Jeff Zeitlin
– Jeff Zeitlin, Commented Apr 28, 2017 at 14:02
If I remember correctly you need to run it like this: sqlite3 yourdatabase.db < ".import export.csv mytable" — henrycarteruk
– henrycarteruk, Commented Apr 28, 2017 at 15:30

qyb2zm302 · Accepted Answer · 2022-11-04 20:05:39Z

3

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

edited Nov 4, 2022 at 20:05

qyb2zm302

6,4592 gold badges19 silver badges17 bronze badges

answered Oct 18, 2020 at 2:49

d3Xt3r

2031 gold badge2 silver badges8 bronze badges

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

Comments

qyb2zm302 · Accepted Answer · 2022-11-14 21:00:21Z

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"

Prasoon Karunan V · Accepted Answer · 2017-05-03 04:24:51Z

0

what about sqlite3.exe ".import export.csv mytable".

You can check the documentation of sqlite3.exe to use it not being in its shell, how to pass parameter to sqlite3.exe.

You can try below line

Invoke-Expression 'sqlite3 yourdatabase.db < ".import export.csv mytable"'

edited May 3, 2017 at 4:24

answered May 2, 2017 at 4:55

Prasoon Karunan V

3,0653 gold badges15 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to import CSV file using PowerShell?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related