Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6997
  • Last Modified:

BULK INSERT - Unexpected end-of-file (EOF) encountered in data file.

Hello Experts,

I have a BULK INSERT statement,

SET ANSI_WARNINGS OFF
bulk insert temp_SSI05AUG01_TXT from 'F:\SSI.TXT' with (FORMATFILE = 'C:\FormatFile\SSI.fmt' ,
FIRE_TRIGGERS, ROWS_PER_BATCH=1000)

and my format file SSI.fmt is,

8.0
2
1      SQLCHAR      0      15      ";"      1      Col1      Latin1_General_CI_AI
2      SQLCHAR      0      30      "\n"      2      Col2      Latin1_General_CI_AI

and my data file SSI.TXT is,

9132551868629;20050731 11:05:26
9132551868814;20050731 22:29:32
9132551784225;20050731 13:33:20
9132551785466;20050731 09:57:55
9132551376501;20050731 14:15:19

When I execute the above statement, I am getting the following error,

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

Please give me an answer.


0
batchakamal
Asked:
batchakamal
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is F: a local drive or not?
does the data file have some empty lines at the end?
does the data file a \n or rather a \r\n
0
 
batchakamalAuthor Commented:
Thanks for ur reply.
Yeah I have checked the data, It has a summary line and empty line,
which look like this,

9132551868629;20050731 11:05:26
9132551868814;20050731 22:29:32
9132551784225;20050731 13:33:20
9132551785466;20050731 09:57:55
9132551376501;20050731 14:15:19
Total records : 8565

I have removed the last 2 lines, and it works fine.
Can I specify this one in the BULK INSERT statement itself. Bcoz i have n number of files, and also I am putting this BULK INSERT in the scheduled job for new files to upload.

Awaiting ur reply.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
unfortunately, on bulk insert you can only skip the first lines.
however, you can specify to allow X errors, using MAXERRORS

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_2e5s.asp

SET ANSI_WARNINGS OFF
bulk insert temp_SSI05AUG01_TXT from 'F:\SSI.TXT' with (FORMATFILE = 'C:\FormatFile\SSI.fmt' ,
FIRE_TRIGGERS, ROWS_PER_BATCH=1000, MAXERRORS=2)

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now