我正在尝试使用XML格式化文件将CSV文件批量插入SQL Server 2017:
<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="Col_0" xsi:type="CharTerm" MAX_LENGTH="100" TERMINATOR=","/>
<FIELD ID="Col_1" xsi:type="CharTerm" MAX_LENGTH="100" TERMINATOR=","/>
<FIELD ID="Col_2" xsi:type="CharTerm" MAX_LENGTH="3" TERMINATOR=","/>
<FIELD ID="Col_3" xsi:type="CharFixed" Length="19" TERMINATOR=","/>
<FIELD ID="Col_4" xsi:type="CharFixed" Length="19" TERMINATOR=","/>
<FIELD ID="Col_5" xsi:type="CharFixed" Length="53" TERMINATOR=","/>
<FIELD ID="Col_6" xsi:type="CharFixed" Length="10" TERMINATOR=","/>
</RECORD>
<ROW>
<COLUMN SOURCE="Col_0" NAME="NAME" xsi:type="SQLVARCHAR"/>
<COLUMN SOURCE="Col_1" NAME="MATCHNAME" xsi:type="SQLVARCHAR"/>
<COLUMN SOURCE="Col_2" NAME="CHROMOSOME" xsi:type="SQLVARCHAR"/>
<COLUMN SOURCE="Col_3" NAME="START_LOCATION" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="Col_4" NAME="END_LOCATION" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="Col_5" NAME="CENTIMORGANS" xsi:type="SQLFLT4"/>
<COLUMN SOURCE="Col_6" NAME="MATCHING_SNPS" xsi:type="SQLSMALLINT"/>
</ROW>
</BCPFORMAT>
当我执行这个时,我收到以下错误消息:
执行期间出错 37000(4855)[Microsoft][ODBC SQL Server Driver][SQL Server]格式文件 "c:\temp\FormatFile.xml" 中的第 2 行:意外元素"BCPFORMAT"。
我不明白为什么无法识别 BCPFORMAT 元素。XML根据Altova XMYSpy正确格式化。
执行上传的代码为:
bulk insert FF_ChromBrwResults_20190124 from 'c:temptemp.csv'
with (FIELDTERMINATOR = ',',FIRSTROW =1,ROWTERMINATOR = 'n'
,KEEPIDENTITY,CODEPAGE ='OEM',KEEPNULLS, FORMATFILE = 'c:tempFormatFile.xml' )
CSV 包含许多采用此格式的行,以换行符结尾:
名称1,名称2,1,48745397,54260005,2.84,1000
我尝试从 .NET 代码和 ODBC 连接正常工作的 AQT 中执行此操作;这两种方法都产生了相同的错误。
发生此错误是因为 xmlns 包含 HTTPS 的网址正确是 HTTP。
不正确 = <BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
正确 = <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
您的 XML 文件还需要一次修改,一旦 CharFixed 的分隔符不是逗号而是长度属性,就会将所有 CharFixed 转换为 CharTerm。
将所有类型 SQLVARCHAR 更改为 SQLVARYCHAR
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="Col_0" xsi:type="CharTerm" MAX_LENGTH="100" TERMINATOR=","/>
<FIELD ID="Col_1" xsi:type="CharTerm" MAX_LENGTH="100" TERMINATOR=","/>
<FIELD ID="Col_2" xsi:type="CharTerm" MAX_LENGTH="3" TERMINATOR=","/>
<FIELD ID="Col_3" xsi:type="CharTerm" TERMINATOR=","/>
<FIELD ID="Col_4" xsi:type="CharTerm" TERMINATOR=","/>
<FIELD ID="Col_5" xsi:type="CharTerm" TERMINATOR=","/>
<FIELD ID="Col_6" xsi:type="CharTerm" TERMINATOR=","/>
</RECORD>
<ROW>
<COLUMN SOURCE="Col_0" NAME="NAME" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="Col_1" NAME="MATCHNAME" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="Col_2" NAME="CHROMOSOME" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="Col_3" NAME="START_LOCATION" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="Col_4" NAME="END_LOCATION" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="Col_5" NAME="CENTIMORGANS" xsi:type="SQLFLT4"/>
<COLUMN SOURCE="Col_6" NAME="MATCHING_SNPS" xsi:type="SQLSMALLINT"/>
</ROW>
</BCPFORMAT>
不是一个完整的答案,因为我发现格式化文件是一个持久的谜,但请尝试让 BCP 生成格式化文件,例如:
bcp tempdb.dbo.FF_ChromBrwResults_20190124 format nul -c -x -t "," -f formatfile_gen.xml -T -S localhost
对于此表
create table FF_ChromBrwResults_20190124
(
NAME varchar(100),
MATCHNAME varchar(100),
CHROMOSOME varchar(30),
START_LOCATION bigint,
END_LOCATION bigint,
CENTIMORGANS real,
MATCHING_SNPS smallint
)
输出
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="21"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="21"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="rn" MAX_LENGTH="7"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="NAME" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="MATCHNAME" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="CHROMOSOME" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="START_LOCATION" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="5" NAME="END_LOCATION" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="6" NAME="CENTIMORGANS" xsi:type="SQLFLT4"/>
<COLUMN SOURCE="7" NAME="MATCHING_SNPS" xsi:type="SQLSMALLINT"/>
</ROW>
</BCPFORMAT>