CSV to MSSQL Express

Soms is de gratis versie van Microsoft SQL Server (MSSQL Express) wel eens erg irritant. Je kan bijvoorbeeld niet automatisch een CSV bestand importeren in de database. Gelukkig zijn er scripts die je daarmee kunnen helpen. Vroeger zou ik een batch-file gemaakt hebben die sqlcmd.exe aanriep. Nu kan ik nog veel meer met Powershell en dat staat overal op Windows geïnstalleerd tegenwoordig. Mijn script download automatisch een csv bestand, past het aan zodat het beter in de database past en upload het en voert daarna nog een ‘stored procedure’ uit om wat nabewerkingen te doen. Het is geautomatiseerd omdat je het via scheduled tasks kan inplannen.

Nu ben je vast nieuwsgierig naar het script, hieronder kan je het zien. :

<# set the desired variables #>
$sqlServer = "<servernaam>"
$url = "<url>/getCSV/null/KL"
$file = "C:Tempexport.csv"

<# clean-up files #>
If (Test-Path $file)
    { Remove-Item $file }

Import-Module "sqlps" -DisableNameChecking
$sqlQuery = "TRUNCATE TABLE [inventarisaties].[dbo].[export]"
Invoke-SQLcmd $sqlQuery -ServerInstance $sqlServer

<# download action #>
$webclient = New-Object System.Net.WebClient
$webclient.DownloadFile($url,$file)

<# clean-up downloaded file #>
(Get-Content $file) | Foreach-Object {$_ -replace """", ""} | Set-Content $file

<# convert data to sql query and execute on server #>
$data = Import-Csv -Path c:tempexport.csv -Delimiter ";" -header("Kolom1","Kolom2","Kolom3","Kolom4")
$sqlHeader = "INSERT INTO [inventarisaties].[dbo].[export]
                          ([Kolom1], [Kolom2], [Kolom3], [Kolom4])"

ForEach ($line in $data) {
    if ($line.workstation) {
        $kolom1 = "'" + $line.Kolom1 + "'"
        $kolom2 = "'" + $line.Kolom2" + "'"
        $kolom3 = "'" + $line.Kolom3 + "'"
        $kolom4 = "'" + $line.Kolom4 + "'"

        $sqlValues = " VALUES (" + $kolom1 + ", " + $kolom2 + ", " + $kolom3 + ", " + $kolom4 + ")"
        $sqlQuery = $sqlHeader + $sqlValues
        Invoke-SQLcmd $sqlQuery -ServerInstance $sqlServer
    }
}

<# run procedure to update main tables #>
$sqlQuery = "[inventarisaties].[dbo].[process_NO2A_import]"
Invoke-SQLcmd $sqlQuery -ServerInstance $sqlServer