Problem in Creating Delimited Data file using FastExport.

[mtlrsk_12345]

Hi All,

My Fastexport Script:-

.LOGTABLE XXXX.XX_test_exp_LOG;
.LOGON XXXX/XXXXX,XXXXX;
.BEGIN EXPORT SESSIONS 16 TENACITY 4 SLEEP 6;
.EXPORT OUTFILE /abc/def/test_exp.dat;
LOCKING XXXX.test_exp FOR ACCESS
SELECT
TRIM(COALESCE(CAST(col1 AS VARCHAR(12)),'')) || '|' ||
TRIM(COALESCE(CAST(col2 AS VARCHAR(10)),'')) || '|' ||
TRIM(COALESCE(CAST(col3 AS VARCHAR(5)),''))
FROM XXXX.test_exp;
.END EXPORT;
LOGOFF;

Data in table.
5 e e
3 c c
4 d d
1 a a
2 b b

Data exported by using Fastexport.
^H^@^@^E^@5|e|e
^H^@^@^E^@3|c|c
^H^@^@^E^@4|d|d
^H^@^@^E^@1|a|a
^H^@^@^E^@2|b|b

Question:-

How can I export the data from this table in delimited format. I want to avoid the starting special character appended in each row.

Please advise at the earliest.

Rgds,
mtlrsk.

[feinholz]

I believe you need to tell FastExport that you do NOT want indicators (I think FastExport exports the data with the indicator bytes), and I think you need to specify a record format of TEXT (I believe FastExport defaults to the FastLoad format).

[mtlrsk_12345]

Hi,
Thanks for a reply, but i also tried the options what you have suggested. following is the sample script:-

.LOGTABLE XXXX.XX_test_exp_LOG;
.LOGON XXXX/XXXXX,XXXXX;
.BEGIN EXPORT SESSIONS 16 TENACITY 4 SLEEP 6;
.EXPORT OUTFILE /abc/def/test_exp.dat MODE RECORD FORMAT TEXT;
LOCKING XXXX.test_exp FOR ACCESS
SELECT
TRIM(COALESCE(CAST(col1 AS VARCHAR(12)),'')) || '|' ||
TRIM(COALESCE(CAST(col2 AS VARCHAR(10)),'')) || '|' ||
TRIM(COALESCE(CAST(col3 AS VARCHAR(5)),''))
FROM XXXX.test_exp;
.END EXPORT;
LOGOFF;

But still this does not help, i guess the problem lies with the casting, because if i cast the complete select statement with fixed char(XXX) then it works fine.

SELECT CAST(('' ||
TRIM(COALESCE(CAST(col1 AS CHAR(12)),'')) || '|' ||
TRIM(COALESCE(CAST(col2 AS VARCHAR(10)),'')) || '|' ||
TRIM(COALESCE(CAST(col3 AS VARCHAR(5)),''))) AS CHAR(30))
FROM XXXX.test_exp;

5|e|e
3|c|c
4|d|d
1|a|a
2|b|b

But the issue lies with what i need to cast to ? I need to know the complete row length for casting. Is there a way?

Thx for the suggestions and waiting for reply,
Rgds,
mtlrsk

[Vador]

If you don't cast then entire concatenation expression, Teradata will consider it as a VARCHAR, then when Fastexport produce the export file, it add a 2 byte length field at the begining of each row.
CHAR(xx) datatype is a fixed type, so there is no length information at the beginig of the field. But you must take care,if you are casting the record with a CHAR(xx) wjish is less length than the record, truncation will occure.

[Vador]

By th way there is another way to produce such files, did you consider TPT export operator in conjuction of a dataconnector consumer operator?
TPT export will produce fields, and the dataconnector as consumer will produce the right file with the right field separator.