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
Answer
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.