FastExport to Text

Database
Enthusiast

FastExport to Text

The FastExport script I am using are:

.BEGIN EXPORT
TENACITY 4
SESSIONS 10
SLEEP 6;
.EXPORT OUTFILE 'e:\datafile\agr_farm.out' mode record FORMAT text;
Select COVERAGE_CD,STATE_CD,COUNTY_CD,CROP_CD,INS_PLAN_ID,CROP_YR_ID FROM CAESTG1.AGR_FARM;
.END EXPORT;

Obviously the FORMAT text dosen't work. Do I miss anything in the script?

Thanks in advance!
Ivy
8 REPLIES
Teradata Employee

Re: FastExport to Text

Hello!

Looking at you FE script, this should produce a tab-delimited text file. Fast Export exports to tab-delimited files as standard.

When you are running the script, what kind of file are you getting as your output?
Enthusiast

Re: FastExport to Text

It is a tab delimited file. the output file name called agr_farm.out. And I used ultraedit to open it.
But the result is wired. Following is one record of the output.

L  0063? Ð   997Ð  997FAd è Ý× L | d 1 9W õ …* Ž îj à t 00
A5  0063? Ñ   997Ñ  997CRd Z ÅÔ

Thanks,
Ivy
Enthusiast

Re: FastExport to Text

Here is the FE log file

========================================================================
= =
= FastExport Utility Release FEXP.07.06.03.000 =
= Platform WIN32 =
= =
========================================================================
= =
= Copyright 1990-2003, NCR Corporation. ALL RIGHTS RESERVED. =
= =
========================================================================
**** 11:21:56 UTY2411 Processing start date: WED JUN 11, 2008
========================================================================
= =
= Logon/Connection =
= =
========================================================================
0001 .ROUTE MESSAGES WITH ECHO TO FILE 'e:\datafile\agr_farm.out.log' ;
0002 .LOGTABLE FE_AGR_FARM;
0003 .DATEFORM ANSIDATE;
**** 11:21:56 UTY1200 DATEFORM has been set to ANSIDATE.
0004 .LOGON TAR/ivy,;
**** 11:21:59 UTY8400 Default character set: ASCII
**** 11:21:59 UTY8400 Maximum supported buffer size: 64K
**** 11:22:02 UTY6211 A successful connect was made to the RDBMS.
**** 11:22:02 UTY6217 Logtable 'UPDATEAREA.FE_AGR_FARM' has been created.
========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0005 .BEGIN EXPORT
TENACITY 4
SESSIONS 10
SLEEP 6;
0006 .EXPORT OUTFILE 'e:\datafile\agr_farm.out' mode record FORMAT text;
0007 Select
COVERAGE_CD,STATE_CD,COUNTY_CD,CROP_CD,INS_PLAN_ID,CROP_YR_ID,AGR_SEQ_NBR,C
ROP_TYPE_CD,REINS_YR_ID,PRACTICE_CD,RO_PAIRING_CD,COV_LEV_PCT,PIC_SVCG_CNTY
_CD,POLICY_NBR,AGR_PAY_RATE,EFA_PREM_DISCNT,CU_NBR,EFA_DISC_CD,AGR_COMOD_NB
R,AGR_APPRVD_AGR,AGR_MPCI_LIAB,AGR_LIABILITY,AGR_TOT_WTD_FM_RT,AGR_DVRSTY_F
ACT,AGR_RATE,AGR_TOT_PREM,AGR_PROD_PREM,WRITN_AGMT_FLG,WRITN_AGMT_NBR,WRITN
_AGMT_TYP,SUBSIDY,ADDT_SUBS_FLG,ADDT_SUBSIDY,EXPECT_INCOME,APPRV_EXPNSE,ALT
_BEARING_FLG,APPROVAL_NBR,ANIMAL_EXP,RECORD_NBR,STPRV_SUBS_FLG,STPRV_SUBSID
Y,POLICY_REPORTED_DT,PROD_TAX_ID,REVIEWER_SIGN_DT,REVIEWER_SSN,RVW_FLG,CP_C
ROP_TYPE_CD FROM CAESTG1.AGR_FARM;
0008 .END EXPORT;
========================================================================
= =
= FastExport Initial Phase =
= =
========================================================================
**** 11:22:02 UTY8700 Options in effect for this FastExport task:
. Sessions: 10 session(s).
. Mode: RECORD
. Blocksize: 64260 bytes.
. Outlimit: No limit in effect.
. Tenacity: 4 hour limit to successfully connect load sessions.
. Sleep : 6 minute(s).
**** 11:22:02 UTY8715 FastExport is submitting the following request:
Select NULL from UPDATEAREA.FE_AGR_FARM where (LogType = 220) and (Seq = 1)
and (FExptSeq = 0);
**** 11:22:05 UTY8705 EXPORT session(s) requested: 10.
**** 11:22:05 UTY8706 EXPORT session(s) connected: 10.
**** 11:22:05 UTY8715 FastExport is submitting the following request:
BT;BEGIN FASTEXPORT;
**** 11:22:05 UTY8715 FastExport is submitting the following request:
SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER), MiscInt3 (INTEGER),FExptSeq
(INTEGER), FExptCkpt (VARBYTE(1024)) from UPDATEAREA.FE_AGR_FARM WHERE
(LogType = 210) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq) from
UPDATEAREA.FE_AGR_FARM where (LogType = 210) and (Seq = 1)));
**** 11:22:05 UTY8715 FastExport is submitting the following request:
SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER), MiscInt3 (INTEGER), FExptSeq
(INTEGER), FExptCkpt (VARBYTE(1024)) from UPDATEAREA.FE_AGR_FARM WHERE
(LogType = 212) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq) from
UPDATEAREA.FE_AGR_FARM where (LogType = 212) and (Seq = 1)));
**** 11:22:05 UTY8715 FastExport is submitting the following request:
Select
COVERAGE_CD,STATE_CD,COUNTY_CD,CROP_CD,INS_PLAN_ID,CROP_YR_ID,AGR_SEQ_NBR,C
ROP_TYPE_CD,REINS_YR_ID,PRACTICE_CD,RO_PAIRING_CD,COV_LEV_PCT,PIC_SVCG_CNTY
_CD,POLICY_NBR,AGR_PAY_RATE,EFA_PREM_DISCNT,CU_NBR,EFA_DISC_CD,AGR_COMOD_NB
R,AGR_APPRVD_AGR,AGR_MPCI_LIAB,AGR_LIABILITY,AGR_TOT_WTD_FM_RT,AGR_DVRSTY_F
ACT,AGR_RATE,AGR_TOT_PREM,AGR_PROD_PREM,WRITN_AGMT_FLG,WRITN_AGMT_NBR,WRITN
_AGMT_TYP,SUBSIDY,ADDT_SUBS_FLG,ADDT_SUBSIDY,EXPECT_INCOME,APPRV_EXPNSE,ALT
_BEARING_FLG,APPROVAL_NBR,ANIMAL_EXP,RECORD_NBR,STPRV_SUBS_FLG,STPRV_SUBSID
Y,POLICY_REPORTED_DT,PROD_TAX_ID,REVIEWER_SIGN_DT,REVIEWER_SSN,RVW_FLG,CP_C
ROP_TYPE_CD FROM CAESTG1.AGR_FARM;
**** 11:22:05 UTY8724 Select request submitted to the RDBMS.
**** 11:22:06 UTY8725 Select execution completed. 40 data blocks generated.
**** 11:22:06 UTY8715 FastExport is submitting the following request:
INS UPDATEAREA.FE_AGR_FARM (LogType, Seq) VALUES (220, 1)
**** 11:22:06 UTY8715 FastExport is submitting the following request:
END FASTEXPORT;ET;
**** 11:22:08 UTY8710 Processing complete for this FastExport task.
========================================================================
= =
= FastExport Task Complete =
= =
========================================================================
**** 11:22:09 UTY8722 7015 total records written to output file.

0009 .LOGOFF;
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 11:22:10 UTY6216 The restart log table has been dropped.
**** 11:22:11 UTY6212 A successful disconnect was made from the RDBMS.
**** 11:22:11 UTY2410 Total processor time used = '0.21875 Seconds'
. Start : 11:21:56 - WED JUN 11, 2008
. End : 11:22:11 - WED JUN 11, 2008
. Highest return code encountered = '0'.
Enthusiast

Re: FastExport to Text

Hi,

Dont ever open a data file which you got. you can only use it to load an other table.
Dont verify your data by opening your file. Just load an other table and verify your data.

Thanks,
Bharath Rajagopal
Teradata Certified Master
Enthusiast

Re: FastExport to Text

But we need a tab delimited flat file output as our final result.
Enthusiast

Re: FastExport to Text

Hi,
I've similar problem. I need generate a flat file with pipes delimiters (variable length records), but output file add two bytes on each record at begining. It appears:
4 L |PIN de SIM Card de GSM|PIN de SIM Card de GSM
@ Q |Reservado para nro de Cuenta|Reservado para nro de Cuenta
-1 |No Informado|No informado

I want to load in another DB and I need something like that:
L |PIN de SIM Card de GSM|PIN de SIM Card de GSM
Q |Reservado para nro de Cuenta|Reservado para nro de Cuenta
-1 |No Informado|No informado

The FE scripts is:

.LOGTABLE logtb;
.LOGON td/user,pass;

.BEGIN EXPORT SESSIONS 4
DATAENCRYPTION OFF;

.EXPORT OUTFILE output_file.txt
FORMAT TEXT
MODE RECORD;

SELECT
Code||'|'||
Coalesce(Name,'')||'|'||
Coalesce(Desc,'') as row_text
FROM
table;

.END EXPORT;
.LOGOFF;

Any ideas?
Thanks,
Jorge.
Enthusiast

Re: FastExport to Text

.BEGIN EXPORT
TENACITY 4
SESSIONS 10
SLEEP 6;
.EXPORT OUTFILE 'e:\datafile\agr_farm.out' mode record FORMAT text;
Select COVERAGE_CD,STATE_CD,COUNTY_CD,CROP_CD,INS_PLAN_ID,CROP_YR_ID FROM CAESTG1.AGR_FARM;
.END EXPORT;

If you want the output file in text format you have to mention the format text along with the CAST function to convert the columns in to the character format.

.BEGIN EXPORT
TENACITY 4
SESSIONS 10
SLEEP 6;
.EXPORT OUTFILE 'e:\datafile\agr_farm.out'
mode record
FORMAT text;
Select
cast(COVERAGE_CD as char(1)),
cast(' ' as char(2)), /*tab character from the keyboard */
cast(STATE_CD as char(1)),
cast(' ' as char(2))
cast(COUNTY_CD as char(1)),
cast(' ' as char(2))
cast(CROP_CD as char(1)),
cast(' ' as char(2)),
cast(INS_PLAN_ID as char(1)),
cast(' ' as char(2))
cast(CROP_YR_ID as char(1))

FROM CAESTG1.AGR_FARM;
.END EXPORT;

Hope this will help you to get the solution!

Sushant Paricharak
s_1
Enthusiast

Re: FastExport to Text

Hi experts,

1.fastexport runs any phases ?

2.we can update the target table using another table ?

3.using fastexport we can load the table like staging to wh?

Thanks

ratnam