I have the following BTEQ script to write the table results to a output file
.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;
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.
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
FROM TABLE(CSV(NEW VARIANT_TYPE(
), ',', '')
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.
Thanks dnoeth, I was able to utilize this in BTEQ and it worked perfectly. I was wondering if there is a way to perform a similar extract using FastExport to a node attached external hard drive?
Of course, same as for BTEQ: use CSV or better switch to TPT EXPORT using Delimited format :-)