公司需要导入数据到MSSQLSERVER，但是对方给出的奇葩cvs文件是这样的:

aa,bb,cc,dd,ee
1,"this
is a",1,"this is a",1
2,"this is
" a",2,"this is a",2

普通的bcp处理会有悲催的错位，而且mssql个坑货也不能像mysql自动区分文本限定符的"
研究了一下，用这个方式处理：

1. 在DB里建立对应的表

点击(此处)折叠或打开

USE [test]
GO
CREATE TABLE [dbo].[Table_1](
[aa] [int] NULL,
[bb] [varchar](50) NULL,
[cc] [int] NULL,
[dd] [varchar](50) NULL,
[ee] [int] NULL
) ON [PRIMARY]
GO

2. 利用该表格生成fmt文件
点击(此处)折叠或打开

/*generaate thebcp fmt file*/
exec xp_cmdshell 'bcp test.dbo.table_1 format nul -t "," -c -x -f C:\test\table_1.xml -U**** -P**** '
go

3，修改xml，请注意高亮部分。这个你们用ie来打开的话也可以明确的看到"
此外还有这些特殊字符，大家先备着，万一哪天被不靠谱的猪队友坑了还能用。
单引号是'
双引号是"
&是&
>是>
<是<
空格是  等

点击(此处)折叠或打开

<?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="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=""," MAX_LENGTH="50" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR=","" MAX_LENGTH="12"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR=""," MAX_LENGTH="50" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="aa" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="bb" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="cc" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="dd" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="ee" xsi:type="SQLINT"/>
</ROW>
</BCPFORMAT>

然后再把cvs的首列的标题去掉，虽说微软说可以通过firstrow来指定第一行，但我之前用了firstrow=2居然是从第二行开始的。
如果不去掉，会报错，由于赶时间，报错测原因和具体原理暂时不考虑再去深入研究了，之后有时间慢慢测。

4. 从cvs里取值，这里我用了openrowset来做验证，其实这个时候也可以直接用bcp加指定 format file来进行导入操作了。

点击(此处)折叠或打开

select * from
openrowset(bulk 'C:\Booker\test.csv', --要读取的文件路径和名称
formatfile='C:\Booker\Table_1.xml', --格式化文件的路径和名称
--fieldterminator='","',
firstrow = 1,--要载入的第一行
--lastrow = 1000, --要载入的最后一行,此值必须大于firstrow
maxerrors = 100, --在加载失败之前加载操作中最大的错误数
--errorfile ='c:\wc_error1.txt', --存放错误的文件
rows_per_batch = 10000 --每个批处理导入的行数
) as t

你需要的结果有了

文本格式下就是这么个东东
aa bb cc dd ee
----------- -------------------------------------------------- ----------- -------------------------------------------------- -----------
1 this,
is, a 1 this is a 1
2 this is
" a 2 this is a 2

(2 行受影响)

ITPub博客

如何处理带有引号文本限定符的CVS

最新文章