USING FASTEXPORT TO EXPORT TAB-DELIMITED TEXT FILE

we must remember one thing that fastexport is used for export flat-file from Teradata and the output file is used for load to tables.
so do not try to open the exported file, that's not the purpose of fastexport.

but, sometimes, we indid have to use the exported file as a text file, eg. tab-delimited text file

if all output columns are fixed length char then we just need to add tab as dummy column between columns like

.EXPORT
OUTFILE E:/DAT3/CNV/DATA23/SPND040060.DAT
MODE RECORD
FORMAT TEXT;


SELECT
ADDRESS1(CHAR(20))
,' '||ADDRESS2(CHAR(20))
,' '||ADDRESS3(CHAR(20))
,' '||ADDRESS4(CHAR(20))
FROM
M_VM_KNRYO


.END EXPORT;

but, if any column is varchar, instead of fixed length char, we will got some trouble here
because teradata will place some indicators includes the infomation of the following varchar column, at each head of varchar column . eg. length of the varchar following

in this case, we can use some tricks
like, combine all output all columns as one, separated by tab

.EXPORT
OUTFILE E:/DAT3/CNV/DATA23/SPND040060.DAT
MODE RECORD
FORMAT TEXT;


SELECT
ADDRESS1
||' '||ADDRESS2
||' '||ADDRESS3
||' '||ADDRESS4
FROM
M_VM_KNRYO


.END EXPORT;

Although, as a solo varchar column, on the head of each line still exist the 2bytes indicators, we can easily delete it by external tools



FASTEXPORT的目的，原本便是为了输出LOAD所需的数据，并非用来导出可编辑的文本文件。
但是有时候我们不得不这么做。比如说，输出一个TAB间隔的文本文件，提供给其他系统。

如果每个字段都是定长，只需要在每两个字段间，手工添加TAB
但是如果有VARCHAR,那么事情就变得麻烦起来，Teradata在Fastexport可变长字段的时候，会在每个变长字段前添加某些标记信息，比如说变长字段的长度等
这些标记会被文本编辑器识别为乱码，这时候，我们可以把所有的字段和tab拼接成一个单独的字段，整体输出
这样只有在每行开头输出标记信息，可以容易的用外部工具删除

当然了，数据量小的时候，还是用定长，或者干脆用BTEQ来EXPORT容易些

Tweet