CSV Import zu SQL Server mit Powershell

$server = 'server.domain.tld'  
$database = 'mydb'  
$table = 'MyTable'  
$username = 'USER'  
$password = 'Password'  

$creds = (new-Object PSCredential($username,(ConvertTo-SecureString $password -AsPlainText -Force)))

$dt = Read-SqlTableData -ServerInstance $server -DatabaseName $database -TableName $table -OutputAs DataTable -TopN 1 -Credential $creds
$dt.Rows.Clear()
$csv = Import-CSV "C:\test.csv" -Delimiter ";"  

$cols =  $csv | gm -MemberType NoteProperty | select -Expand Name
# fill datatable
foreach($row in $csv){
    # create datarow 
    $dr = $dt.NewRow()
    # fill row with data from columns
    $cols | %{$dr[$_] = $row.$_}
    # add row
    $dt.Rows.Add($dr)
}
$dt | Write-SqlTableData -ServerInstance $server -DatabaseName $database -TableName $table -Credential $creds

<#
    SQL - Insert CSV Files into Database via Datatable
#>

# Folder with csv data files
$folder = 'D:\csv_files'  
# File extension
$extension = '*.csv'  
# Database Servername
$server = 'myserver.domain.tld'  
# Databasename
$database = 'MyDataBase'  
# Tablename
$table = 'demotable'  
# Credentials
$username = 'dbsqluser'  
$password = 'Passw0rd'  

try{
    # connection object
    $conn = new-object System.Data.SqlClient.SqlConnection
    # connection string
    $conn.ConnectionString = “Server=$server;Database=$database;Integrated Security=False;UID=$username;PWD=$password”
    # open connection
    $conn.Open()
    # create data adapter
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter("SELECT TOP 1 * FROM $table",$conn)  
    # create datatable to hold insert batches
    $dt = New-Object System.Data.DataTable
    # update schema of datatable to match the table in the database
    $adapter.FillSchema($dt,[System.Data.SchemaType]::Source)
    # auto create insert/update commands
    $cmdbuilder = New-Object System.Data.SqlClient.SqlCommandBuilder($adapter)
    # enumerate all files
    foreach($file in (Get-ChildItem $folder -Filter $extension -File)){
        # import csv data
        $csv = Import-CSV $file.Fullname -Delimiter ";"  
        # extract column names
        $cols =  $csv | gm -MemberType NoteProperty | select -Expand Name
        # fill datatable
        foreach($row in $csv){
            # create datarow 
            $dr = $dt.NewRow()
            # fill row with data from columns
            $cols | %{$dr[$_] = $row.$_}
            # add row
            $dt.Rows.Add($dr)
        }
        # execute update
        $cnt = $adapter.Update($dt)
        write-host "Inserted $cnt number of rows from '$($file.FullName)' into '$table' in '$database'." -F Green  
        [void]$dt.Rows.Clear()
    }
}catch{
    # error occured
    Write-host $_.Exception.Message -F Red
}finally{
    # cleanup
    $adapter.Dispose()
    $dt.Dispose()
    $cmdbuilder.Dispose()
    $conn.Close()
}