export using bteq has very unformatted data

Tools & Utilities
Enthusiast

export using bteq has very unformatted data

hi

i wrote a BTEQ script as follows:

.LOGON TDCNS1/agandhi1,Para11Comp&me

create volatile table sample_table AS (sel * from appl_dwmdm_01.v_ied_inv_sfp_lot_inv_dtl sample 1000) with data ON COMMIT PRESERVE ROWS;
create volatile table supPlan_inv_Result AS (sel * from appl_dwmdm_01.v_ied_inv_sfp_lot_inv_dtl sample 0) with data ON COMMIT PRESERVE ROWS;

INSERT INTO supPlan_inv_Result sel * from sample_table where sfp_id not in (select itm_dsc from appl_dwmdm_01.r_itm);

.export DATA FILE=test.txt
sel * from supPlan_inv_Result;
.LOGOFF
.exit

the output file contain data which is not delimited properly and contain various unknowns letter like |- and many which are not even present in keyboard.

i have to import this data to a table.
please do suggest the reason and possible solution

Thanks
Akhshay

1 REPLY
Enthusiast

Re: export using bteq has very unformatted data

I had a similar problem trying to export the results of a query to a flat file on UNIX.
I had to use .EXPORT REPORT instead of .EXPORT DATA. Then filter out the header information using the "awk" command to keep only the detail records.

.EXPORT DATA FILE=//filepath/outputfile.rpt
SELECT F1 || '~' || F2 AS OUTPUT_RECORD
FROM TABLE1
;
.EXPORT RESET;

The output file at this point will include the column header and a header seperator line.
OUTPUT_RECORD
---------------------
001~FINANCE
002~MARKETING
003~MANUFACTURING

In your calling script filter out only the detail lines and put in another file.
awk '/~/' //filepath/outputfile.rpt > //filepath/outputfile.txt

now you should have only your exported detail records in //filepath/outputfile.txt
001~FINANCE
002~MARKETING
003~MANUFACTURING