title: SQL Server Bulk Import with format file - An unexpected end of file was encountered in the data file
body: <p>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. </p>
  
  <p>following is the a single row from csv file</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>and following is my format file, generated by using BCP utility</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and here is the SQL</p>
  
  <pre><code>BULK INSERT WASA_Bill_Detail 
  FROM 'e:\WasaBillRecord.csv'
  WITH
  (
      KEEPIDENTITY,
      FORMATFILE = 'e:\bill_detail_format1.fmt',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      ERRORFILE = 'e:\error.log'
  )
  </code></pre>
  
  <p>know when i execute the abve sql following error accour</p>
  
  <pre><code>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)".
  </code></pre>
  
  <p>i have tried <code>ROWTERMINATOR = '\n',</code> , <code>ROWTERMINATOR = '\r\n',</code> and <code>ROWTERMINATOR = '0x0a',</code>. The error is same</p>
  
  <p>any help</p>