All Forums Tools
hubert 2 posts Joined 02/12
16 Feb 2012
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
 

ulrich 816 posts Joined 09/09
16 Feb 2012

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

hubert 2 posts Joined 02/12
16 Feb 2012

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 );

ulrich 816 posts Joined 09/09
16 Feb 2012

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...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

SuzanneA 9 posts Joined 05/09
16 Feb 2012

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

.SET SEPARATOR 0

Chaitu1015 5 posts Joined 10/15
28 Oct 2015

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.
 
       

Tuen 44 posts Joined 07/05
12 Nov 2015

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, 
 

You must sign in to leave a comment.