在SQL Server中带有文本限定符的批量插入

table

关注

发信

关注(28)粉丝(399)

在SQL Server中带有文本限定符的批量插入

本文介绍了在SQL Server中带有文本限定符的批量插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试从CSV文件的表测试中批量插入几条记录，

 创建表Level2_import 
（wkt varchar（max），
区域VARCHAR（40），
）
 
 
批量
 INSERT level2_import 
从'D：\test.csv'
与
（
 FIRSTROW = 2，
 FIELDTERMINATOR ='，'，
 ROWTERMINATOR ='\n'
）

批量插入代码应去除第一行并将数据插入表中。它摆脱了第一行，但是在定界符部分感到困惑。第一列是wkt，该列的值用双引号引起来，并且值中包含逗号。

所以我想我想问的是是否有办法告诉 BULK INSERT

CSV文件如下所示：

  MULTIPOLYGON（（（（60851.286135090661 510590.66974495345,60696.086128673756 510580.56976811233,60614.7860844061 510579.36978015327,60551.486015895614））））''，123123.22

$ class = h2_lin>解决方案

您需要使用格式文件来实现批量插入的文本限定符。本质上，您将需要告知批量插入操作每个字段中可能存在不同的定界符。

创建一个名为 level_2.fmt的文本文件并保存。

  11.0 
 2 
 1 SQLCHAR 0 8000 \， 1 wkt SQL_Latin1_General_CP1_CI_AS 
 2 SQLCHAR 0 40 \r\n 2区域SQL_Latin1_General_CP1_CI_AS

第一行， 11.0引用您的SQL版本，第二行显示您的表[level2_import]有两列，此后的每一行将描述一列，并遵循以下格式：

[源列号] [DataType] [最小大小] [最大大小] [分隔符模式] [目标列号] [目标列名] [数据库的区分大小写]

一次您已经创建了该文件，可以使用以下批量插入语句读取数据：

 批量插入level2_import 
从'D：\test.csv' 
和
（
 FIRSTROW = 2，
 FORMATFILE ='D：\level_2.fmt'
）；

请参阅此，以获取格式文件的详细说明。

I am trying to bulk insert few records in a table test from a CSV file ,

 CREATE TABLE Level2_import
 (wkt varchar(max),
 area VARCHAR(40),
 )


BULK
 INSERT level2_import
 FROM 'D:\test.csv'
 WITH
 (
 FIRSTROW = 2,
 FIELDTERMINATOR = ',',
 ROWTERMINATOR = '\n'
 )

The bulk insert code should rid of the first row and insert the data into the table . it gets rid of first row alright but gets confused in the delimiter section . The first column is wkt and the column value is double quoted and has comma within the value .

So I guess I question is if there is a way to tell the BULK INSERT that the double quoted part is one column regardless of the comma within it ?

the CSV file looks like this ,

 "MULTIPOLYGON (((60851.286135090661 510590.66974495345,60696.086128673756 510580.56976811233,60614.7860844061 510579.36978015327,60551.486015895614)))", 123123.22

解决方案

You need to use a 'format file' to implement a text qualifier for bulk insert. Essentially, you will need to teach the bulk insert that there's potentially different delimiters in each field.

Create a text file called "level_2.fmt" and save it.

11.0
2
1   SQLCHAR   0  8000   "\","      1     wkt         SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR   0  40   "\r\n"      2     area         SQL_Latin1_General_CP1_CI_AS

The first line, "11.0" refers to your version of SQL. The second line shows that your table, [level2_import], has two columns. Each line after that will describe a column, and obeys the following format:

[Source Column Number][DataType][Min Size][Max Size][Delimiter pattern][Destination Column Number][Destination Column Name][Case sensitivity of database]

Once you've created that file, you can read in your data with the following bulk insert statement:

BULK INSERT level2_import
FROM 'D:\test.csv'
WITH
(
  FIRSTROW = 2,
  FORMATFILE='D:\level_2.fmt'
);

Refer to this blog for a detailed explanation of the format file.

这篇关于在SQL Server中带有文本限定符的批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-30 02:12