将csv文件导入Server数据库表的Powershell函数

Question

我创建了一个PowerShell函数，它可以从.csv文件中批量复制数据(第一行是标题)，并将数据插入到Server数据库表中。

请看我的代码：

function BulkCsvImport($sqlserver, $database, $table, $csvfile, $csvdelimiter, $firstrowcolumnnames) {
    Write-Host "Bulk Import Started." 
    $elapsed = [System.Diagnostics.Stopwatch]::StartNew()  
    [void][Reflection.Assembly]::LoadWithPartialName("System.Data") 
    [void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient") 

    # 50k worked fastest and kept memory usage to a minimum 
    $batchsize = 50000 

    # Build the sqlbulkcopy connection, and set the timeout to infinite 
    $connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;" 

    # Wipe the bulk insert table first
    Invoke-Sqlcmd -Query "TRUNCATE TABLE $table" -ServerInstance $sqlserver -Database $database

    $bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock) 
    $bulkcopy.DestinationTableName = $table 
    $bulkcopy.bulkcopyTimeout = 0 
    $bulkcopy.batchsize = $batchsize 

    # Create the datatable, and autogenerate the columns. 
    $datatable = New-Object System.Data.DataTable 

    # Open the text file from disk 
    $reader = New-Object System.IO.StreamReader($csvfile) 
    $columns = (Get-Content $csvfile -First 1).Split($csvdelimiter) 

    if ($firstrowcolumnnames -eq $true) { $null = $reader.readLine() } 

    foreach ($column in $columns) {  
        $null = $datatable.Columns.Add() 
    } 

    # Read in the data, line by line 
    while (($line = $reader.ReadLine()) -ne $null)  { 
        $null = $datatable.Rows.Add($line.Split($csvdelimiter)) 

        $i++; 
        if (($i % $batchsize) -eq 0) {  
            $bulkcopy.WriteToServer($datatable)  
            Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())." 
            $datatable.Clear()  
        }  
    }  

    # Add in all the remaining rows since the last clear 
    if($datatable.Rows.Count -gt 0) { 
        $bulkcopy.WriteToServer($datatable) 
        $datatable.Clear() 
    } 

    # Clean Up 
    $reader.Close(); 
    $reader.Dispose() 

    $bulkcopy.Close(); 
    $bulkcopy.Dispose() 

    $datatable.Dispose() 

    Write-Host "Bulk Import Completed. $i rows have been inserted into the database." 
    # Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())" 
    # Sometimes the Garbage Collector takes too long to clear the huge datatable. 
    $i = 0
    [System.GC]::Collect()
}

不过，我希望修改上面的内容，以便.csv文件中的列名与Server数据库表中的列名相匹配。他们应该是一样的。目前，数据正在导入到不正确的数据库列中。

我是否可以得到一些协助，作为我需要做的，以修改上述功能，以实现这一点？

Answer 1

我会使用现有的开源解决方案：

进口-DbaCsv- dbatools.io Import-DbaCsv.ps1 高效地将非常大(和很小)的CSV文件导入Server。 导入-DbaCsv利用.NET的超高速SqlBulkCopy类将CSV文件导入Server。

参数：

-ColumnMap

默认情况下，大容量复制尝试自动化列。当它不按需要工作时，这个参数将有所帮助。

PS C:\> $columns = @{
>> Text = 'FirstName'
>> Number = 'PhoneNumber'
>> }
PS C:\> Import-DbaCsv -Path c:\temp\supersmall.csv 
        -SqlInstance sql2016 -Database tempdb -ColumnMap $columns 
        -BatchSize 50000 -Table table_name -Truncate

CSV列'Text‘被插入到SQL列'FirstName’中，CSV列号插入到SQL列'PhoneNumber‘中。所有其他列都将被忽略，因此为null或默认值。