BTEQ export CSV issue

Tools
Enthusiast

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

9 REPLIES
Enthusiast

Re: BTEQ export CSV issue

Try to cast each field.

This example may help you.

https://forums.teradata.com/forum/database/help-needed-in-bteq-export

Enthusiast

Re: BTEQ export CSV issue

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

Enthusiast

Re: BTEQ export CSV issue

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.

Enthusiast

Re: BTEQ export CSV issue

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

Junior Contributor

Re: BTEQ export CSV issue

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.

Enthusiast

Re: BTEQ export CSV issue

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.

Enthusiast

Re: BTEQ export CSV issue

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

Junior Contributor

Re: BTEQ export CSV issue

Hi Rohit,

you must write the column header with a seperate Select:

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

Re: BTEQ export CSV issue

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