All Forums Tools
cbsmith 4 posts Joined 07/10
14 Jul 2010
Fast Export producing unreadable text file

I am using Fast export to copy a Teradata table to a flat file on a windows server.

.logtable database.logtable_tablename;
.logon terdata_box/user_id,password;
.begin export sessions 2;
.export outfile myoutputfile.txt RECORD MODE FORMAT TEXT;
select *
from databse.table
where date_column ge cast ('01/01/2010' as date format 'dd/mm/yyyy')
and key_column eq 999999999999
;
.end export;
.logoff;

The query runs fine in sql assistant producing a readable text file. When I run fexp.exe the output file has a lot of unreadable rubbish in it.

9999999999 ¦ ©É N REDsòÌ ón€ sòÌ €ƒL ón€ % > ú Û e( €ƒL ón€ sòÌ ‘‰ ‘‰ U¥ ê ê ƒ âc Branch Halifax Y ™ F02F02 MIXMIX RàñÀÈƒŒ@  k@F02 )¤ F02F02 P³ÒR’&aÀr§BEA@лÖöÆ@ ´

Any idea what I am missing here?

teradata_techie 13 posts Joined 03/10
14 Jul 2010

Hi

In FORMAT TEXT, it will produce an arbitrary number of bytes followed by an end-of-record marker, either
X ‘0A’ or X ‘0D0A’ for Windows systems.

So can you try with VARTEXT or UNFORMAT.

cbsmith 4 posts Joined 07/10
14 Jul 2010

Tried different combinations but received error messages every time;

0004 .export outfile my_file.txt mlscript mload.txt MODE RECORD FORMAT varTEXT;
**** 11:00:24 UTY1600 Internal error in 'EXPORTSIM', unexpected keyword value = '97'.

0004 .export outfile my_file.txt mlscript mload.txt MODE RECORD UNFORMAT TEXT;
**** 11:01:51 UTY00006 Input error in the EXPORT command at position 75: "UNFORMAT"

0004 .export outfile my_file.txt mlscript mload.txt MODE RECORD UNFORMAT VARTEXT;
**** 11:06:21 UTY00006 Input error in the EXPORT command at position 75: "UNFORMAT"

CarlosAL 512 posts Joined 04/08
14 Jul 2010

You must cast the entire resultset as a unique column with type CHAR(N) with MODE RECORD FORMAT TEXT:

...
.EXPORT OUTFILE blah/blah/blah/file MODE RECORD FORMAT TEXT ;

SELECT CAST(
CAST(COL1_N as char(2)) || '|' ||
COL2_C || '|' ||
...
COLN_C
AS CHAR(50))
FROM YOUR_DB.YOUR_TABLE;
...

HTH

Cheers.

Carlos.

cbsmith 4 posts Joined 07/10
14 Jul 2010

I was hoping for something simpler. The table has hundreds of rows. Not only do I have to cast each column individually depending upon it's type and desired format, but I also have to include coalesce functions as at least one null value renders the entire concatenation null.

Is there anything simpler? SQL Assistant can export a Teradata table with a simple query though slowly. I was hoping Fast Export would do the same but faster.

CarlosAL 512 posts Joined 04/08
14 Jul 2010

"I was hoping for something simpler."

Simpler than what?

Your question was "Any idea what I am missing here?"

Here you have the answer. You don't like it? Then go ahead with a SELECT * FROM... in your SQLAssistant and don't write proper SQL...

BTW: Are you paid by the keystroke?

Cheers.

Carlos.

cbsmith 4 posts Joined 07/10
14 Jul 2010

I should have said the table has hundreds of columns rather than rows. And there will be many more tables (with millions of rows) like this.

I would need to write a SAS program to generate Fast Export scripts including bespoke cast clauses and coalesce functions for every column as hand writing them would be time consuming and error prone.

I thought it would be prudent to check if I was missing something like a particuliar option on the export statement or something else before I launched into writing/testing/applying yet another code generation script. I get paid for the keystrokes I don't make.

Thanks for looking at my problem but it sounds like Fast Export can't produce a simple readable flat file without coding a cast and coalesce clause for every column in the tables I need to export.

CarlosAL 512 posts Joined 04/08
15 Jul 2010

It's clear as a day: you are not willing to use an effective export tool (multiple sessions, support environment, restart, parallel, INMOD/OUTMOD...) because you get paid for the keystrokes you don't make.

On the other hand: hundreds of columns? What an interesting design you have over there!

And tables with millions of rows! Surely the regulars here barely have seen tables like these! Data Warehousing... what does it mean anyway?

Cheers.

Carlos.

ChaniTata 1 post Joined 11/12
02 Nov 2012

Hey this is just FYI..! Dont have to be solution you are looking for..
The following worked for me.
 
 

export

        file=/blah/blah/blah/export_tbl.data

        ColumnDelimiter = |

Endexport

 

select colA, colB, colC  ....

...

colX, colY, colZ

from <schema>.<table>;

karim 2 posts Joined 11/12
06 Nov 2012

Do you think fast export will support columndelimiter felid, i have tried but it showing error
 

feinholz 1234 posts Joined 05/08
07 Nov 2012

We will not be making any changes to FastExport for delimited data output.
If you would like this functionality, we offer it in TPT.
 

--SteveF

karim 2 posts Joined 11/12
08 Nov 2012

Hi can any one tell me what is feild size will consider into output file  when fast export is exporting the file
 

feinholz 1234 posts Joined 05/08
08 Nov 2012

I am not quite sure I understand the question.

--SteveF

You must sign in to leave a comment.