首页 / 知识

关于sql server：从CSV批量导入SQL

2023-04-17 09:10:00

SQL Bulk import from CSV

我需要将大型CSV文件导入SQL服务器。我正在使用这个：

1
2
3
4
5
6
7
8
9
10

BULK
INSERT CSVTest
FROM 'c:\\csvfile.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\
'
)
GO

问题是我所有的字段都被引号("")包围，所以实际上一行看起来像：

1	"1","","2","","sometimes with comma , inside",""

我可以以某种方式批量导入它们，并告诉SQL使用引号作为字段定界符吗？

编辑：使用'"，"'作为分隔符的问题，如建议的示例中那样：
大多数示例所做的是，他们导入的数据包括第一列中的第一个""和最后一个列中的"最后一个"，然后继续进行处理并将其删除。 first，我的第一列(也是最后一列)是日期时间，并且不允许将" 20080902"作为日期时间导入。

从我一直在阅读的内容来看，我认为FORMATFILE是必经之路，但是文档(包括MSDN)非常无用。

尝试FIELDTERMINATOR='","'

这是一个很好的链接，可以帮助您使用第一个和最后一个引号...看看他如何使用SP的子字符串

http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

我有时使用的另一种技巧是在Excel中打开CSV，然后将您的sql语句写入每行末尾的单元格中。
例如：

1	=concatenate("insert into myTable (columnA,columnB) values ('",a1,"','",b1,"'")")

填充可以为您填充到每一行中。然后只需将输出复制并粘贴到新的查询窗口中即可。

这是古老的做法，但是如果您只需要偶尔执行一次导入，那么可以避免您以"正确"的方式阅读所有晦涩的文档。

尝试使用OpenRowSet。这可用于导入Excel内容。 Excel可以打开CSV文件，因此您只需要找出正确的[ConnectionString] [2]。

[2]：驱动程序= {Microsoft文本驱动程序(* .txt; * .csv)}； Dbq = c：\ txtFilesFolder \;扩展名= asc，csv，tab，txt;

我知道这不是一个真正的解决方案，但我使用虚拟表进行导入，并为所有内容设置了nvarchar。然后，我执行插入操作，该操作去除了"字符并进行了转换。它虽然不漂亮，但是可以完成工作。

id说使用FileHelpers是一个开源库

您需要将CSV文件导入数据表中的文件

然后，您可以使用SQLBulkCopy插入批量行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

USING System;
USING System.Data;
USING System.Data.SqlClient;

namespace SqlBulkInsertExample
{
class Program
{
static void Main(string[] args)
{
DataTable prodSalesData = NEW DataTable("ProductSalesData");

// CREATE COLUMN 1: SaleDate
DataColumn dateColumn = NEW DataColumn();
dateColumn.DataType = TYPE.GetType("System.DateTime");
dateColumn.ColumnName ="SaleDate";

// CREATE COLUMN 2: ProductName
DataColumn productNameColumn = NEW DataColumn();
productNameColumn.ColumnName ="ProductName";

// CREATE COLUMN 3: TotalSales
DataColumn totalSalesColumn = NEW DataColumn();
totalSalesColumn.DataType = TYPE.GetType("System.Int32");
totalSalesColumn.ColumnName ="TotalSales";

// ADD the COLUMNS TO the ProductSalesData DataTable
prodSalesData.Columns.Add(dateColumn);
prodSalesData.Columns.Add(productNameColumn);
prodSalesData.Columns.Add(totalSalesColumn);

// Let's populate the datatable with our stats.
// You can add as many rows as you want here!

// Create a new row
DataRow dailyProductSalesRow = prodSalesData.NewRow();
dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
dailyProductSalesRow["ProductName"] ="Nike";
dailyProductSalesRow["TotalSales"] = 10;

// Add the row to the ProductSalesData DataTable
prodSalesData.Rows.Add(dailyProductSalesRow);

// Copy the DataTable to SQL Server using SqlBulkCopy
using (SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=dbProduct;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
{
dbConnection.Open();
using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
{
s.DestinationTableName = prodSalesData.TableName;

foreach (var column in prodSalesData.Columns)
s.ColumnMappings.Add(column.ToString(), column.ToString());

s.WriteToServer(prodSalesData);
}
}
}
}
}

您可以尝试此代码，如果您愿意的话，它非常棒，
这将从您的代码中删除不需要的分号。
例如，如果您的数据是这样的：" Kelly"，" Reynold"，" kelly@reynold.com"

1
2
3
4
5
6
7
8
9
10
11
12
13

Bulk INSERT test1
FROM 'c:\\1.txt' WITH (
fieldterminator ='","'
,rowterminator='\
')

UPDATE test1
SET name =SUBSTRING (name , 2,len(name))
WHERE name LIKE **'"% '**

UPDATE test1
SET email=SUBSTRING(email, 1,len(email)-1)
WHERE email LIKE **' %" '**

您必须小心使用BCP / BULK INSERT，因为如果报价不一致，即使格式文件(甚至XML格式文件都没有提供此选项)，并且在引号处还有假["]字符，BSP或批量插入都不能很好地处理此问题。开头和结尾，并使用["，"]作为分隔符。从技术上讲，如果没有嵌入的[，]字符，则CSV文件不需要具有["]字符

因此，有时将逗号分隔文件称为喜剧限制文件。

OpenRowSet将在服务器上需要Excel，并且在64位环境中可能会出现问题-我知道在64位Jet中使用Excel会出现问题。

如果文件将来可能与您的期望有所不同，SSIS确实是您的最佳选择。

您需要以编程方式执行此操作，还是一次性拍摄？

使用企业管理器，右键单击"导入数据"，可以选择定界符。

这是一个老问题，所以我写这篇文章是为了帮助那些偶然发现它的人。

SQL Server 2017引入了用于此确切用例的FIELDQUOTE参数。

如果您知道如何将文件解析为DataTable，则建议使用SqlBulkInsert类将其插入SQL Server。

您可以控制输入格式吗？ | (管道)和\ t通常会带来更好的字段终止符。

您也可以使用DTS或SSIS。

是的，Richard K是对的：FIELDTERMINATOR = '","'

有关更多信息，请参见http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file。