sql-server - Powershell函数将csv文件导入SQL Server数据库表

Question

我创建了一个 PowerShell 函数，用于从 .csv 批量复制数据。文件(第一行是标题)，并将数据插入到 SQL 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 中的列名文件与 SQL Server 数据库表中的列名匹配。它们应该是相同的。目前正在将数据导入到不正确的数据库列中。

我可以得到一些帮助，因为我需要做些什么来修改上述功能以实现这一目标？

Answer 1

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

Import-DbaCsv - dbatools.io

Import-DbaCsv.ps1

Efficiently imports very large (and small) CSV files into SQL Server.

Import-DbaCsv takes advantage of .NET's super fast SqlBulkCopy class to import CSV files into SQL Server.

---

Parameters:

-ColumnMap

By default, the bulk copy tries to automap columns. When it doesn't work as desired, this parameter will help.

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

The CSV column 'Text' is inserted into SQL column 'FirstName' and CSV column Number is inserted into the SQL Column 'PhoneNumber'. All other columns are ignored and therefore null or default values.