Sql server bulk insert文本文件插入到数据库
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Sql server bulk insert文本文件插入到数据库,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3591字,纯文字阅读大概需要6分钟。
内容图文
bulk insert test from ‘f:\test.txt‘ with (fieldterminator=‘,‘, rowterminator=‘\n‘, FIRSTROW =1, LASTROW=3)
要把平面文件数据导入到数据库表中,平面文件只有3个字段,数据库表有7个字段,
怎么把平面文件字段的对应到表的字段,如何用bulk insert来实现?
数据库表 userinfo
CREATE TABLE userinfo ( id INT identity, userName varchar(20), pass varchar(20), address varchar(100), phone varchar(20), email varchar(128), registerTime datetime )
平面文件数据是F:\test.txt
userName,address,phone hua,湖南,5971898 jan,重庆,2334512 wang, 北京, 100201
先在G盘存放一个格式化xml文件
G:\format.xml
<?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="20" COLLATION="Chinese_PRC_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="userName" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="2" NAME="address" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="3" NAME="phone" xsi:type="SQLVARYCHAR"/> </ROW> </BCPFORMAT>
然后使用bulk insert语句时,采用FORMATFILE参数指定该format.xml文件
BULK INSERT userinfo FROM ‘F:\test.txt‘ WITH ( FORMATFILE = ‘G:\format.xml‘, FIELDTERMINATOR=‘,‘, ROWTERMINATOR=‘\n‘, FIRSTROW = 2 )
这样bulk insert语句就会按照format.xml文件中的列映射,将平面文件F:\test.txt的三列数据,依次插入到表userinfo的userName、address、phone这三个字段上了。
疑难解答
1.最好使用十六进制ASCII码来声明ROWTERMINATOR
有时候当我们的txt或csv文件的文件头没有声明文件的编码方式(比如txt或csv文件是936-GBK或者UTF-8等编码方式,但是在其文件头没有声明)时,会导致bulk insert语句的ROWTERMINATOR参数失效,比如当我在导入一个test.txt文件是936-GBK编码方式的时候,我发现明明文件中的换行符是‘\n‘,但是当我声明ROWTERMINATOR=‘\n‘的时候根本不起作用,bulk insert老是找不到文件中的换行符进而报错。
然后我通过网上查资料发现,原来bulk insert的ROWTERMINATOR参数,可以使用十六进制ASCII码来表示换行符是什么。比如‘\n‘的ASCII码是十进制12,十六进制0A,那么我们可以在bulk insert语句中声明0x0a来作为ROWTERMINATOR表示换行符‘\n‘,如下所示:
BULK INSERT userinfo FROM ‘F:\test.txt‘ WITH ( FIELDTERMINATOR=‘|‘, ROWTERMINATOR=‘0x0a‘, FIRSTROW =1, LASTROW=1000, CODEPAGE=‘936‘ )
实验发现当我用十六进制ASCII码声明bulk insert的ROWTERMINATOR参数后,sql server成功识别出了文件中每一行结束的位置。同样如果换行符是‘\r‘,那么我们可以声明ROWTERMINATOR为十六进制ASCII码0x0d。所以当你使用常规字符(例如‘\n‘、‘\r‘等)来给bulk insert的ROWTERMINATOR参数赋值不起作用的时候,你可以尝试使用字符的十六进制ASCII码来给ROWTERMINATOR参数赋值,bulk insert的ROWTERMINATOR参数可以根据十六进制ASCII码准确识别出数据文件中的换行符。
2.使用CODEPAGE参数声明文件的编码方式
CODEPAGE参数可以声明txt或csv文件的编码方式是什么,有时候bulk insert无法识别出导入文件的编码方式,会导致从文件导入的数据是乱码,这时候如果我们知道文件的编码方式,就可以使用CODEPAGE参数告诉bulk insert文件使用的是什么编码,从而避免数据导入到数据库后变为乱码。比如上面的sql中我们就用CODEPAGE参数声明了导入文件的编码是936(GBK)
另外这里有一篇文章很详细的总结了使用bulk insert可能会出现的一些换行问题,大家可以参考下:
SQL Server Bulk Insert Row Terminator Issues
Sql server bulk insert文本文件插入到数据库
标签:pos 文本 form schema reg tab identity fir ble
本文系统来源:https://www.cnblogs.com/yclizq/p/12154609.html
内容总结
以上是互联网集市为您收集整理的Sql server bulk insert文本文件插入到数据库全部内容,希望文章能够帮你解决Sql server bulk insert文本文件插入到数据库所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。