当前位置: 动力学知识库 > 问答 > 编程问答 >

csv - SQL Server Bulk Import with format file - An unexpected end of file was encountered in the data file

问题描述:

I am testing bulk import from a comma separated csv file to sql server 2012. for this purpose i have created the format file by using BCP utility.

following is the a single row from csv file

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

and following is my format file, generated by using BCP utility

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

and here is the 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'

)

know when i execute the abve sql following error accour

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)".

i have tried ROWTERMINATOR = '\n', , ROWTERMINATOR = '\r\n', and ROWTERMINATOR = '0x0a',. The error is same

any help

网友答案:

From the BULK INSERT doc under examples:

C. Using line feed as a row terminator
The following example imports a file that uses the line feed as a row terminator such as a UNIX output:

DECLARE @bulk_cmd varchar(1000);
SET @bulk_cmd = 'BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')';
EXEC(@bulk_cmd);

Due to how Microsoft Windows treats text files (\n automatically gets replaced with \r\n).

Alternately, send your text file through a line ending converter (unix2dos, et al) or transfer the file in ASCII mode from whatever server you retrieve it from.

分享给朋友:
您可能感兴趣的文章:
随机阅读: