How to Create Flat File

UDA
Enthusiast

How to Create Flat File

Hi,

Please give inputs to create Flat File in Teradata containing the result set of a query.

4 REPLIES
Enthusiast

Re: How to Create Flat File

Hi Nits,
you can create a flat file using fastexport utility.

at this file:
.logtable ;

.logon /,

.begin export;

.export outfile mode record format text; /* pay attention, you have to specify both mode record and format text */

select cast(
(
cast(field1 as char(xx))
||cast(field2 as char(xx))
||
)
as char(yy)) /* you have to use this final cast where "yy" is the total lenght of the unique field you obtain linking together the fields you are exporting */

from ....
where ....

;

.end export;
.logoff;

Hope thie helps,
Bye,
TDUser
Enthusiast

Re: How to Create Flat File

You could replicate the logic of the following example bteq script (which outputs the file as a CSV formatted file):

.SET WIDTH 65531;
.LOGON demo/dbc,dbc;

.OS DEL /Q EventLog.txt
.EXPORT DATA FILE=EventLog.txt
.SET INDICDATA OFF

SELECT
Trim(coalesce(cast(DateFld as varchar(32)),''))
||'|'||Trim(coalesce(TimeFld,''))
||'|'||Trim(coalesce(UserName,''))
||'|'||Trim(coalesce(AccountName,''))
||'|'||Trim(coalesce(Event,''))
||'|'||Trim(coalesce(LogicalHostId,''))
||'|'||Trim(coalesce(IFPNo,''))
||'|'||Trim(coalesce(SessionNo,''))
||'|'||Trim(coalesce(cast(LogonDate as varchar(32)),''))
||'|'||Trim(coalesce(LogonTime,''))
||'|'||Trim(coalesce(LogonSource,''))
FROM DBC.EventLog;

.EXPORT RESET

.LOGOFF
.QUIT

Enthusiast

Re: How to Create Flat File

Please tell me where to get FastExport and BTEQ utilities.

These are freewares or come as part of Teradata.

We are currently using Teradata client version.

Regards
Nitin Bajaj
Enthusiast

Re: How to Create Flat File

Hi,
if you have Teradata RDBMS installed somewhere, on the server you'll find the executable files to run. Usually you find them in Program Files\Ncr\Teradata Client\bin\ directory.

You may execute them directly on the server or using an ETL tool.

Hope this helps,
Bye,
TDUser