Fast Export

Tools & Utilities
TS
Enthusiast

Fast Export

While using a fast export script, I am using FORMAT TEXT but the text file created is not in a readable format, what should be the used for formatting the text file?
11 REPLIES

Re: Fast Export

If you want the file to be in readable format then use like this

.BEGIN EXPORT
SESSIONS 20;
EXPORT OUTFILE OUTDD MODE RECORD;
SELECT * from database1.table1;
.END EXPORT;

TS
Enthusiast

Re: Fast Export

The following is the code which is being executed, still the output is not in the correct format.

.LOGTABLE db.datastore_log ;
.logon;
.BEGIN EXPORT
SESSIONS 20
TENACITY 5
SLEEP 1;
.EXPORT OUTFILE c:\OUT.txt MODE RECORD
FORMAT TEXT;
SELECT
cust_id
,acct_nbr
,minimum_balance
,acct_start_date
,starting_balance
FROM financial.checking_acct
;
.END EXPORT;
.LOGOFF;
Enthusiast

Re: Fast Export

Hi,
in order to have a readable output you have to cast as char and concat all the fields you are exporting.

Like this:

SELECT cast((cast(cust_id as char(xx))
||cast(acct_nbr as char(xx))
||cast(minimum_balance as char(xx))
||cast(acct_start_date as char(10))
||cast(starting_balance as char(xx))) as char(yy))
FROM financial.checking_acct
;
where xx is the length of every field in your select, and yy is the total length of the global record you are exporting; that is yy is the sum of cust_id length plus acct_nbr length... and so on.

Pay attention that if you have nullable fields I would suggest using coalesce on those fields.

Hope this helps,
bye,
TDUser

TS
Enthusiast

Re: Fast Export

Thanks a lot!
It worked.
Could u pls tell, is it necessary to cast the fields while doing a fast export?

Regards,
TS.
Enthusiast

Re: Fast Export

I don't really know if casting every field of the select is necessary when exporting a readable output.

When you use a FXP - let me say - in the "standard way" (that is you export a file in fastload format or in another format and then you use this fole as input of a multiload), you don't have to cast the fields.

But if you want the export file to be opened for example with notepad or excel you have to cast them all.

I hope I've understood your question and answered properly...

Bye,
TDUser
TS
Enthusiast

Re: Fast Export

Yes, thnx a lot!
But there's a small query again, on running that script, the output / txt file which is being created is as follows:
H 1362500|0000000013625002| 100|1995-12-04| 882.150
H 1362503|0000000013625032| 200|1994-09-01| 2176.170
H 1362672|0000000013626722| 100|1995-12-18| 177.940
H 1362605|0000000013626052| 200|1994-05-21| 256.110
H 1362498|0000000013624982| 100|1995-02-12| 233.770
H 1362551|0000000013625512| 100|1995-09-18| 352.970
H 1362486|0000000013624862| 3000|1994-08-24| 4252.390
H 1362489|0000000013624892| 200|1993-10-19| 833.400

everything is fine except that "H", don't know from where is it coming????
If u could help on this.

Thanks & Regards,
TS.
Enthusiast

Re: Fast Export

I don't know...
Are you using cast on every field and on the "global concat field"? could you please post your final script?
Bye,
TDUser
TS
Enthusiast

Re: Fast Export

This is the script that is being used:

.LOGTABLE abc.datastore_log ;
.logon ab/ab,;
.BEGIN EXPORT
SESSIONS 20
TENACITY 5
SLEEP 1;
.EXPORT OUTFILE c:\OUT.txt MODE RECORD
FORMAT TEXT;
SELECT CAST(cast(cust_id as INTEGER)
|| '|' || cast(acct_nbr as VARCHAR(26))
|| '|' || cast(minimum_balance as INTEGER)
|| '|' || (acct_start_date (FORMAT 'yyyy-mm-dd'))
|| '|' || cast(starting_balance as DECIMAL(18,3)) AS VARCHAR(1000))
FROM financial.checking_acct
;
.END EXPORT;
.LOGOFF;

This is the output:

H 1362500|0000000013625002| 100|1995-12-04| 882.150
H 1362503|0000000013625032| 200|1994-09-01| 2176.170
H 1362672|0000000013626722| 100|1995-12-18| 177.940
H 1362605|0000000013626052| 200|1994-05-21| 256.110
H 1362498|0000000013624982| 100|1995-02-12| 233.770
H 1362551|0000000013625512| 100|1995-09-18| 352.970
H 1362486|0000000013624862| 3000|1994-08-24| 4252.390
H 1362489|0000000013624892| 200|1993-10-19| 833.400
Enthusiast

Re: Fast Export

I think the cast should be:

SELECT CAST(cast(cast(cust_id as INTEGER format ) as char(the length fo the integer format))
|| '|' || cast(acct_nbr as CHAR(26))
|| '|' || cast(minimum_balance as INTEGER) <-- same as field cust_id
|| '|' || cast(cast(acct_start_date as date FORMAT 'yyyy-mm-dd') as char(10))
|| '|' || cast(cast(starting_balance as DECIMAL(18,3)) as char(18)) AS CHAR(the sum of the lengths of the various cast functions))
FROM financial.checking_acct
;

Teradata automatically casts when you concat fields, but in this case I wouldn't suggest to.

Hope this helps!
Bye!
TDUser