All Forums Tools
ubya308 4 posts Joined 05/11
13 Jun 2012
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

 

CarlosAL 512 posts Joined 04/08
13 Jun 2012

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.

feinholz 1234 posts Joined 05/08
13 Jun 2012

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

CarlosAL 512 posts Joined 04/08
14 Jun 2012

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.

feinholz 1234 posts Joined 05/08
14 Jun 2012

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

CarlosAL 512 posts Joined 04/08
15 Jun 2012

Steven:

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

That was exactly my point.

Cheers.

Carlos.

You must sign in to leave a comment.