Fastexport ASCII character removal

Tools & Utilities

Fastexport ASCII character removal

Hi,

I am trying to export data from the database using Fastexport. This is the code

.logtable P_EUMKTAN_T.ERROR_LOG;

.logon MOZART/USERNAME,PASSWORD;

.begin export sessions 12;

.export outfile

c:\fast\data\C2C_UK_MOBILE_LIST.txt 

MODE RECORD FORMAT TEXT;

SELECT TOP 1000 TRIM(TRIM((USER_ID (FORMAT 'Z(I)9')))||' '||TRIM(MOBILE_CLASS)||' '||TRIM(TEST_CONTROL)) (VARCHAR(255))

FROM

DB_NAME.TABLE_NAME

;

.end export;

.logoff;

USER_ID is an integer and MOBILE_CLASS and TEST_CONTROL are both varchar

When I download the above file I get a couple of ASCII characters at the beginning of each line - either CAN, ESC, SUB, EN, FS or ETB then NUL

How can I remove these?

Thanks

Mike
Tags (1)
5 REPLIES
Junior Supporter

Re: Fastexport ASCII character removal

Hi:

This has been discussed over and over...

The ASCI chars are the size of the varchar that you defined in the SELECT statement. If you don't want this, you must declare the output as a fixed length CHAR (with trailing blanks, etc...)

HTH.

Cheers.

Carlos.

Teradata Employee

Re: Fastexport ASCII character removal

Actually, those are not ASCII characters. The 2 bytes are the record length.

Declaring the output as CHAR will not change that.

FastExport always exports the data with the 2-byte record length.

You will have to post-process the data in order to remove them (or write to an OUTMOD that removes them and then writes to a file).

-- SteveF
Junior Supporter

Re: Fastexport ASCII character removal

Steven:

bteq:

select * from CARLOS.prueba01 ORDER BY 1;

 *** Query completed. 3 rows found. 2 columns returned.

 *** Total elapsed time was 1 second.

       ID_N  C_TXT

-----------  -------------------------

          1  UNO

          2  DOS

          3  TRES

script for fast export:

.LOGTABLE CARLOS.PRUEBA01_FXP;

.LOGON MY_DB/carlos,xxxxxx;

.BEGIN EXPORT;

.EXPORT OUTFILE C:\Pruebas\FastExport\PRUEBA01.txt

   MODE RECORD

 FORMAT TEXT;

SELECT CAST(CAST(ID_N AS FORMAT '99') AS CHAR(2)) || CAST(C_TXT AS CHAR(10))

  FROM CARLOS.PRUEBA01

;

.END EXPORT;

.LOGOFF;

execution:

C:\Pruebas\FastExport>fexp < PruebaFastExport.fexp;

     ========================================================================

     =                                                                      =

     =          FastExport Utility    Release FEXP.13.10.00.003             =

     =          Platform WIN32                                              =

     =                                                                      =

     ========================================================================

     =                                                                      =

     =     Copyright 1990-2010 Teradata Corporation. ALL RIGHTS RESERVED.   =

     =                                                                      =

     ========================================================================

**** 10:37:05 UTY2411 Processing start date: THU JUN 14, 2012

     ========================================================================

     =                                                                      =

     =          Logon/Connection                                            =

     =                                                                      =

     ========================================================================

...

0002 .LOGON MY_DB/carlos,;

**** 10:37:07 UTY8400 Teradata Database Release: 12.00.02.46

**** 10:37:07 UTY8400 Teradata Database Version: 12.00.02.46

**** 10:37:07 UTY8400 Default character set: ASCII

**** 10:37:07 UTY8400 Current RDBMS has UDT support

**** 10:37:07 UTY8400 Current RDBMS has Large Decimal support

**** 10:37:07 UTY8400 Current RDBMS doesn't have FEXP w/o Spooling support

**** 10:37:07 UTY8400 Maximum supported buffer size: 1M

**** 10:37:07 UTY8400 Data Encryption supported by RDBMS server

**** 10:37:08 UTY6211 A successful connect was made to the RDBMS.

**** 10:37:08 UTY6217 Logtable 'CARLOS.PRUEBA01_FXP' has been created.

     ========================================================================

     =                                                                      =

     =          Processing Control Statements                               =

     =                                                                      =

     ========================================================================

0003 .BEGIN EXPORT;

0004 .EXPORT OUTFILE C:\Pruebas\FastExport\PRUEBA01.txt

        MODE RECORD

      FORMAT TEXT;

