Bulk insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bulk insert

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2007-12-10 : 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

160 Posts

Posted - 2007-12-10 : 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

469 Posts

Posted - 2007-12-10 : 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'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 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

469 Posts

Posted - 2007-12-10 : 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 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

469 Posts

Posted - 2007-12-10 : 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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 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

469 Posts

Posted - 2007-12-10 : 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 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

469 Posts

Posted - 2007-12-11 : 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

469 Posts

Posted - 2007-12-11 : 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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 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

469 Posts

Posted - 2007-12-11 : 13:55:20

Yes we have..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 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"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2007-12-11 : 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....)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 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

469 Posts

Posted - 2007-12-11 : 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... )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 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

469 Posts

Posted - 2007-12-11 : 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?????

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources