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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Bulk insert format file
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

acid_bratt
Starting Member

13 Posts

Posted - 07/30/2003 :  01:53:03  Show Profile
hi..

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

thanks..

Andraax
Aged Yak Warrior

Sweden
790 Posts

Posted - 07/30/2003 :  02:35:07  Show Profile
Yes, it demands a collation name. Leaving it blank will just give you an error.
Go to Top of Page

acid_bratt
Starting Member

13 Posts

Posted - 07/30/2003 :  05:05:02  Show Profile
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?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 07/30/2003 :  07:36:37  Show Profile  Visit robvolk's Homepage
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/30/2003 :  08:58:22  Show Profile
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-)
Go to Top of Page

acid_bratt
Starting Member

13 Posts

Posted - 07/30/2003 :  20:16:30  Show Profile
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?


Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 07/30/2003 :  23:45:00  Show Profile  Visit robvolk's Homepage
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/31/2003 :  11:44:30  Show Profile
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-)
Go to Top of Page

acid_bratt
Starting Member

13 Posts

Posted - 08/04/2003 :  03:12:37  Show Profile
brett, it's not a manual process..

thanks for all your help guys..
Go to Top of Page

acid_bratt
Starting Member

13 Posts

Posted - 08/04/2003 :  03:12:45  Show Profile
brett, it's not a manual process..

thanks for all your help guys..
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000