使用Powershell将大型CSV大容量导入SQL Server

Question

我偶然看到一篇讨论如何使用Powershell相对较快地批量导入大量数据的帖子。我有一个典型的csv文件，其中大约有500万行以常规方式格式化。

无论我选择导入txt还是csv文件，我都会收到相同的错误信息。玩弄csvdelimiter/firstcolumnnames部分也产生了他们自己的问题。

我花了几个小时试图弄清楚如何让它与我的csv文件一起工作，但无论我如何尝试，我都会得到相同的错误信息。所有字段名称都接受Null，并且它们在表和csv文件之间的各个方面都是相同的。我没有数据库的主键。

# Database variables
$sqlserver = "SERVERNAMEHERE"
$database = "autos"
$table = "AgedAutos"

# CSV variables
$csvfile = "C:\temp\aged.csv"
$csvdelimiter = "',"
$firstRowColumnNames = $true

################### No need to modify anything below ###################
Write-Host "Script 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;"
$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 1) { 
        $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 "Script complete. $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.
[System.GC]::Collect()

下面列出了错误消息。

Exception calling "WriteToServer" with "1" argument(s): "The given value of type String from the data source cannot be converted to 
type date of the specified target column."
At C:\powershell_scripts\batch_csv_import-code1-working-test for auto table.ps1:43 char:3
+         $bulkcopy.WriteToServer($datatable)
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

340000 rows have been inserted in 00:00:03.5156162

我不知道这个错误是什么意思，因为我在谷歌上找不到任何有用的东西。我认为其中一列可能在SQL Server中没有正确列出，但我可能是错的。

请帮我弄清楚这个问题。谢谢。

Answer 1

您正在获取第一列中的所有数据，因为您的$csvdelimiter值不正确。你有：$csvdelimiter = "'，“它应该是：$csvdelimiter = "，”