running multiple commands through sqlite command line via powershell

delete duplicates from sqlite and shrink.ps1

"DELETE FROM jobs WHERE rowid NOT IN (SELECT min(rowid) FROM jobs GROUP BY JobId)" | .\sqlite3.exe .\jobs2.sqlite
"VACUUM" | .\sqlite3.exe .\jobs2.sqlite

import data into sqlite.ps1

".mode csv",".separator \t",".import marketing_jobs.csv jobs" | .\sqlite3.exe jobs2.sqlite

read data from sqlite and transform to powershell.ps1

# Change encoding to UTF8
[Console]::OutputEncoding = [text.encoding]::utf8

# turn sqlite headers on, read from sqlite database and convert into powershell object
".headers on","Select * from jobs limit 10" | .\sqlite3.exe .\jobs2.sqlite | ConvertFrom-Csv -Delimiter "|" | Format-Table


# If there are linebreaks in the columns, you should do it this way
[Console]::OutputEncoding = [text.encoding]::utf8
$results = (( ".headers on","Select * from jobs limit 10" | & $sqliteExe -separator "`t" -newline "###" $sqliteDb.FullName.Replace("\", "/") ) -join "`r" ) -replace '###',"`r`n" | ConvertFrom-Csv -Delimiter "`t"
$results | Export-Csv -Encoding UTF8 -Path "test.csv" -NoTypeInformation 
$results | Format-Table
[Console]::OutputEncoding = [text.encoding]::Default

# Or use this function

Function Read-Sqlite {

    param(
         [Parameter(Mandatory=$true)] $query
        ,[Parameter(Mandatory=$true)] $sqliteDb
        ,[Parameter(Mandatory=$true)] $sqliteExe
    )

    $separator = "`t"
    $newline = "###"

    [Console]::OutputEncoding = [text.encoding]::utf8
    $results = (( ".headers on", $query | & $sqliteExe -separator $separator -newline $newline $sqliteDb.Replace("\", "/") ) -join "`r" ) -replace $newline,"`r`n" | ConvertFrom-Csv -Delimiter $separator
    [Console]::OutputEncoding = [text.encoding]::Default

    return $results

}

read_via_tmp_file.ps1

################################################
#
# PREPARATION / ASSEMBLIES
#
################################################

# Load scriptpath
if ($MyInvocation.MyCommand.CommandType -eq "ExternalScript") {
    $scriptPath = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
} else {
    $scriptPath = Split-Path -Parent -Path ([Environment]::GetCommandLineArgs()[0])
}

cd $scriptPath

################################################
#
# LOAD DATA
#
################################################

$sqliteDB = ".\kontakte.db"
$tmpfile = "$( $scriptPath )\$( [datetime]::UtcNow.ToString("yyyyMMddHHmmss") ).csv"

# the sql statement is loaded in this case from a text file, but it could also be hardcoded in here
$sqlStatement = Get-Content -Path "select_statement.sql"

# put commands together
$cliCommands = @(
    ".headers on"
    ".mode csv"
    ".separator ""\t"""   
    ".once $( $tmpfile -replace "\\","/" )"
    $sqlStatement
)

# let sqlite create an export
$cliCommands | .\sqlite3.exe $sqliteDB

change encoding in powershell console