All Forums Tools
TS 12 posts Joined 02/06
28 Feb 2006
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?

pavan sriram 4 posts Joined 02/06
28 Feb 2006

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 12 posts Joined 02/06
28 Feb 2006

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 RECORDFORMAT TEXT;SELECT cust_id ,acct_nbr ,minimum_balance ,acct_start_date ,starting_balanceFROM financial.checking_acct;.END EXPORT;.LOGOFF;

TDUser-781 35 posts Joined 12/04
28 Feb 2006

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 12 posts Joined 02/06
28 Feb 2006

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

TDUser-781 35 posts Joined 12/04
28 Feb 2006

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 12 posts Joined 02/06
28 Feb 2006

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.400everything is fine except that "H", don't know from where is it coming????If u could help on this.Thanks & Regards,TS.

TDUser-781 35 posts Joined 12/04
28 Feb 2006

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 12 posts Joined 02/06
03 Mar 2006

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 RECORDFORMAT 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.150H 1362503|0000000013625032| 200|1994-09-01| 2176.170H 1362672|0000000013626722| 100|1995-12-18| 177.940H 1362605|0000000013626052| 200|1994-05-21| 256.110H 1362498|0000000013624982| 100|1995-02-12| 233.770H 1362551|0000000013625512| 100|1995-09-18| 352.970H 1362486|0000000013624862| 3000|1994-08-24| 4252.390H 1362489|0000000013624892| 200|1993-10-19| 833.400

TDUser-781 35 posts Joined 12/04
03 Mar 2006

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

TS 12 posts Joined 02/06
06 Mar 2006

Thanks a lot!It worked.Regards,TS.

09 Mar 2006

This is very strange i am not sure why we get that extra char after casting each field in the select statement.The last post wich has cast( cast(......) as (sum of all the filed lengths) should work and an another way to get this done is cast the pipe too.selectcast(emp_no as char(10)) || cast('|' as char(1)),cast(fname as char(20)) || cast('|' as char(1)),cast(lname as char(20)) || cast('|' as char(1)),cast(mgr_emp_no as char(10)) || cast('|' as char(1)),cast(DOB as char(10)) || cast('|' as char(1)),cast(start_date as char(10)) || cast('|' as char(1)),cast(dept_no as char(3)) || cast('|' as char(1)) FROM tablename;This worked for me.But if you are sure that you want to use this file as an input for fastload or multiload dont do it this way just use the "format fastload" and the "mode indicators"

You must sign in to leave a comment.