| Author |
 Topic  |
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
 Posted - 12/10/2007 : 14:26:27
|
I'm doing a bulk insert from a text file and get error:
Bulk insert: Unexpected end-of-file (EOF) encountered in data file
Here is my sp:
quote:
CREATE PROCEDURE MyJobs
@PathFileName varchar(100)
as
If Exists(Select * From Information_Schema.Tables Where Table_Type = 'Base Table' And Table_Name = 'MyJobTable')
Begin
Drop Table MyJobTable
End
create table MyJobTable
(
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(6,2),
qty_received decimal(6,2),
plant_id char (5)
)
DECLARE @SQL varchar(2000)
SET @SQL = "BULK INSERT MyJobTable FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""' ) "
EXEC (@SQL)
GO
Here is my text file:
11/28/2007,3004245 ,05201 ,408
11/28/2007,3006204 ,05101 ,330
11/28/2007,3006204 ,05401 ,135 |
|
|
tm
Posting Yak Master
119 Posts |
Posted - 12/10/2007 : 15:24:57
|
Your text file fields do not match the number of fields in your table and this is giving you the error.
You need a format file if you only want to insert the 4 fields into your table that has 6 fields.
Refer to SQL Books-On-Line >> Bulk Insert .. FormatFile |
 |
|
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 12/10/2007 : 15:35:19
|
Here is now what I have:
VB:
Dim content As String = ticket_date & "\t" & job & "\t" & job_phase & "\t" & tons & "\n"
IO.File.AppendAllText(absolutePath, content + Environment.NewLine)
Sp:
CREATE PROCEDURE MyJobs
@PathFileName varchar(100),
@Return int =0 output
as
If Exists(Select * From Information_Schema.Tables Where Table_Type = 'Base Table' And Table_Name = 'MyJobTable')
Begin
Drop Table MyJobTable
End
create table MyJobTable
(
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(6,2)
)
DECLARE @SQL varchar(2000)
SET @SQL = 'BULK INSERT MyJobTable FROM ''' + @PathFileName + ''' WITH (FIELDTERMINATOR = ''\t'' ,ROWTERMINATOR = "\n")'
EXEC (@SQL)
RETURN @Return
GO
In my VB program I get error:
Incorrect syntax near '\n'
|
Edited by - snufse on 12/10/2007 15:37:51 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/10/2007 : 16:26:48
|
Use double single-quote instead of single double-quote.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 12/10/2007 : 16:29:47
|
Ok, using single quotes.
I still get "Unexpected EOF encountered in data file"
VB:
Dim content As String = ticket_date & "\t" & job & "\t" & job_phase & "\t" & tons & vbCr
IO.File.AppendAllText(absolutePath, content + Environment.NewLine)
SP:
CODE
CREATE PROCEDURE MyJobs
@PathFileName varchar(100),
@Return int =0 output
as
If Exists(Select * From Information_Schema.Tables Where Table_Type = 'Base Table' And Table_Name = 'MyJobTable')
Begin
Drop Table MyJobTable
End
create table MyJobTable
(
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(6,2)
)
DECLARE @SQL varchar(2000)
SET @SQL = 'BULK INSERT MyJobTable FROM ''' + @PathFileName + ''' WITH (FIELDTERMINATOR = ''\t'') '
EXEC (@SQL)
RETURN @Return
GO
|
Edited by - snufse on 12/10/2007 16:30:15 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/10/2007 : 16:31:56
|
Add a new empty line in the text-file by placing the text-caret in the last line, after the last character.
New press ENTER and save text file.
Try to import again.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 12/10/2007 : 16:40:19
|
| If I understand you correctly, I should write a blank line to my data file at the end and just include the "vbCr" as content. Correct? |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/10/2007 : 16:46:00
|
Add a new empty line, yes.
Add content vbCr? No.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 12/10/2007 : 16:55:44
|
That Did not work, same error:
My Loop add didn data to file:
code]
Dim absolutePath As String = "\\VGIWPW03-SQL3\c$" & RelativePath
Dim content As String = ticket_date & "\t" & job & "\t" & job_phase & "\t" & tons & vbCr
IO.File.AppendAllText(absolutePath, content + Environment.NewLine)[/code]
After last data line written:
code]
Dim absolutePath As String = "\\VGIWPW03-SQL3\c$" & RelativePath
Dim content As String = " "
IO.File.AppendAllText(absolutePath, content + Environment.NewLine)[/code]
SP:
code]
CREATE PROCEDURE MyJobs
@PathFileName varchar(100),
@Return int =0 output
as
If Exists(Select * From Information_Schema.Tables Where Table_Type = 'Base Table' And Table_Name = 'MyJobTable')
Begin
Drop Table MyJobTable
End
create table MyJobTable
(
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(6,2)
)
DECLARE @SQL varchar(2000)
SET @SQL = 'BULK INSERT MyJobTable FROM ''' + @PathFileName + ''' WITH (FIELDTERMINATOR = ''\t'') '
EXEC (@SQL)
RETURN @Return
GO[/code]
My Data File:
11/29/2007\t3004245 \t05201 \t205
11/29/2007\t3006204 \t05101 \t434
11/29/2007\t3006274 \t05101 \t7
11/29/2007\t3007208 \t02007 \t1
11/29/2007\t3007280 \t05401 \t138
11/30/2007\t3004245 \t05201 \t192
11/30/2007\t3006204 \t05101 \t504
11/30/2007\t3007280 \t05401 \t238
12/2/2007\t3004245 \t05301 \t212
12/2/2007\t3006204 \t05101 \t210
|
Edited by - snufse on 12/10/2007 16:57:35 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/10/2007 : 17:04:07
|
Works great for me!CREATE TABLE #Yak
(
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(6,2)
)
DECLARE @SQL varchar(2000),
@PathFileName varchar(200)
set @PathFileName = 'c:\temp\yak.txt'
SET @SQL = 'BULK INSERT #Yak FROM ''' + @PathFileName + ''' WITH (FIELDTERMINATOR = ''\t'')'
EXEC (@SQL)
select * from #Yak
drop table #Yak And the text-file did not even have to have an extra linebreak at the end.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/10/2007 : 17:06:12
|
Are you are you are not having some empty lines in the data?
Like
11/29/2007 3004245 05201 205
11/29/2007 3006204 05101 434
11/29/2007 3006274 05101 7
11/29/2007 3007208 02007 1
11/29/2007 3007280 05401 138
11/30/2007 3004245 05201 192
11/30/2007 3006204 05101 504
11/30/2007 3007280 05401 238
12/2/2007 3004245 05301 212
12/2/2007 3006204 05101 210
12/3/2007 3004245 05301 392
12/3/2007 3006204 05101 503
12/3/2007 3007280 05401 140
12/4/2007 3004245 05201 104
12/4/2007 3004507 05101 5
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 12/11/2007 : 12:41:50
|
I tried to call the sp directly and get the same error.
If I change the text file to have 1 data element and 5 records and then call the sp it works. Seems like when I insert a terminator it does not like that. |
|
|
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 12/11/2007 : 13:04:55
|
If I have one data field, it works fine, see below
SP:
CREATE procedure MyTest
as
CREATE TABLE Test
(
jobnumber char(15)
)
DECLARE @SQL varchar(2000),
@PathFileName varchar(200)
set @PathFileName = 'c:\yak.txt'
BULK INSERT Test
FROM 'c:\yak.txt' WITH ( ROWTERMINATOR = '\n', DATAFILETYPE =
'char')
EXEC (@SQL)
select * from Test
GO
File:
ABCDEFGHIJKLMN
CCCCCCCCC
BBBB
MMMMMMMMM
If I have 2 data fields it does NOT work, see below:
SP:
CREATE procedure MyTest
as
CREATE TABLE Test
(
jobnumber char(15),
jobphase char(15)
)
DECLARE @SQL varchar(2000),
@PathFileName varchar(200)
set @PathFileName = 'c:\yak.txt'
BULK INSERT Test
FROM 'c:\yak.txt' WITH ( FIELDTERMINATOR = ',', DATAFILETYPE =
'char')
EXEC (@SQL)
select * from Test
GO
File:
ABCDEFGHIJKLMN,AAAAAAAA
CCCCCCCCC,GGGGGGGGG
BBBB,TTTTTTTTT
MMMMMMMMM,YYYYYYYY
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. |
Edited by - snufse on 12/11/2007 13:05:36 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/11/2007 : 13:39:57
|
Have you enabled ad-hoc queries such as OPENROWSET with Surface Area Configuration Tool?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 12/11/2007 : 13:55:20
|
| Yes we have.. |
Edited by - snufse on 12/11/2007 13:59:01 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/11/2007 : 15:25:51
|
Here (http://p2p.wrox.com/topic.asp?TOPIC_ID=67520) you posted an extra comma at the end of each line.
Because of that, you have THREE columns in the textfile.
The last column is empty but still there.
E 12°55'05.25"
N 56°04'39.16" |
Edited by - Peso on 12/11/2007 15:27:04 |
|
|
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 12/11/2007 : 15:49:48
|
Now, is there another way to automate bulk insert? We are using both sequel server 2000 and 2005, does that make a difference?
(Just curious: Lat and Long E 12°55'05.25" & N 56°04'39.16" must be close to Halmstad or....) |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/11/2007 : 16:05:11
|
50 miles from Halmstad, that is correct.
Are you a resident of Halmstad? My girlfriend works there on Swedish Military Academy.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 12/11/2007 : 16:13:39
|
Did another test.
Here is a simple test that does not work and gives error (run i qry analyzer)
CREATE TABLE Names
(
Firstname varchar(20),
Surname varchar(20)
)
BULK INSERT Names FROM 'c:\names.txt'
File:
John Greed
Kevin Heath
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.
(used to work in Malmoe before I moved to the US... )
|
Edited by - snufse on 12/11/2007 16:14:40 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/11/2007 : 16:17:52
|
The two names should be separated with comma before trying to insert.
As of now, BULK INSERT treats the names as one column and since the other column is missing you get an error.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
snufse
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 12/11/2007 : 16:26:13
|
I took the example from
http://www.dotnetjohn.com/articles.aspx?articleid=148
quote:
Example:
Create a text file with note pad containing names with first and surnames separated by tabs on each line, e.g.:
Chris Sully
Sian Davies
Fiona Phelps
Tania Barra-Shaw
Save this as c:\test.txt
In Query Analyzer create a new table to transfer this data to:
CREATE TABLE BulkInsertTest
(
Firstname varchar(20),
Surname varchar(20)
)
Now BULK INSERT the data:
BULK INSERT BulkInsertTest FROM 'c:\test.txt'
and check it's worked:
select * from BulkInsertTest
I also tried with 'commas' but nothing seems to work, I always get the same error message..... do you have any good ideas????? |
Edited by - snufse on 12/11/2007 16:27:26 |
|
|
|
Topic |
|
Bulk insert
