将大型CSV文件导入到SQL服务器失败

Question

我读过一篇名为“使用PowerShell C＃或VB Net高效地将大型CSV导入SQL Server”的文章。我正在使用文章中的PowerShell脚本，我有3个我不明白的错误。

我创建了只有21个条目的csv文件的小版本。我有一个SQL Server表，我需要从CSV文件导入数据。 CSV文件将在夜间从Oracle数据库中提取，并将其导入到另一个程序使用的SQL Server表中。

我拿了脚本，并用它对5列的21行数据集，所有这些都是10个字符宽。在最后一列数据中不能输入，这就是为什么我将它包含在数据集中的原因。我使用SSMS在数据库中创建了一个表CMWBACCT，我在我的机器上创建了CCBCMWB，以及我试图导入数据的表。现实生活中的CSV文件约为150万行，对于我工作的3个表格中的每一个。我使用的程序是文章中的程序，但只是修改了一个表格。

第一个错误是在脚本的第38行：

$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter) 

其中PowerShell不喜欢-First

获取内容：参数无法找到匹配的参数名称“第一” 。 在C：\ CMWBTST \输入-CSVtoSQLA.ps1：38字符：40分 + $列=（获取内容$ csvfile - 第一< < < < 1）.Split（$ csvdelimiter） + CategoryInfo：InvalidArgument：（： ）[获取内容]，ParameterBin dingException + FullyQualifiedErrorId：NamedParameterNotFound，Microsoft.PowerShell.Comm ands.GetContentCommand

第二误差我明白甚至更少它发生在线路48上：

$null = $datatable.Rows.Add($line.Split($csvdelimiter)) 

并且发生21倍

异常调用“添加”和“1”的说法（S）：“输入阵列比在该表的列的n个 棕土长。” 在C：\ CMWBTST \输入-CSVtoSQLA.ps1：48个字符：32 + $ NULL = $ datatable.Rows.Add < < < <（$ line.Split（$ csvdelimiter）） + CategoryInfo：NotSpecified：（： ）[]，MethodInvocationException + FullyQualifiedErrorId：DotNetMethodException

该数组包含其中列的数目相匹配表中的，所以我很困惑五个字段。

最后错误的程序在65行不太愿意：与错误

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

的我缺少的方法Dispose

方法调用失败，因为[System.Data.SqlClient.SqlBulkCopy]不包含名为“Dispose”的方法。 在C：\ CMWBTST \ Import-CSVtoSQLA.ps1：65 char：37 + $ bulkcopy.Close（）; $ bulkcopy.Dispose < < < <（） + CategoryInfo：InvalidOperation：（处置：字符串）[]，RuntimeEx ception + FullyQualifiedErrorId：MethodNotFound

在哪里被定义该方法应该？

下面是我使用的脚本：

#################################################### 
#             # 
# PowerShell CSV to SQL Import Script    # 
#             # 
#################################################### 

# Database variables 
$sqlserver = "EMRICHRT3400" 
$database = "CCBCMWB" 
$table = "CMWBACCT" 

# CSV variables 
$csvfile = "C:\CMWBTST\CMWBACCT21.csv" 
$csvdelimiter = "," 
$FirstRowColumnNames = $false 

################### 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 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 "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() 

和使用

0000050590,1390457725,2013-01-02,2016-03-07,2016-06-06 
0000100491,8156952728,2008-12-16,2016-04-01,2016-07-01 
0000120293,0000120000,2006-11-15,2016-02-18,2016-05-19 
0000220299,0000220000,2006-10-11,2016-04-15,2016-07-15 
0000340706,0000340000,2009-03-12,2016-02-24,2016-05-25 
0000420610,9760303504,2012-05-16,2016-04-15,2016-07-15 
0000500613,0000500000,2006-12-06,2016-03-01,2016-06-03 
0000740524,0000740000,2006-10-18,2016-04-25,2016-07-25 
0001030634,0001030000,2006-11-16,2016-02-18,2016-05-19 
0001120239,0001120000,2006-12-14,2016-03-17,2016-06-17 
0001150542,0001150000,2006-11-16,2016-02-18,2016-05-19 
0001220144,0001220000,2006-10-10,2016-04-15,2016-07-15 
0001240146,2947199958,2011-09-26,2016-04-07,2016-07-08 
0001520257,7724424991,2012-12-17,2016-04-15,2016-07-15 
0001530858,0001530000,2006-12-20,2016-03-22,2016-06-27 
0001620362,0001620000,2006-10-16,2016-04-20,2016-07-20 
0001700965,0001700000,2006-12-04,2016-03-03,2016-06-08 
0001730768,0001730000,2006-10-10,2016-04-07,2016-07-07 
0001910075,6494797239,2016-05-17,, 
0001920876,0001920000,2006-10-31,2016-05-03,2016-08-03 
0002140587,5733138981,2013-02-01,2016-04-14,2016-07-14 

任何帮助将不胜感激

Answer 1

我建议使用进口-CSV PowerShell中的CSV（文本）文件命令从CSV文件导入数据，因为它是处理csv格式文件的官方方法。该cmdlet的语法非常简单。

Import-Csv -Path target.csv -Delimiter , 

要将数据导入数据库，可以在PowerShell中使用SQL Server PowerShell模块而不是ADO.NET。

Invoke-Sqlcmd -Query "<sql statements>" 

你可以看到这篇文章How to use SQL Server PowerShell Module to import data from CSV file下载样本