Bulk insert multiple records from multiple files

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)

Bulk insert multiple records from multiple files

Author

Topic

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-06 : 17:27:55

Below is my code. Why does it only insert the first record of each file. Also if I have 13 records in the directory, it only pulls about 7 of the first records. Thanks in advance for your help.

CREATE TABLE files (filename varchar(128))
INSERT INTO files (filename) EXEC master..xp_cmdshell 'dir/b "f:\Research\*.csv"'
DELETE files WHERE NullIf(filename, 'File Not Found') IS NULL

DECLARE @sql varchar(8000)
WHILE EXISTS (SELECT * FROM files)
BEGIN

SELECT @sql=''
SELECT @sql=@sql+'BULK INSERT HBOC_LIST FROM ''F:\Research\' + filename + ''' WITH (ROWTERMINATOR='',\n'', FIELDTERMINATOR='',''); ' FROM files
DELETE files WHERE CharIndex(filename, @sql)>0
EXEC(@sql)

END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-06 : 17:56:00

Are you sure the row and field terminator settings are correct for the files you're importing?

You may want to change this to:

SELECT TOP 5 @sql=@sql+'BULK INSERT HBOC_LIST FROM ''F:\Research\' + filename + ''' WITH (ROWTERMINATOR='',\n'', FIELDTERMINATOR='',''); ' FROM files

If the file names are long and/or there are a lot of files the sql might exceed the 8000 byte limit and may throw an error. Using TOP will avoid this.

AlDragon
Starting Member

12 Posts

Posted - 2004-12-06 : 22:58:15

Try this I use SET instead of SELECT on @sql and a cursor for the file names

DECLARE @sql varchar(8000)
DECLARE @FILES VARCHAR(128)

CREATE TABLE files
([filename] varchar(128)
)

INSERT INTO files (filename)
EXEC master..xp_cmdshell 'dir/b "f:\Research\*.csv"'

DELETE
FROM files
WHERE NullIf(filename, 'File Not Found') IS NULL

DECLARE FILE_CURSOR CURSOR FOR
SELECT [FILENAME] FROM files

OPEN FILE_CURSOR

FETCH NEXT FROM FILE_CURSOR
INTO @FILES

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql=''
SET @sql=@sql+'BULK INSERT HBOC_LIST FROM ''F:\Research\' + @FILES + ''' WITH (ROWTERMINATOR='',\n'', FIELDTERMINATOR='',''); '

DELETE
FROM files WHERE [filename] = @FILES

--Change this back to EXEC (@sql)
PRINT @sql

FETCH NEXT FROM FILE_CURSOR
INTO @FILES

END

CLOSE FILE_CURSOR
DEALLOCATE FILE_CURSOR

Al Franzini

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-07 : 08:10:16

eeeeeeeeeeeeeeeewwwwwwwwwwwwwwwwwww cursors

AlDragon
Starting Member

12 Posts

Posted - 2004-12-07 : 09:00:12

quote:
Originally posted by robvolk

eeeeeeeeeeeeeeeewwwwwwwwwwwwwwwwwww cursors

Sometimes it is necessary to use them. Looping thru file names to get them imported is no big deal in this situation, but some people use cursors for the wrong situations.

Al Franzini

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-07 : 09:29:55

I would say this is one of those situations. And if by "sometimes" you mean 0.00000001% of time, or less, I'd agree.

AlDragon
Starting Member

12 Posts

Posted - 2004-12-07 : 10:15:20

quote:
Originally posted by robvolk

I would say this is one of those situations. And if by "sometimes" you mean 0.00000001% of time, or less, I'd agree.

I hardly ever use them, but that .00001% always does the trick.

Al Franzini

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-21 : 15:35:30

I have tried both suggestions and the curser doesn't give me any data and the "TOP" suggestion only gives me one record like I am already getting. Any more suggestions?

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-21 : 15:46:11

BULK INSERT HBOC_LIST FROM 'F:\FTP_Clinical_Research\CR_HBOC20041220.csv' WITH (ROWTERMINATOR=',\n ', FIELDTERMINATOR=',');

I even just tried the above alone with no other code and it doesn't error, but it doesn't give any data either. Why is this?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-21 : 16:04:16

Can you ask Jethro or Uncle Jed?

And for our twenty point bonus question..... Identify the person named "Daisy Moses".

HTH

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

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-21 : 16:17:35

Well, if we were playing for points I might just jump on that, but my critters are getting a bit aggitated and my brother and uncle Jed would agree that I was speaking a different language if I asked them. :) Anyone???? Thanks in advance for your help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-21 : 16:38:53

Actually Jethro is not your brother. He is the son of your father's sister Pearl. As such, you're cousins. Jed is your father; not your uncle.

But back to business. Is it possible you are getting an error during the bulk load? There are some error logging switches that might help nail this possibility down.

HTH

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

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-21 : 16:51:53

I am all ears for suggestions. I am not getting any errors that I know of though.

And actually Jethro is my brother really. Well that is his nickname Jethro Bodine and my little boy calls him Uncle Jeb. But as far as my SQL issues, it is always possible that there are errors, but I am not getting any error messages in the results pane.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-21 : 17:05:47

You could also set a switch that allows X number of errors to occur before aborting the bulk load. And did anything appear in the error log. I'm not sure if an error message really appears on the screen.

"Daisy Moses", by the way, was the name of the character who was referred to simply as "Granny". Played admirably by the late Irene Ryan.

HTH

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

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-21 : 17:20:24

Okay. Now you have lost me. I checked my event log and NOTHING. If that is what you were wanting me to check. As for the switch, how would I do that? Thanks for your help.

"Granny" was great! That is what my son calls my mom too! My in-laws started calling me Elliemay because of how many wild animals I have raised and My brother's friends started calling him Jethro or Bodine and I have just never really put it together until now. Um INTERESTING.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-21 : 17:38:18

From BOL:

MAXERRORS [ = max_errors ] ]

is a switch for the BULK INSERT. My thinking, however murky, was that if the bulk insert proceeds further when we allow a greater number of errors to occur, it might point us at the source of the problem. Food for thought; nothing more.

My bad on the error switch. BCP has a [-e err_file] option but I confused the two.

HTH

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

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-21 : 18:10:39

Well, it didn't give me any errors, but it didn't give me any records either. I took it back out and it didn't give me any any records again. I am SO sorry that I am being difficult. You are too great to try to help me, but it has got me stumpted. Here is my code again:

use ftsm_cr_patients

CREATE TABLE files (filename varchar(128))
INSERT INTO files (filename) EXEC master..xp_cmdshell 'dir/b "f:\Research\*.csv"'
DELETE files WHERE NullIf(filename, 'File Not Found') IS NULL

DECLARE @sql varchar(8000)
WHILE EXISTS (SELECT * FROM files)
BEGIN

SELECT @sql=''
SELECT @sql=@sql+'BULK INSERT IMPORT_LIST FROM ''F:\Research\' + filename + ''' WITH (ROWTERMINATOR='',\n'', FIELDTERMINATOR='','', MAXERRORS=1); ' FROM files
DELETE files WHERE CharIndex(filename, @sql)>0
EXEC(@sql)

END

DROP TABLE files

insert into MATCH_list
SELECT a.*
FROM IMPORT_LIST AS a INNER JOIN COMPARE_LIST AS p
ON a.ssn = p.ssn

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-21 : 18:11:37

I may not be able to check back with you until after Christmas. If not, Thanks again and HAPPY HOLIDAYS!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources