how to avoid unexpected EOF w/Bulk Insert

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 2000 Forums

SQL Server Development (2000)

how to avoid unexpected EOF w/Bulk Insert

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-24 : 12:33:55

I'm using Bulk Insert to import various text files whose total number of records will vary.

How do I tell bulk insert to import rows UP to the effective end of the file, i.e. last row/last column entry.

Right now I'm testing with lastrow=400, but this of course only works for a file with the lastrow at 400. If the lastrow is 398, I get the unexpected end of file (EOF) error.

thx!

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-24 : 12:35:41

Don't use LASTROW?

Brett

8-)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-24 : 12:41:00

tried that but unexpected EOF error is generated when I remove LASTROW.

Here's the code:

BULK INSERT #abi_1 FROM 'D:\downloads\staging_test.txt'
WITH (DATAFILETYPE = 'char', FIRSTROW=2)

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-24 : 12:47:04

quote:
Originally posted by steelkilt

tried that but unexpected EOF error is generated when I remove LASTROW.

Here's the code:

BULK INSERT #abi_1 FROM 'D:\downloads\staging_test.txt'
WITH (DATAFILETYPE = 'char', FIRSTROW=2)

Well you don't need to specify that...it's the default

Is this your comma delimited file?

What happended to the garbage rows 1-3?

Where does the data come from and what the DDL of your temp table?

Brett

8-)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-24 : 12:52:19

Ive had this before when loading a file which was sourced from a unix system(which I know very little about).
I found that the file had some weird character in the middle of a record somewhere which sql server was interpretting as an end of row indicator.
I found this by viewing the input file with a text editor that could show me the data in hex values.

What I'm trying to say is that it could well be a problem with the input data.

Duane.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-24 : 13:01:15

all,

thanks for taking a look at this.

when I reveal the codes in a text editor I see that there are two hard returns after the last true row, thus three "blank" rows are created following last true row.

I'm starting with firstrow=2 now because sql has determined that all the "garbage" data is ROW 1.

the code:

GO

CREATE TABLE #testing
(ID int,
lastname varchar (100),
firstname varchar (100),
middlename varchar (50)

)

GO

BULK INSERT #testing FROM 'D:\downloads\test_input.txt'
WITH (DATAFILETYPE = 'char', FIRSTROW=2, LASTROW=400, KEEPNULLS)

*again, I get the EOF error if I remove LASTROW entirely, if I remove KEEPNULLS entirely, if I increment LASTROW=401+

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-24 : 13:10:31

Have you tried removing the 3 blank rows at the end of the file??

Duane.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-24 : 13:23:08

duane,

yes, that works fine if I remove the rows manually.

is there a way to auto-remove them via sql script, during bulk insert?

thx

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-24 : 13:25:52

Not that I Know of.
The application that extracts the data for you (prior to the bulk insert) must take care of this for you.

Duane.

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-24 : 13:56:35

Can you try something for me...

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO

BULK INSERT myTable99 FROM 'D:\downloads\test_input.txt'
GO

What happens with this?

Brett

8-)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-24 : 14:10:37

Brett,

Your script takes everything from the text file, one cell per row, tabs represented by square box.

The two blank rows at the end have one <NULL> in the cell per row.

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-24 : 14:58:03

Well this got quickly out of hand...it's much easier with fixed width files...



USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO

INSERT INTO myTable99(Col1)
SELECT 'FName'+CHAR(9)+'LName'+CHAR(9)+'MI' UNION ALL
SELECT 'Brett'+CHAR(9)+'Kaiser'+CHAR(9)+'J' UNION ALL
SELECT 'Steel'+CHAR(9)+'Kilt'+CHAR(9)+'' UNION ALL
SELECT 'SQL'+CHAR(9)+'Princess'+CHAR(9)+'W' UNION ALL
SELECT 'Ditch'+CHAR(9)+''+CHAR(9)+'' UNION ALL
SELECT Null UNION ALL
SELECT Null
GO

