Long title in bteq export to csv file distorts size of first column

Tools
Enthusiast

Long title in bteq export to csv file distorts size of first column

Hi,

I'm using bteq to export data into a csv file for opening in ms excel.

I need the selected data, along with the title of the data to show up in the excel file, however, the long title makes the size of the first column long, thus opening the excel file does not show the data in the first row (unless you double-click the edge of the row, so that the entire width of the cell will expand. Is there a way to format this such that I will still be able to see the first row upon opening the file?

Here is my command:

LOGON_STRING=`cat logon/bteq`

bteq <<- EOF

.LOGON ${LOGON_STRING};

.SET TITLEDASHES OFF;

.SET SEPARATOR ',';

.EXPORT file=/mydir/test_file.csv

.SET RTITLE "THIS IS THE TITLE OF THIS CSV FILE WE ARE EXPORTING FROM THE DATABASE";

.SET FORMAT ON;

SELECT

test_id (CHAR(8)) (TITLE 'TEST'),

test_cd (TITLE 'CODE')

from database.table;

.SET FORMAT OFF;

.EXPORT RESET

.LOGOFF

The result is this: (When this is viewed in excel, the first column values under test is not visible since the size of the column is small, and the width of the title's column is large. Also, even if i set the length of TEST to be only 8Chars long, the column automatically adds extra character before the values.

09/03/04 THIS IS THE TITLE OF THIS CSV FILE WE ARE EXPORTING FROM THE DATABASE Page 1

(this has blank spaces)TEST CODE

(this has blank spaces)TF999003 TCLSOH65DS1

(this has blank spaces)TF999002 TCMTOH3333E

(this has blank spaces)TF999006 TCHGMIMNDS1

(this has blank spaces)TF999005 TCWOTXPEAXD

(this has blank spaces)TF999004 TCRTMIMTDS0

In the unix .csv file, it shows up as:

09/03/04 THIS IS THE TITLE OF THIS CSV FILE WE ARE EXPORTING FROM THE DATABASE Page 1

(this has blank spaces)TEST ,CODE

(this has blank spaces)TF999003,TCLSOH65DS1

(this has blank spaces)TF999002,TCMTOH3333E

(this has blank spaces)TF999006,TCHGMIMNDS1

(this has blank spaces)TF999005,TCWOTXPEAXD

(this has blank spaces)TF999004,TCRTMIMTDS0

Any idea how i can remove the excess spaces before the first column's values?

Thanks!
1 REPLY

Re: Long title in bteq export to csv file distorts size of first column

Hi All,

I am trying to execute the bleow in bteqq

bteq<<EOF
.SESSIONS 4
.SET ERROROUT STDOUT ;
.logon PKDTD/BATCH_EDB,BATCH_EDB;
.EXPORT RESET;
.EXPORT REPORT FILE = /TgtFiles/BDPJV/Output/BDP_Total_Balances.csv;
.logon PKDTD/BATCH_EDB,BATCH_EDB

/*Setting format of output file*/
.SET RECORDMODE OFF;
.SET FORMAT OFF ;
.SET TITLEDASHES OFF;
.SET SEPARATOR ',';

SELECT rel.cde_lgcy_rel legacy_relationship_code, cde_lgcy_acct_mtch_rul, SUM(pos.amt_close_mkt_val) sum_amt_close_mkt_val, COUNT(pos.key_acct) count_key_acct

FROM edw1_edb_baseview.TPOS_BALANCE_TD_REPL pos, edw1_edb_baseview.tacct_lgcy_rel rel
WHERE pos.key_acct = rel.key_acct
AND cde_msdw_sec = '000000360'
AND pos.KEY_ACCT NOT IN
(
SELECT ACCT.KEY_ACCT
FROM EDW1_EDB_BASEVIEW.TCLT_ACCT ACCT
JOIN EDW1_EDB_BASEVIEW.TACCT_ROLLUPS ROLL
ON ACCT.NUM_OFFICE = ROLL.NUM_PARENT_OFFICE
AND ACCT.NUM_ACCT = ROLL.NUM_PARENT_CUST
WHERE roll.num_parent_office = roll.num_sub_office
AND roll.num_parent_cust = roll.num_sub_cust
)
GROUP BY 1,2
ORDER BY 1,2;

COMMIT;

It returns me the firdt 3 columns of the select query but not the 4th one. I need to solve tis urgently. Could you please advide??