how can i remove the extra spaces when using bteq to export data into a flat txt file

Tools
Fan

how can i remove the extra spaces when using bteq to export data into a flat txt file

here is the code i used:

.SET SESSION TRANSACTION ANSI

.BEGIN EXPORT SESSIONS 20;

.LOGON localtd/tduser,tduser;

.EXPORT RESET;

.EXPORT DATA FILE = d:\hubert\bteq_test_result1.txt

/*Setting format of output file*/

.SET RECORDMODE OFF;

.set FORMAT OFF;

.set width 255;

.SET SEPARATOR "|";

select

*

from  retail.employee where empno like 'Clerk#000000447';

.LOGOFF;

Here is the result and you can see there are many extra spaces before or after pipe:

Clerk#000000447|Debra Catini      |1701 W. Mellody Rd.                     |7901659353     |  8477|  77093.60|     5|1957-03-31|N      |    0|h

6 REPLIES
Senior Supporter

Re: how can i remove the extra spaces when using bteq to export data into a flat txt file

I guess some of them related to data types - so can you share the DDL of the table?

Fan

Re: how can i remove the extra spaces when using bteq to export data into a flat txt file

Hi,

thanks for quick reply,:)

here is the DDL of that table:

Show Table retail.EMPLOYEE;

CREATE SET TABLE retail.EMPLOYEE ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

EmpNo VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Name VARCHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Address VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Phone CHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

DeptNo SMALLINT,

Salary DECIMAL(8,2),

YrsExp BYTEINT,

DOB DATE FORMAT 'YYYY-MM-DD',

MedStat CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

EdLev BYTEINT,

Note VARCHAR(79) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)

UNIQUE PRIMARY INDEX ( EmpNo );

Senior Supporter

Re: how can i remove the extra spaces when using bteq to export data into a flat txt file

manual states 

"In Field Mode, BTEQ returns all data values in character format." 

And here BTEQ seems to add the extra spaces - don't see a way to overcome this with parameter settings.

And I guess you want the data in "readable" character format.

The only way to do it I know is to convert everything by hand to a single varchar like

cast(trim(ename) !! '|' !! trim(eno)  !! '|' !!  Trim(deptno)  !! '|' !! trim(address) !! '|' !!  Trim(birthday) as varchar(1000))

-> where I used a different table...

Teradata Employee

Re: how can i remove the extra spaces when using bteq to export data into a flat txt file

To eliminate the separator strings that BTEQ adds for Field Mode,  you can use...

.SET SEPARATOR 0

Enthusiast

Re: how can i remove the extra spaces when using bteq to export data into a flat txt file

How about queries like that has count, max, top and some analytic functions. I don't see a way to trim those. An example has been provided below:

SEL TOP 10*  FROM  TABLE WHERE COL1 = 2 AND COL2 = '2015-03-10'

Output: 5268501126230  2015-02-28          22015-10-15 00:52:00.000000+00:00                               ?5268501126230    ?                                    

Desired o/p: 5268501126230,2015-02-28,2,2015-10-15 00:52:00.000000+00:00,?,5268501126230,?    

where ? are NULL's.

I need to remove those extra spaces and get it replaced by any delimiter ','.

Thanks.

Enthusiast

Re: how can i remove the extra spaces when using bteq to export data into a flat txt file

If you want to output data in bteq and get the data without the spaces between the delimiters, then you wouldn't be able to use 'Sel TOP 10 *...'  You would have to list the columns out, then convert each one to character that isn't character, trim it, and convert nulls to something you want to see, such as blanks or whatever.   There really isn't any other way around it using that tool.  

Also remember that if you concatentate fields that contain NULL values it will turn the whole row null, so you would need to address that by changing the NULL to a blank (or whatever else you want to reference it as) on export.

So your data might look like this

5268501126230,2015-02-28,2,2015-10-15 00:52:00.000000+00:00,,5268501126230,