SELECT * FROM myTable99
GO

CREATE TABLE #testing (
	  ID int IDENTITY(1,1)
	, lastname varchar (100)
	, firstname varchar (100)
	, middlename varchar (50)

)
GO

INSERT INTO #testing(firstname,lastname,middlename)
SELECT 	   CASE WHEN  CHARINDEX(CHAR(9),Col1)-1 > 0 THEN
	   LEFT(Col1,CHARINDEX(CHAR(9),Col1)-1)	    ELSE NULL
	   END AS firstname
	,  CASE WHEN (CHARINDEX(CHAR(9),Col1,(CHARINDEX(CHAR(9),Col1)+1))-1)-((CHARINDEX(CHAR(9),Col1))) > 0 THEN
	   SUBSTRING(Col1
		,(CHARINDEX(CHAR(9),Col1)+1)
		,(CHARINDEX(CHAR(9),Col1,(CHARINDEX(CHAR(9),Col1)+1))-1)-((CHARINDEX(CHAR(9),Col1)))
		)											    
													    ELSE NULL
	   END AS LastName
	,  CASE WHEN  CHARINDEX(CHAR(9),REVERSE(Col1))-1 > 0      THEN
	   REVERSE(LEFT(REVERSE(Col1),CHARINDEX(CHAR(9),REVERSE(Col1))-1)) ELSE NULL
	   END AS middlename
  FROM myTable99
 WHERE SUBSTRING(Col1,1,5) <> 'FName' AND Col1 IS NOT NULL
GO

SELECT * FROM #testing
GO

DROP TABLE #testing
DROP TABLE myTable99
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-25 : 10:57:36

Did you try the code?

Also, you can use the work table to interogate the data to determine where the bogus values are....

Brett

8-)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-03-01 : 15:50:54

Brett,

Just got around to trying it today and it works beautifully. It's quite a lesson on the use of CHARINDEX, which leads to my next question:

your code uses CHARINDEX to insert either columns 1 through 3 into the second table, or simply all three columns, hard for me to tell based on my limited experience with CHARINDEX syntax.

The text file I'm accessing consists of 10 columns. I pull the whole text file into your first single-col table, then I want to use CHARINDEX to INSERT only columns 1, 2, and 6 into my final table.

Honestly, I can't see how to do this with CHARINDEX. Can you assist?

Thanks again.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-03-01 : 16:39:01

Here's what a typical line in the text file looks like. I used slashes to divide columns so I could get it all on one line. again, cols 1, 2, and 6 are the ones I need. Col 1 would be INT datatype, the other two VARCHAR. length of values in fields can and does vary.

1/ab2/sometext/66.70/55.69/CrucialText/800/textjunk/textjunk/1.01

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-01 : 17:02:57

DECLARE @String VARCHAR(7000)

SET @String = '1/ab2/sometext/66.70/55.69/CrucialText/800/textjunk/textjunk/1.01'

SELECT 'First' = SUBSTRING(@String, 1, CHARINDEX('/', @String) - 1)

SELECT @String = SUBSTRING(@String, CHARINDEX('/', @String) + 1, LEN(@String))

SELECT 'Second' = SUBSTRING(@String, 1, CHARINDEX('/', @String) - 1)

SELECT @String = SUBSTRING(@String, CHARINDEX('/', @String) + 1, LEN(@String))
SELECT @String = SUBSTRING(@String, CHARINDEX('/', @String) + 1, LEN(@String))
SELECT @String = SUBSTRING(@String, CHARINDEX('/', @String) + 1, LEN(@String))
SELECT @String = SUBSTRING(@String, CHARINDEX('/', @String) + 1, LEN(@String))

SELECT 'Sixth' = SUBSTRING(@String, 1, CHARINDEX('/', @String) - 1)

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-01 : 19:29:40

