LINUX - BTEQ - Export data to file - Removing extra spaces between the column values

Database
Enthusiast

LINUX - BTEQ - Export data to file - Removing extra spaces between the column values

Hello,

I have the following BTEQ script to write the table results to a output file 

.LOGON TDPID/Username/Pwd
.SET SEPERATOR 0
.SET WIDTH 1500
.SET TITLEDASHES OFF
.SET FORMAT OFF
.OS rm -f output.txt
.EXPORT DATA FILE=output.txt
.SET RECORDMODE OFF
SEL TOP 10* FROM TABLE;
.LOGOFF
.EXIT


Output:  5268501115929  2015-02-26          22015-10-08 00:55:00.000000+00:00                               ?5268501115929    

Desired output: 5268501115929,2015-02-26,2,2015-10-08 00:55:00.000000+00:00,?,5268501115929

I need to get rid of the extra spaces in between the columns and should be replaced with a delimiter ','.

Any help is really appreciated.

Thanks for your time.

Thanks,

C

3 REPLIES
Enthusiast

Re: LINUX - BTEQ - Export data to file - Removing extra spaces between the column values

Any one has any inputs on this. Please do share..

Enthusiast

Re: LINUX - BTEQ - Export data to file - Removing extra spaces between the column values

you should be using select cast( (cast(col1 as varchar(20))||','|| cast(col2 as varchar(32))||','||cast(coalesce(col3,'') as varchar(10))||','||cast(col4 as varchar(10))) as char(72)) from tableA.

if the columns have integer  or decimal data type then you should be using trim(both '.' from cast(col1 as varchar(15))). 

This is to avoid the special character that get generated.

Junior Contributor

Re: LINUX - BTEQ - Export data to file - Removing extra spaces between the column values

BTEQ does not export in a delimited format. 

You must either do lots of CAST & concats as krisaneesh proposed or utilize the CSV function in TD14.10: 

WITH cte AS
(
SELECT * FROM tab
)
SELECT *
FROM TABLE(CSV(NEW VARIANT_TYPE(
cte.col1
,cte.col2
,...
), ',', '')
RETURNS (x VARCHAR(32000) CHARACTER SET UNICODE)) AS t;

This will automatically CAST numeric columns to VarChar, optionally you can specify a quote character for strings.

Instead of using BTEQ you also switch to TPT which supports DELIMITED format.