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 |
|
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----------------------------------------HEADERM03001AAXXXX010111 0111 BRANCH 1YYYY010111 0111 BRANCH 1XXXX010222 0222 BRANCH 2YYYY010222 0222 BRANCH 2XXXX020999 0999 DUMMY BRANCHYYYY020999 0999 DUMMY BRANCHTRAILER06----------------------------------------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 footerSELECT SubString(linedata, 8, 2) FROM #import WHERE linedata LIKE 'TRAILER%' --rowcount, for exampleSELECT SubString(linedata, 7, 20) FROM #import WHERE linedata LIKE 'HEADER%' -- filename...statements to parse out detailSELECT 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?Brett8-) |
|
|
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.. |
|
|
|