All Forums Tools
Rohit Ranjan 20 posts Joined 05/12
29 Oct 2014
BTEQ export CSV issue

hi,
I am running a BTEQ script in Unix and trying to export the result in CSV format. I have four columns in the teradata table. When i get the exported result set in CSV, all the four different columns of the table are coming under column A only of CSV file. Not sure how to recrify this.
bteq<<EOF
.logon ${TD_LOGON};

.EXPORT REPORT FILE = table.csv
sel * from abc;
.EXPORT RESET

.LOGOFF
.QUIT
EOF
 
Thanks,
Rohit
 

Raja_KT 1246 posts Joined 07/09
29 Oct 2014

Try to cast each field.
This example may help you.
https://forums.teradata.com/forum/database/help-needed-in-bteq-export

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Rohit Ranjan 20 posts Joined 05/12
30 Oct 2014

I tried casting the column names to varchar(20). but it didn't worked. I got column names like below

((((((eno||'

')||ename)||'

')||dno)||'

')||sal)

 
Thanks,
Rohit

Raja_KT 1246 posts Joined 07/09
30 Oct 2014

You  cat in unix?
Share your table details and some sample data.
I selected 3 fields and it exports well, without cast or anything. 
You can try with 2 or 3 fields first and see.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Rohit Ranjan 20 posts Joined 05/12
30 Oct 2014

Yes, i am running this bteq script in Unix. Below is the table details.
create table abc
( eno integer,
 ename varchar(20),
dno integer,
sal decimal(10,2)
)
unique primary index (eno)
insert into abc (1,'a', 10, 10000);
insert into abc (2,'b', 20, 15000);
insert into abc (3,'c', 20, 20000);
insert into abc (4,'d', 30, 40000);
Below is the script
bteq<<EOF
.logon ${TD_LOGON};

.EXPORT REPORT FILE = c:/xyz.csv
sel * from abc;
.SET TITLEDASHES OFF
.EXPORT RESET
.LOGOFF
.QUIT
EOF
 

dnoeth 4628 posts Joined 11/04
30 Oct 2014

Hi Rohit,
you can write any file extension, a REPORT will always export in the same format, readable fixed width text. There's no built in support for delimited export in BTEQ.
You have to concat all columns in the SELECT, then add a (TITLE '') to the resulting column and use TITLEDASHES OFF.
 
Or you simply switch to TPT plus a Format = 'Delimited' for the DataConnector.

Dieter

Raja_KT 1246 posts Joined 07/09
30 Oct 2014

With the above data, it works perfectly fine with me and it shows the records perfectly since  the field lengths are small
 
even with simple code you have
.export report file=$HOME/abc.csv
sel * from db.abc;
.export reset
.logoff
.quit
Yes as Dieter  suggested,I feel that we should inch towards using  tpt more often than not.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Rohit Ranjan 20 posts Joined 05/12
31 Oct 2014

Thanks a lot Raja and Dieter for your reply.
 After adding (TITLE '') i am getting result in below format.
 
2 a 20 15000
 
4 b 30 40000
 
1 c 10 10000
 
3 d 20 20000
 
However, i want it in  below format
 
 
eno. ename dept   sal
2      a    20 15000 
 
4 b   30  40000 
 
1 c   10   10000  
 
3 d   20 20000 
 
.EXPORT REPORT FILE = c:/abc.csv
 
sel eno ||','||ename ||','||dno ||','||sal (TITLE '') from xyz;
 
.EXPORT RESET
 
.LOGOFF
 .QUIT
 EOF
 
Thanks,
 Rohit
 

dnoeth 4628 posts Joined 11/04
31 Oct 2014

Hi Rohit,
you must write the column header with a seperate Select:

sel 'eno,ename,dno,sal' (TITLE '');

 

Dieter

Rohit Ranjan 20 posts Joined 05/12
31 Oct 2014

Thanks Dieter. As always you are the life saver:)

You must sign in to leave a comment.