0005 SELECT CAST(CAST(ID_N AS FORMAT '99') AS CHAR(2)) || CAST(C_TXT AS CHAR(10))

       FROM CARLOS.PRUEBA01

     ;

0006 .END EXPORT;

     ========================================================================

     =                                                                      =

     =          FastExport Initial Phase                                    =

     =                                                                      =

     ========================================================================

**** 10:37:08 UTY8700 Options in effect for this FastExport task:

     .       Sessions:    4 session(s).

     .                    Minimum of 1 export session(s) requested.

     .       Mode:        RECORD

     .       Blocksize:   64330 bytes.

     .       Outlimit:    No limit in effect.

**** 10:37:08 UTY8715 FastExport is submitting the following request:

     Select NULL from CARLOS.PRUEBA01_FXP where (LogType = 220) and (Seq =

     1) and (FExptSeq = 0);

**** 10:37:09 UTY8705 EXPORT session(s) requested: 4.

**** 10:37:09 UTY8706 EXPORT session(s) connected: 4.

**** 10:37:09 UTY8715 FastExport is submitting the following request:

     BT;BEGIN FASTEXPORT;

**** 10:37:09 UTY8715 FastExport is submitting the following request:

     SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER), MiscInt3 (INTEGER),FExptSeq

     (INTEGER), FExptCkpt (VARBYTE(1024)) from CARLOS.PRUEBA01_FXP WHERE

     (LogType = 210) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq) from

     CARLOS.PRUEBA01_FXP where (LogType = 210) and (Seq = 1)));

**** 10:37:09 UTY8715 FastExport is submitting the following request:

     SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER),  MiscInt3 (INTEGER),FExptSeq

     (INTEGER), FExptCkpt (VARBYTE(1024)) from CARLOS.PRUEBA01_FXP WHERE

     (LogType = 212) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq) from

     CARLOS.PRUEBA01_FXP where (LogType = 212) and (Seq = 1)));

**** 10:37:09 UTY8715 FastExport is submitting the following request:

     SELECT CAST(CAST(ID_N AS FORMAT '99') AS CHAR(2)) || CAST(C_TXT AS

     CHAR(10))

       FROM CARLOS.PRUEBA01

     ;

**** 10:37:09 UTY8724 Select request submitted to the RDBMS.

**** 10:37:09 UTY8725 Select execution completed. 3 data blocks generated.

**** 10:37:09 UTY8715 FastExport is submitting the following request:

     INS CARLOS.PRUEBA01_FXP (LogType, Seq) VALUES (220, 1)

**** 10:37:09 UTY8715 FastExport is submitting the following request:

     END FASTEXPORT;ET;

**** 10:37:10 UTY8710 Processing complete for this FastExport task.

     ========================================================================

     =                                                                      =

     =          FastExport Task Complete                                    =

     =                                                                      =

     ========================================================================

**** 10:37:10 UTY8722 3 total records written to output file.

0007 .LOGOFF;

     ========================================================================

     =                                                                      =

     =          Logoff/Disconnect                                           =

     =                                                                      =

     ========================================================================

**** 10:37:10 UTY6216 The restart log table has been dropped.

**** 10:37:10 UTY6212 A successful disconnect was made from the RDBMS.

**** 10:37:10 UTY2410 Total processor time used = '0.390625 Seconds'

     .       Start : 10:37:04 - THU JUN 14, 2012

     .       End   : 10:37:10 - THU JUN 14, 2012

     .       Highest return code encountered = '0'.

output file:

C:\Pruebas\FastExport>TYPE C:\Pruebas\FastExport\PRUEBA01.txt

01UNO

02DOS

03TRES

C:\Pruebas\FastExport>

There are no 2-byte length leading bytes...

Cheers.

Carlos.

Teradata Employee

Re: Fastexport ASCII character removal

Yep, I missed the "FORMAT TEXT" part.

I am trying to get people to use "TEXT" in the proper way. In fact, I think we have made changes over the years as to how we handle TEXT.

TEXT is supposed to be just that: text (i.e. ASCII). It is not supposed to have any binary components to it.

Therefore, TEXT should not contain any VARCHARs because those require field length bytes.

When using TEXT, all fields should be fixed length CHAR.

I know that is not how it was used in the past, and if you look at the utility documentation, there is nothing to indicate that you cannot use TEXT with binary data but I think we do put out warnings now.

TEXT is a very misused record format.

-- SteveF
Junior Supporter

Re: Fastexport ASCII character removal

Steven:

>>"Therefore, TEXT should not contain any VARCHARs because those require field length bytes."

That was exactly my point.

Cheers.

Carlos.