SQL Server BCP批量插入管道，以文本限定符格式文件分隔 [英] SQL Server BCP Bulk insert Pipe delimited with text qualifier format file
问题描述
我有一个csv文件，该文件是垂直管道，每列分隔，文本限定符为.
I have a csv file which is vertical pipe delimited with every column also with a text qualifier of ".
我一直在尝试尝试使BCP格式文件正常工作，但是没有运气.
I have been trying for ages to try and get the BCP format file to work, but no luck.
我有以下登台表:
[ID] [VARCHAR](100) NULL,
[SUB_ID] [NUMERIC](18, 0) NULL,
[CODE1] [VARCHAR](20) NULL,
[CODE2] [NUMERIC](18, 0) NULL,
[DATE] [DATE] NULL
csv中的数据:
"ID"|"SUB_ID"|"CODE1"|"CODE2"|"DATE"
"HAJHD87SADAD9A87SD9ADAS978DAA89D09AS"|"7510"|"N04FY-1"|"359420013"|"08/08/2018"
格式化文件
14.0
5
1 SQLCHAR 0 0 '"|"' 1 ID ""
2 SQLCHAR 0 0 '"|"' 2 SUB_ID ""
3 SQLCHAR 0 0 '"|"' 3 CODE1 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 0 '"|"' 4 CODE2 ""
5 SQLCHAR 0 0 '"\n"' 5 DATE ""
当我尝试使用以下SQL语句执行时:
When I try to execute using the following SQL statement:
BULK INSERT [dbo].[TEST]
FROM 'G:\DATA\TABLE.csv'
WITH (FIRSTROW = 2,
FORMATFILE = 'G:\DATA\TEST.fmt')
我收到此错误
4866级消息，第16级，状态8，第1行
批量加载失败.数据文件中第1行第1列的列太长.请验证是否正确指定了字段终止符和行终止符.
Msg 4866, Level 16, State 8, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
消息7301，第16级，状态2，第1行
无法从链接服务器(null)"的OLE DB访问接口"BULK"获得所需的接口("IID_IColumnsInfo").
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".
我无法找出问题出在哪里.是数据类型不匹配，还是我的FIELDTERMINATOR和ROWTERMINATOR不正确?任何想法都会受到欢迎，我已经尝试了很多组合.
I can't work out where it is going wrong. Is it a data type mismatch or are my FIELDTERMINATOR and ROWTERMINATOR incorrect? Any ideas will be greatly received, I've tried so many combinations.
推荐答案
首先，BCP程序仅将双引号识别为定界符的容器.因此，使用单引号会导致错误.
First, the BCP program only recognizes the doublequote as the container of the delimiter. So, using the single quote is causing an error.
第二，由于您要指定分隔符"|"包含BCP要求您用来包围定界符的双引号字符，您必须使用转义字符来使BCP程序忽略要用作定界符的引号.转义字符是反斜杠字符.所以...
Second, since the delimiter you want to specify "|" includes the doublequote character that BCP requires you to use to use to enclose your delimiter, you have to use the escape character to get the BCP program to ignore the quotes you want to use as delimiters. The escape character is the backslash character. So...
代替...，使用..."\" | \"
Instead of "|"... use... "\"|\""
这将告诉BCP忽略以反斜杠开头的双引号，并将其视为其他任何字符.
This will tell BCP to ignore the doublequots preceded by a backslash and just treat them as any other character.
第三，您必须说明第一个具有双引号的字段. "|"我上面提到的终止符不会解释第一个字段在每行开头的双引号.
Third, you must account for the first field that has a preceding doublequote. The "|" terminator I mentioned above wont account for the opening doublequote at the beginning of each line for the first field.
要处理此问题，必须在格式文件中添加一个虚拟"字段，并将其终止符分配为\(或在格式文件中实际为"\"").然后，由于文件中的字段现在比表中的字段多，因此必须偏移列编号，以告诉BCP跳过由文件中第一个双引号终止的新字段
To handle that you must add a "dummy" field to your format file and assign it's terminator as \" (or actually "\"" in the format file). Then, since you now have one more field in the file than you have in the table, you must offset your column numbering to tell BCP skip this new field that is terminated by the first doublequote in the file
最后，最后一个字段不是仅由换行符"\ n"终止.它也被称为双引号(不包括管道字符).因此，我们必须自定义最终的字段终止符(实际上是行/行终止符).像这样的"\" \ n".
Last, the last field is not terminated by just a newline character "\n". It is also termed by a doublequote (no pipe character included). So, we must customimze the final field terminator (which is actually the line/row terminator). Like this "\"\n".
您的格式文件现在将如下所示:
Your format file will now look like this:
14.0
5
1 SQLCHAR 0 0 "\"" 0 dummy_field ""
2 SQLCHAR 0 0 "\"|\"" 1 ID ""
3 SQLCHAR 0 0 "\"|\"" 2 SUB_ID ""
4 SQLCHAR 0 0 "\"|\"" 3 CODE1 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 0 "\"|\"" 4 CODE2 ""
6 SQLCHAR 0 0 "\"\n" 5 DATE ""
我希望能帮上忙.
这篇关于SQL Server BCP批量插入管道，以文本限定符格式文件分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
