Fast Export producing unreadable text file

Tools

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?
Tags (1)
12 REPLIES
Enthusiast

Re: Fast Export producing unreadable text file

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.

Re: Fast Export producing unreadable text file

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"

Junior Supporter

Re: Fast Export producing unreadable text file

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.

Re: Fast Export producing unreadable text file

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.
Junior Supporter

Re: Fast Export producing unreadable text file

"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.

Re: Fast Export producing unreadable text file

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.
Junior Supporter

Re: Fast Export producing unreadable text file

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.

Re: Fast Export producing unreadable text file

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>;

Fan

Re: Fast Export producing unreadable text file

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