Bulk insert format file - 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

Old Forums

CLOSED - General SQL Server

Bulk insert format file

Author

Topic

acid_bratt
Starting Member

13 Posts

Posted - 2003-07-30 : 01:53:03

hi..

i just want to ask if the format file in bulk insert using sql 2000 requires collation name?

thanks..

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-30 : 02:35:07

Yes, it demands a collation name. Leaving it blank will just give you an error.

acid_bratt
Starting Member

13 Posts

Posted - 2003-07-30 : 05:05:02

thanks for the answer...

another question...

in a format file with a header & trailer records... is there a way i can remove these records before proceeding to bulk insert?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-30 : 07:36:37

A format file doesn't have headers or trailers, and it won't be able to discard or skip headers/trailers in the data file. Usually the way to work around this is to import all the data into a big varchar column and then parse it from there. You can use a WHERE clause to identify header rows from detail rows and handle them accordingly.

Do you have a sample of the file you're importing, and the table(s) it goes into? Posting that can help us refine a solution for you.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-30 : 08:58:22

Damn that sounds SOOOOOOOOO familiar...

Where did I see that before?

Oh Yeah...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27752

Brett

8-)

acid_bratt
Starting Member

13 Posts

Posted - 2003-07-30 : 20:16:30

thanks for the info rob!..

i have here a sample of the file i have to insert in a table but with header & trailer record.

filename: M03001AA.txt
----------------------------------------
HEADERM03001AA
XXXX010111 0111 BRANCH 1
YYYY010111 0111 BRANCH 1
XXXX010222 0222 BRANCH 2
YYYY010222 0222 BRANCH 2
XXXX020999 0999 DUMMY BRANCH
YYYY020999 0999 DUMMY BRANCH
TRAILER06
----------------------------------------

this file is downloaded from mainframe & we need the header & trailer for some validations such as filenames, record #s consistencies, etc... but after validations, how can i get rid of the header & trailer?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-30 : 23:45:00

CREATE TABLE #import(linedata varchar(8000) null)
BULK INSERT #import FROM 'C:\M03001AA.txt' WITH(FIELDTERMINATOR='')
...statements to parse out header and footer
SELECT SubString(linedata, 8, 2) FROM #import WHERE linedata LIKE 'TRAILER%' --rowcount, for example
SELECT SubString(linedata, 7, 20) FROM #import WHERE linedata LIKE 'HEADER%' -- filename
...statements to parse out detail
SELECT whatever FROM #import WHERE linedata NOT LIKE 'HEADER%' AND linedata NOT LIKE 'TRAILER%'
...etc. etc.

If you want to get rid of the header and trailer:

DELETE #import WHERE linedata LIKE 'HEADER%' OR linedata LIKE 'TRAILER%'

But the WHERE clause listed above for the detail will ignore it.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-31 : 11:44:30

And since it's really a staging table, you might as well leave it...

You'll have to parse out the details with substring to theur final destination anyway...

Is this a totally manual process?

Brett

8-)

acid_bratt
Starting Member

13 Posts

Posted - 2003-08-04 : 03:12:37

brett, it's not a manual process..

thanks for all your help guys..

acid_bratt
Starting Member

13 Posts

Posted - 2003-08-04 : 03:12:45

brett, it's not a manual process..

thanks for all your help guys..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources