here is the code i used:
.SET SESSION TRANSACTION ANSI
.BEGIN EXPORT SESSIONS 20;
.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 "|";
from retail.employee where empno like 'Clerk#000000447';
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
I guess some of them related to data types - so can you share the DDL of the table?
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,
DOB DATE FORMAT 'YYYY-MM-DD',
MedStat CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
Note VARCHAR(79) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX ( EmpNo );
"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
To eliminate the separator strings that BTEQ adds for Field Mode, you can use...
.SET SEPARATOR 0
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 ','.
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