0

I am exporting to a text file from BTEQ and I am getting whitespace padding to the maximum length of each of my columns in my output text file. For example I just want customer_name and post_code columns to look like;

Mr Always Teste,AB10 1AB,

but it on my file it is like;

Mr Always Teste                                                                                                                                                                                                  ,AB10 1AB                       ,

I just want the data I need and not all the whitespace at the end as I need to import the data cleanly after exporting.

My export script contains:

.SET TITLEDASHES OFF
.SET SEPARATOR ','
.SET FORMAT OFF (ON MAKES IT ALL WEIRD)
.SET NULL ''
.SET WIDTH 1000

Forgive me I can't paste any data as it's on another pc and its all confidential anyway.

Example column definitions are (they are all like this with varying lengths):

Name: customer_name Type: CV Format: X(208) Max Length: 208

Like I say, this and all the other columns pad out to their length with whitespace in the output file. Anything I can do about it?

Shuko
  • 3
  • 2

1 Answers1

1

REPORT format in BTEQ is fixed width, setting the SEPERATOR will not remove spaces. But you might return a single column only using the CSV function to return a delimited string:

with cte as
 (
   select * from tab
 )
select * 
from table(CSV(new variant_type(cte.col1 -- list all columns here
                               ,cte.col2
                               ,cte.col3)
              ,','  -- seperator
              ,'"') -- string delimiter
returns (s varchar(10000)) as t;

This is much easier and better performing than CONCAT & COALESCE all columns.

dnoeth
  • 59,503
  • 4
  • 39
  • 56