Junk character in fast export

General
Tourist

Junk character in fast export

 

Hi,

the fast exproting generaring file with junk character $ ( i dont watn junk character $)

fexp <<END > /dev/null
.LOGTABLE rmcust_log;
.LOGON TDDEV/e_service_batch_DV4,Edwtd_dev_dv4;
DATABASE STGDB_DV4;
.BEGIN EXPORT SESSIONS 2;
.EXPORT OUTFILE $FILE_PATH/pmdata1.txt
MODE RECORD FORMAT TEXT;
SELECT TRIM(SUBSTRING('0000000000' FROM 1 FOR 10 - CHARACTERS(ST.TCH_ID)))||TRIM(ST.TCH_ID)||
TRIM(SUBSTRING('0000000000' FROM 1 FOR 5 - CHARACTERS(CUST.ACCT_NUM)))||TRIM(CUST.ACCT_NUM)||TRIM(CUST.REG_CD)||TRIM(CUST.REG_CD)||
TRIM((CASE WHEN PARAM.MTN_ACCT_IND='A' THEN '7777777777' ELSE CUST.MTN END))||TRIM(PARAM.STU_ID)
FROM STGDB_DV4.WI_RM_PARAM PARAM
INNER JOIN STGDB_DV4.WI_RM_CUSTOMER ST
ON PARAM.STU_ID = ST.STU_ID
AND PARAM.END_DATE > DATE;
.END EXPORT;
.LOGOFF;
END

outfile generarting with junk character $ and not able to deleting $ with sed command in unix, i am not sure why i am not able to delete.
how to avoid junk character while exporting file.
$098765436700002MSMS77777777771234
$001765436700003NYNY77777777771234
$000035436700001CACA77777777771234

Created same file manually and deleted $ with sed command.

 

 

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

cut command not working on exported file i am not sure.

cat $FILE_PATH/pmdata1.txt | cut -f3- -d ',' > $FILE_PATH/pmdata.txt

,,0,123456789,00001,CR,CR,12345

script:

fexp <<END > /dev/null
.LOGTABLE rmcust_log;
.LOGON TDDEV/e_service_batch_DV4,Edwtd_dev_dv4;
DATABASE STGDB_DV4;
.BEGIN EXPORT SESSIONS 2;
.EXPORT OUTFILE $FILE_PATH/pmdata1.txt
MODE RECORD FORMAT TEXT;
SELECT  ','||TRIM(SUBSTRING('0000000000' FROM 1 FOR 10 - CHARACTERS(ST.TCH_ID)))||','||TRIM(ST.TCH_ID)||','||
TRIM(SUBSTRING('0000000000' FROM 1 FOR 5 - CHARACTERS(CUST.ACCT_NUM)))||','||TRIM(CUST.ACCT_NUM)||','||TRIM(CUST.REG_CD)||','||TRIM(CUST.REG_CD)||','||
TRIM((CASE WHEN PARAM.MTN_ACCT_IND='A' THEN '7777777777' ELSE CUST.MTN END))||','||TRIM(PARAM.STU_ID)
FROM STGDB_DV4.WI_RM_PARAM PARAM
INNER JOIN STGDB_DV4.WI_RM_CUSTOMER ST
ON PARAM.STU_ID = ST.STU_ID
AND PARAM.END_DATE > DATE;
.END EXPORT;
.LOGOFF;
END

 

2 REPLIES 2
Teradata Employee

Re: Junk character in fast export

Hi.

 

The first "junk character" is nothing but the length of the VARCHAR string that you are creating with the functions (TRIM, SUBSTRING...)

 

You can CAST the final string to CHAR(n) to remove the first byte that indicates the VARCHAR length.

 

HTH.

 

Cheers.

 

Carlos.

Highlighted
Ambassador

Re: Junk character in fast export

As @CarlosAlvarez wrote, the $ is the length of the resulting VarChar (and CASTing to a CHAR(36) will fix it).

But actually it's a word (hex '0024') and that's probably tthe reason why you couldn't remove it with sed, you didn't specify it correctly.

 

Instead of using legacy FastExport you better switch to TPT Export which allows exporting readable text without additional steps.

 

And adding leading zeroes can be simplified using LPAD (or a FORMAT for numeric values):

 

LPAD(ST.TCH_ID,10,'0')