Teradata adding Â in front of special characters

Question

I am having an issue where when fast loading data into Teradata Table is causing Â to appear in front of special characters when loading from .csv file.

CSV File

1|Hello, £5.00 has been debited from your account, thank you for your payment.|XXXX|XX|XXXX-XXX-XXX

Teradata Table

1|Hello, Â£5.00 has been debited from your account, thank you for your payment.|XXXX|XX|XXXX-XXX-XXX

Table Definition

CREATE MULTISET TABLE DATABASE1.TABLE1 ,NO FALLBACK ,
 NO BEFORE JOURNAL,
 NO AFTER JOURNAL,
 CHECKSUM = DEFAULT,
 DEFAULT MERGEBLOCKRATIO
 (
  FIELD1 VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
  FIELD2 VARCHAR(750) CHARACTER SET LATIN NOT CASESPECIFIC,
  FIELD3 VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
  FIELD4 VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
  FIELD5 VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC
 ) PRIMARY INDEX ( FIELD1 );

Fastload Code

fastload <<-EOF
    .LOGON username/pass;
    DATABASE DATABASE1;

        SET RECORD VARTEXT "|";

        BEGIN LOADING TABLE1
            ERRORFILES TABLE1_ERR1,
                       TABLE1_ERR2;

        DEFINE
            FIELD1      (VARCHAR(25))
            FIELD2      (VARCHAR(750))
            FIELD3      (VARCHAR(35))
            FIELD4      (VARCHAR(35))
            FIELD5      (VARCHAR(50))


        FILE=${LOAD_FILE};

        SHOW;

        INSERT INTO DATABASE1.TABLE1
        (
            FIELD1 ,
            FIELD2 ,
            FIELD3 ,
            FIELD4 ,
            FIELD5 

        )
        VALUES
        (
            :FIELD1 ,
            :FIELD2 ,
            :FIELD3 ,
            :FIELD4 ,
            :FIELD5 
        );

    .END LOADING;
    .LOGOFF;
    .QUIT;
EOF

Does anyone know how to solve this, I am running this from Solaris 10 Fastload Utility v12.00.00.011

Your probably using a wrong character set for the load. Btw, 12 is a veeeery old release. — dnoeth, Apr 06 '17 at 09:59
Try converting character set of the CSV file? See this link for conversion options http://stackoverflow.com/questions/64860/best-way-to-convert-text-files-between-character-sets — xenodevil, Apr 10 '17 at 10:47

score 0 · Answer 1 · answered Apr 20 '17 at 07:19

The Default character set for fastload is ANSII and £ is UTF8, solution is to change session charset in fastload function.

fastload <<-EOF
    SET SESSION CHARSET 'UTF8';
    .LOGON username/pass;
    DATABASE DATABASE1;

    SET RECORD VARTEXT "|";

    BEGIN LOADING TABLE1
        ERRORFILES TABLE1_ERR1,
                   TABLE1_ERR2;

    DEFINE
        FIELD1      (VARCHAR(25))
        FIELD2      (VARCHAR(750))
        FIELD3      (VARCHAR(35))
        FIELD4      (VARCHAR(35))
        FIELD5      (VARCHAR(50))


    FILE=${LOAD_FILE};

    SHOW;

    INSERT INTO DATABASE1.TABLE1
    (
        FIELD1 ,
        FIELD2 ,
        FIELD3 ,
        FIELD4 ,
        FIELD5 

    )
    VALUES
    (
        :FIELD1 ,
        :FIELD2 ,
        :FIELD3 ,
        :FIELD4 ,
        :FIELD5 
    );

.END LOADING;
.LOGOFF;
.QUIT;
EOF

Teradata adding Â in front of special characters

1 Answers1