[code]
CREATE TABLE bulkdata(data varchar(8000))
GO
BULK INSERT bulkdata FROM 'c:\temp\test.txt'
GO

--create tally table
CREATE TABLE numbers ( n INT)
DECLARE @n INT; SET @n = 0
WHILE @n <= 100
BEGIN
INSERT INTO numbers
SELECT @n
SET @n = @n + 1
END

--create stage table
SELECT identity(INT,1,1) ID, data
INTO #stage
FROM bulkdata

SELECT *
FROM #stage

--create second stage table w/parsed data
SELECT identity(INT,1,1) row, d.id,d.n,d.data
INTO #stage2
FROM
(
SELECT id,n,
NullIf(SubString(char(9) + data + char(9) , n , CharIndex(char(9) , char(9) + data + char(9) , n) - n) , '') AS data
FROM numbers, #stage
WHERE ID <= Len(char(9) + data + char(9)) AND SubString(char(9) + data + char(9) , n - 1, 1) = char(9)
AND CharIndex(char(9) , char(9) + data + char(9) , n) - n > 0
) d

--extract columns 1,2 and 6
SELECT
MAX(CASE WHEN d.col = 1 THEN data END) col1,
MAX(CASE WHEN d.col = 2 THEN data END) col2,
MAX(CASE WHEN d.col = 6 THEN data END) col6
FROM
(
SELECT id, n, data, RIGHT(CONVERT(VARCHAR(10),row),1) col
FROM #stage2
) d
GROUP BY d.id

DROP TABLE bulkdata, numbers, #stage, #stage2[/code]

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-03-01 : 20:30:45

tara and ehorn,

thanks for the tips. both of your scripts work w/o errors but they accomplish something different than what I'm trying to accomplish.

Brett's code example above gets me 99% of the way there. If I run his script as-is against the actual text file I'm importing, it outputs columns 1,2, and "last column" -- in the case of my text file, this is column 10.

Brett's example also handles the thorny problem of two "blank" rows at the end of my text file.

What I'm trying to figure out is how to modify Brett's example to call for column 6 instead of "last" column. I'm having no luck.

Brett, are you out there ;-)

thx.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-02 : 12:32:03

You have to modify Brett's code to include either my script or Jay's (ehorn). So when you want to extract column 1,2, and 6, that's when you run this code. You still have to run Brett's as well.

Tara

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-14 : 11:43:30

Jay,

I've recently had a chance to revisit your code, spending some time looking at what it's actually doing. I took your advice re: removing NULLs by adding "where DATA is not null" to the select statement that creates the first #stage table (you noted earlier some reasons for why I was getting cross-linked data returned for cols 1,2, and 6).

When I look at the results for cols 1,2,6 I notice that the first 8 rows are fine, after which the data "shifts", placing the wrong entries in cols 1,2,6. Further investigation suggests this is happening when the routine encounters its first blank or null entry, which appears in row 8, col 7. Once this blank is encountered, the data is "shifted" one column to the left, inserting data from adjacent column into cols 1,2,6, beginning with row 9.

Any suggestions on how to modify your routine to deal with the blanks? I can post some actual data if that would help. As far as I can tell the trouble lies in this section. thanks much:

=======================================================

--create second stage table w/parsed data
SELECT identity(INT,1,1) row, d.id,d.n,d.data
INTO #stage2
FROM
(
SELECT id,n,
NullIf(SubString(char(9) + data + char(9) , n , CharIndex(char(9) , char(9) + data + char(9) , n) - n) , '') AS data
FROM numbers, #stage
WHERE ID <= Len(char(9) + data + char(9)) AND SubString(char(9) + data + char(9) , n - 1, 1) = char(9)
AND CharIndex(char(9) , char(9) + data + char(9) , n) - n > 0
) d

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-14 : 11:44:43

and here's a link to the follow-up discussion on this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33037&SearchTerms=bulk

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources