使用格式化文件的SQL Server大容量导入-在数据文件中遇到意外的文件结尾
使用格式化文件的SQL Server大容量导入-在数据文件中遇到意外的文件结尾
提问于 2014-12-23 16:54:02
我正在测试从逗号分隔的csv文件到sql server 2012的大容量导入。为此，我使用BCP实用程序创建了格式化文件。
以下是csv文件中的一行
W42031535,4000111689,Domestic,NOV-DEC 2014,Mustafa Satta S/O Abdul Sattar,"H#31, Block#F, TNT Colony",1-Dec-14,17-Dec-14,0,322,20,342,8380,0,8722,32,8754,"JUL-AUG,14",0,"SEP-OCT,14",0,"NOV-DEC,14",0,8,242,161,0,0,0,NULL,NULL,NULL,NULL,0 下面是我的格式化文件，使用BCP实用程序生成
11.0
34
1 SQLNCHAR 0 100 '","' 1 AccountNo SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 100 '","' 2 BillNo SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 100 '","' 3 Category SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 100 '","' 4 Billing_Period SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 200 '","' 5 Name SQL_Latin1_General_CP1_CI_AS
6 SQLNCHAR 0 0 '","' 6 Address SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 100 '","' 7 Issue_Date SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 100 '","' 8 Due_Date SQL_Latin1_General_CP1_CI_AS
9 SQLDECIMAL 0 19 '","' 9 Water_Bill Decimal
10 SQLDECIMAL 0 19 '","' 10 Sewerage_Bill Decimal
11 SQLDECIMAL 0 19 '","' 11 Aquifer_Charges Decimal
12 SQLDECIMAL 0 19 '","' 12 Current_Amount Decimal
13 SQLDECIMAL 0 19 '","' 13 Arrears Decimal
14 SQLDECIMAL 0 19 '","' 14 Service_Charges Decimal
15 SQLDECIMAL 0 19 '","' 15 Payable_within_DueDate Decimal
16 SQLDECIMAL 0 19 '","' 16 Surcharge Decimal
17 SQLDECIMAL 0 19 '","' 17 Payable_after_DueDate Decimal
18 SQLNCHAR 0 100 '","' 18 Payment_History_1 SQL_Latin1_General_CP1_CI_AS
19 SQLDECIMAL 0 19 '","' 19 Paid_1 Decimal
20 SQLNCHAR 0 100 '","' 20 Payment_History_2 SQL_Latin1_General_CP1_CI_AS
21 SQLDECIMAL 0 19 '","' 21 Paid_2 Decimal
22 SQLNCHAR 0 100 '","' 22 Payment_History_3 SQL_Latin1_General_CP1_CI_AS
23 SQLDECIMAL 0 19 '","' 23 Paid_3 Decimal
24 SQLDECIMAL 0 19 '","' 24 Area Decimal
25 SQLDECIMAL 0 19 '","' 25 Water_Rate Decimal
26 SQLDECIMAL 0 19 '","' 26 Sewerage_Rate Decimal
27 SQLDECIMAL 0 19 '","' 27 Discharge_Basis Decimal
28 SQLNCHAR 0 100 '","' 28 Pump_Size SQL_Latin1_General_CP1_CI_AS
29 SQLDECIMAL 0 19 '","' 29 Ferrule_Size Decimal
30 SQLNCHAR 0 100 '","' 30 Meter_Type SQL_Latin1_General_CP1_CI_AS
31 SQLNCHAR 0 100 '","' 31 Meter_Status SQL_Latin1_General_CP1_CI_AS
32 SQLNCHAR 0 100 '","' 32 Last_Readin SQL_Latin1_General_CP1_CI_AS
33 SQLNCHAR 0 100 '","' 33 Current_Reading SQL_Latin1_General_CP1_CI_AS
34 SQLDECIMAL 0 19 "\n" 34 Water_Aquiffer_Charges Decimal下面是SQL
BULK INSERT WASA_Bill_Detail
FROM 'e:\WasaBillRecord.csv'
WITH
(
KEEPIDENTITY,
FORMATFILE = 'e:\bill_detail_format1.fmt',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'e:\error.log'
)当我执行abve sql时会遇到以下错误：
Msg 4832, Level 16, State 1, Procedure BInsert, Line 10
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Procedure BInsert, Line 10
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Procedure BInsert, Line 10
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".我试过ROWTERMINATOR = '\n',，ROWTERMINATOR = '\r\n',和ROWTERMINATOR = '0x0a',。错误是相同的
有什么帮助吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2014-12-23 16:59:58
在examples下的BULK INSERT doc中：
C.使用换行符作为行终止符
下面的示例导入一个使用换行符作为行终止符的文件，如UNIX输出：
声明@bulk_cmd varchar(1000)；设置@bulk_cmd = 'BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail FROM‘：\\’‘WITH (ROWTERMINATOR =’+CHAR(10)+‘)’；EXEC(@bulk_cmd)；
由于Microsoft Windows处理文本文件的方式(\n自动替换为\r\n)。
或者，通过行结束转换器发送文本文件(unix2dos等人)，或者以ASCII码模式从检索文件的任何服务器传输文件。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27616962
复制相关文章
相似问题
腾讯云开发者
