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

microsoft.public.dotnet.framework.adonet

28 Mar 2006 8:04 AM

marcus.skillern

I am letting the end-user select the file to be imported into the SQL via
the intranet. I am getting this error displayed.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Bulk Insert: Unexpected
end-of-file (EOF) encountered in data file.

/loaddata/file.asp, line 15

Here is a copy of the asp file

<html><head><title>Loading file</title></head><body>
<%
x = request.form("filename")
response.write "the file is = "&x
set myCon = Server.CreateObject("ADODB.Connection")
mycon.Open "NIPDB"
set myCmd = server.CreateObject("Adodb.command")
mycmd.activeconnection = mycon
mycmd.commandText = "LDS"
mycmd.commandType = 4
Set BudRS5 = mycmd.execute(,array(x))
budrs5.close
mycon.close
set mycon = nothing
%></body></html>

Here is copy of stored procedure LDS

CREATE procedure LDS(@Filein nvarchar(50)) AS
   DECLARE @SQLString NVARCHAR(80)
/* Build Command*/
SET @SQLString =
N'Bulk Insert DailyTbl from ' + N'N''' + @Filein + N'''FIRSTROW = 2,
        MAXERRORS = 0,
        FIELDTERMINATOR = ",",
        ROWTERMINATOR = "\n,\t"'
/* Execute */
EXEC (@SQLString)
GO

Here is a sample of data held within the .csv file

Day,Date,Line,Line Description,Registered Sales (£),Sales SUs,Reduced
(£),Explained Wastage,Reductions % Reg sales
SUN,29/01/2006,28429,CONT CHAR POTS,1072.67,632,58.61,39.38,5.46
SUN,29/01/2006,38439,ORG LGE LSE POTS,279.97,166,0.0,24.98,0.0

Please let me know if more information is required

28 Mar 2006 1:30 PM

Jim Hughes

Note that this is the ADO.NET newsgroup, not ADO you may have better luck in
an asp newsgroup This is not specifically an ADO problem, it is actually a
ASP problem.

Since you are using a web server, it is quite unlikely that the ASPNET user
that the web page is running as has access to the file path referenced by
request.form("filename").

You will probably need to upload the file to the web server (see Google,
search ASP FILE UPLOAD) and then once the file is uploaded, use a path that
is local to the web server for which the ASPNET user has access.

Show quote

"marcus.skillern" <marcus.skill***@britbraz.com> wrote in message
news:utK834jUGHA.5884@TK2MSFTNGP14.phx.gbl...
>I am letting the end-user select the file to be imported into the SQL via
>the intranet. I am getting this error displayed.
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Bulk Insert: Unexpected
> end-of-file (EOF) encountered in data file.
>
> /loaddata/file.asp, line 15
>
> Here is a copy of the asp file
>
> <html><head><title>Loading file</title></head><body>
> <%
> x = request.form("filename")
> response.write "the file is = "&x
> set myCon = Server.CreateObject("ADODB.Connection")
> mycon.Open "NIPDB"
> set myCmd = server.CreateObject("Adodb.command")
> mycmd.activeconnection = mycon
> mycmd.commandText = "LDS"
> mycmd.commandType = 4
> Set BudRS5 = mycmd.execute(,array(x))
> budrs5.close
> mycon.close
> set mycon = nothing
> %></body></html>
>
> Here is copy of stored procedure LDS
>
> CREATE procedure LDS(@Filein nvarchar(50)) AS
>   DECLARE @SQLString NVARCHAR(80)
> /* Build Command*/
> SET @SQLString =
> N'Bulk Insert DailyTbl from ' + N'N''' + @Filein + N'''FIRSTROW = 2,
>        MAXERRORS = 0,
>        FIELDTERMINATOR = ",",
>        ROWTERMINATOR = "\n,\t"'
> /* Execute */
> EXEC (@SQLString)
> GO
>
>
> Here is a sample of data held within the .csv file
>
> Day,Date,Line,Line Description,Registered Sales (£),Sales SUs,Reduced
> (£),Explained Wastage,Reductions % Reg sales
> SUN,29/01/2006,28429,CONT CHAR POTS,1072.67,632,58.61,39.38,5.46
> SUN,29/01/2006,38439,ORG LGE LSE POTS,279.97,166,0.0,24.98,0.0
>
> Please let me know if more information is required
>
>

28 Mar 2006 1:44 PM

Cowboy (Gregory A. Beamer) - MVP

Off the top of my head there are a couple of possibilities:

A char(0) somewhere in the file. Use a streamreader, readall() and put into
a character array to find.

Or (more probable?):
No CR or LF at the end of the last line, which confuses the bulk loader.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Show quote

"marcus.skillern" wrote:

> I am letting the end-user select the file to be imported into the SQL via
> the intranet. I am getting this error displayed.
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Bulk Insert: Unexpected
> end-of-file (EOF) encountered in data file.
>
> /loaddata/file.asp, line 15
>
> Here is a copy of the asp file
>
> <html><head><title>Loading file</title></head><body>
> <%
> x = request.form("filename")
> response.write "the file is = "&x
> set myCon = Server.CreateObject("ADODB.Connection")
> mycon.Open "NIPDB"
> set myCmd = server.CreateObject("Adodb.command")
> mycmd.activeconnection = mycon
> mycmd.commandText = "LDS"
> mycmd.commandType = 4
> Set BudRS5 = mycmd.execute(,array(x))
> budrs5.close
> mycon.close
> set mycon = nothing
> %></body></html>
>
> Here is copy of stored procedure LDS
>
> CREATE procedure LDS(@Filein nvarchar(50)) AS
>    DECLARE @SQLString NVARCHAR(80)
> /* Build Command*/
> SET @SQLString =
> N'Bulk Insert DailyTbl from ' + N'N''' + @Filein + N'''FIRSTROW = 2,
>         MAXERRORS = 0,
>         FIELDTERMINATOR = ",",
>         ROWTERMINATOR = "\n,\t"'
> /* Execute */
> EXEC (@SQLString)
> GO
>
>
> Here is a sample of data held within the .csv file
>
> Day,Date,Line,Line Description,Registered Sales (Â£),Sales SUs,Reduced
> (Â£),Explained Wastage,Reductions % Reg sales
> SUN,29/01/2006,28429,CONT CHAR POTS,1072.67,632,58.61,39.38,5.46
> SUN,29/01/2006,38439,ORG LGE LSE POTS,279.97,166,0.0,24.98,0.0
>
> Please let me know if more information is required
>
>
>

28 Mar 2006 2:31 PM

marcus.skillern

Thanks for all the responses - here is my final script (that works)
I have 3 different files, Daily, Summaries and Branches which need loading
into three different tables.

I am sure there ia 'neater' way of achieving the result - but this way
works,

I was missing the DATAFILETYPE='char' and ROWTERMINATOR = '\n\N'.

Thanks again all

for each myfile in myfolder.files
x = myfile.name
if InStr(x,"Daily") > 0 then

response.write "Daily file loading ="&x&"<br>"

d = d+1
myrs.open "BULK INSERT TblDaily "&_
" FROM '"&myfolder&"\"&x&"' WITH (FIRSTROW=2,DATAFILETYPE = 'char',
FIELDTERMINATOR = '\,', ROWTERMINATOR = '\n\N');",mycon
end if

if InStr(x,"Summaries") > 0 then

response.write "Week file loading ="&x&"<br>"

s = s+1
myrs.open "BULK INSERT TblSummaries "&_
" FROM '"&myfolder&"\"&x&"' WITH (FIRSTROW=2,DATAFILETYPE = 'char',
FIELDTERMINATOR = '\,', ROWTERMINATOR = '\n\N');",mycon
end if

if InStr(x,"Branches") > 0 then
b = b+1

response.write "branch file loading ="&x&"<br>"

myrs.open "BULK INSERT TblBranch "&_
" FROM '"&myfolder&"\"&x&"' WITH (FIRSTROW=2,DATAFILETYPE = 'char',
FIELDTERMINATOR = '\,', ROWTERMINATOR = '\n\N');",mycon
end if

fs.MoveFile myfolder&"\"&x,"E:\NIPDATA\Loaded\"
next