While using a fast export script it's possible that the output created is not in a readable format.
In order to have a readable output you have to cast as char and delimit all the fields you are exporting.
If you have a table with a lot of fields (or you need to export several tables) it takes a lot of time to type in all the column names and column lengths.
Below script handles with these issues (including the addititon of a column header).
It's not fully dynamical (you have to copy the results - without header - of the 4 queries and paste them into the script). You can make it 100% automatic by exporting a dynamical sql with BTEQ and read it with FastExport.
--------------------------------------------------------------------------
.LOGTABLE abc.datastore_log ;
.logon ab/ab,;
.BEGIN EXPORT
SESSIONS 1
TENACITY 5
SLEEP 1;
.EXPORT OUTFILE c:\OUT.txt MODE RECORD
FORMAT TEXT;
/**********************************************************
**
** Header Record
**
**********************************************************/
sel
/*************************************************************
**
** COPY OUTPUT FROM QUERY BELOW
** (without headers)
**
** sel 'C'||CAST(CSUM(1,1) AS CHAR(4)) a , '(CHAR('||
** trim(characters(
** TRIM(TRAILING' ' FROM c.columnname) ))
**
** ||'))
**
** 'b,
** case when 'C'||trim(a.maxc) <> a then ','
** ||'27'xc
** || ','||'27'xc
** || '(CHAR(1)),' else '' end c
**
** FROM dbc.columns c
** ,
**
** (sel max(c) over (partition by 1) maxc, columnname
** from
** (sel columnname, CSUM(1,1) c from dbc.columns
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME')a)a
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME'
** and c.columnname = a.columnname
**
************************************************************/
-- and paste it here:
FROM
(sel
/*************************************************************
**
** COPY OUTPUT FROM QUERY BELOW
** (without headers)
**
** sel '27'xc||trim(c.COLUMNNAME)||'27'xc a, ' AS ' b
** , 'C'||CAST(CSUM(1,1) AS CHAR(4)) c ,
**
**
** case when 'C'||trim(a.maxc) <> c then ', '
** else '' end d
** FROM dbc.columns c
** ,
**
** (sel max(c) over (partition by 1) maxc, columnname
** from
** (sel columnname, CSUM(1,1) c from dbc.columns
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME')a)a
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME'
** and c.columnname = a.columnname
**
************************************************************/
-- and paste it here:
) A;
/**********************************************************
**
** Detail Record
**
**********************************************************/
sel
/*************************************************************
**
** COPY OUTPUT FROM QUERY BELOW
** (without headers)
**
**
** sel
**
** 'C'||CAST(CSUM(1,1) AS CHAR(4)) a
** ,
** case
**
** when columntype = 'D' then '(CHAR(18))'
** when columntype = 'DA' then '(CHAR('||
** trim(characters(columnformat))
** ||'))
** 'when columntype in ('I','I1','I2','I8','BF','BO','BV','CF','CO','CV') then '(CHAR('||
** substring(columnformat,position ('(' in columnformat)+1,position (')' in columnformat)-
** position ('(' in columnformat)-1)||'))'
** when columntype in ('CF','CV','DM','TS','DS','AT','F','DY','PS','HM','HR','HS','MS') then
** '(CHAR('||
** TRIM(CAST(COLUMNLENGTH AS integer))
** ||'))'
** else
** '(CHAR('||
** TRIM(CAST(COLUMNLENGTH AS integer))
** ||'))'
** end
** b
**
** ,
**
** case when 'C'||trim(a.maxc) <> a then ','
** ||'27'xc
** || ','||'27'xc
** || '(CHAR(1)),' else '' end c
**
** FROM dbc.columns c
**
** ,
**
** (sel max(c) over (partition by 1) maxc, columnname
** from
** (sel columnname, CSUM(1,1) c from dbc.columns
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME')a)a
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME'
** and c.columnname = a.columnname
**
************************************************************/
-- and paste it here:
FROM
(sel
/*************************************************************
**
** COPY OUTPUT FROM QUERY BELOW
** (without headers)
**
** sel
**
** case when columntype in ('AS','DS','TS','DM','DS','DY','HM','HR','HS','MS','TS')
** then c.COLUMNNAME
** else 'TRIM (BOTH FROM '|| trim(c.COLUMNNAME) ||')'
** END a
**
** , ' AS ', 'C'||CAST(CSUM(1,1) AS CHAR(4)) b ,
**
** case when 'C'||trim(a.maxc) <> b then ', '
** else '' end d
** FROM dbc.columns c
** ,
**
** (sel max(c) over (partition by 1) maxc, columnname
** from
** (sel columnname, CSUM(1,1) c from dbc.columns
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME')a)a
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME'
** and c.columnname = a.columnname
**
************************************************************/
-- and paste it here:
FROM
DATABASENAME.TABLENAME ) A
;
.END EXPORT;
/**********************************************************
** Logoff the Teradata Database
**********************************************************/
.LOGOFF;
--------------------------------------------------------------------------
