CODEWITHSUNDEEP
powershellcsvsqlite
StevenMMK
StevenMMK

Reputation: 31

Powershell - Export SQLite table to csv

Not sure if this is possible but I'd appreciate any input given. I want to be able to export a SQLite database (*.sqlite3) table to a csv file.

Here's my code:

$DB_PATH = "C:\Users\McLuhan\Google Drive\Project\testing\mysite\mysite\db.sqlite3"

#Importing the SQLite assemblies
Add-Type -Path "C:\Program Files\System.Data.SQLite\2010\bin\System.Data.SQLite.dll"

#Connect to DB
$con = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$con.ConnectionString = "Data Source=$DB_PATH"
$con.Open()

#Create query object
$sql = $con.CreateCommand()
$sql.CommandText = "SELECT * FROM identities_identity"
$adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $sql
$data = New-Object System.Data.DataSet
[void]$adapter.Fill($data)

#Show tables 
$data.Tables #| export data to csv file ????

I can get the data from the table but it comes in a baseType of "System.Data.InternalDataCollectionBase". I would like to be able to convert this to a baseType of "System.Array". I believe this would allow me to export to csv.

Any help is greatly appreciated. Thanks!

Upvotes: 1

Views: 2847

Answers (1)

StevenMMK
StevenMMK

Reputation: 31

Just figured out how to export the table. By appending the following piece of code to the original script, it will export to csv file correctly:

#Show tables 
$table = $data.Tables 

#Export using loop
foreach($t in $table){
    $t | Export-Csv c:\temp\test3.csv -NoTypeInformation -Append 
}

Thanks. Hope this might help someone having similar issues :)

Upvotes: 1

Related Questions