Problem to import a delimited text file in teradata table

Tools & Utilities

Problem to import a delimited text file in teradata table

Good afternoon, everyone.

I'm completely new in the world of Teradata.

I have a problem and i would need help from people with much more experience than me.

The problem: When i import (from MULTILOAD) a text file  with a delimited symbol '|' to a teradata auxiliary table (created by me) i have a problem is that the insertion of the data.

Can you help me? I swear on my soul that I was investigating for two weeks and did not find a solution because all the examples I found do not work. I think the problem may be the input file. 

Can any one HELP ME?

Thank you very much to all.

The delimited text file

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

IBS|ByC|5|FECHA|20070101|-1|-1|        6684,5061|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070102|-1|-1|       -282593,15|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070103|-1|-1|       2756278,31|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070104|-1|-1|       -1010329,2|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070105|-1|-1|       115768,226|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070106|-1|-1|       1936545,95|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070107|-1|-1|       2687359,46|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070108|-1|-1|       1453068,16|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070109|-1|-1|       -2690372,1|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070110|-1|-1|       -2085283,5|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070111|-1|-1|       267984,834|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070112|-1|-1|       289931,665|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070113|-1|-1|       1290998,73|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070114|-1|-1|       1055463,79|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070115|-1|-1|       -103615,62|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070116|-1|-1|       -2233793,3|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070117|-1|-1|       -276442,39|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070118|-1|-1|       -382398,29|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070119|-1|-1|        -26900,09|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070120|-1|-1|       1003009,66|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070121|-1|-1|       1179042,28|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070122|-1|-1|       117305,433|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070123|-1|-1|       -1576164,8|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070124|-1|-1|       71149,2559|SUMA_AMT_INCL_TAX

IBS|ByC|5|FECHA|20070125|-1|-1|       138511,473|SUMA_AMT_INCL_TAX


My aux Table

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

my table has 9 columns and all the columns are defined like a char

the .ml file

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

 .LOGTABLE    DTV_HMG_CTL.VALIDACION_MLOG;

    .logon 172.22.138.40/CONS_HMG_USER,CONS_HMG_USER;

     DATABASE DTV_HMG_CTL;

    DROP TABLE  DTV_HMG_CTL.VALIDACION_wk;

    DROP TABLE  DTV_HMG_CTL.VALIDACION_et;

    DROP TABLE  DTV_HMG_CTL.VALIDACION_uv;

    .BEGIN IMPORT MLOAD AMPCHECK NONE TABLES DTV_HMG_CTL.VALIDACION_TERADATA9 

    WORKTABLES  DTV_HMG_CTL.VALIDACION_wk

    ERRORTABLES DTV_HMG_CTL.VALIDACION_et

                DTV_HMG_CTL.VALIDACION_uv

     SLEEP 4    

     TENACITY 100    

     SESSIONS 4     ;

    .layout LAYOUT1;

                .FIELD AMBIENTE * VARCHAR(3);

                .FIELD MODELO * VARCHAR(30);

                .FIELD COM_ID * VARCHAR(1);

                .FIELD APERTURA_1 * VARCHAR(30);

                .FIELD APERTURA_VALOR1 * VARCHAR(30);

                .FIELD APERTURA_2 * VARCHAR(30);

                .FIELD APERTURA_VALOR2 * VARCHAR(30);

                .FIELD CANTIDAD * VARCHAR(30);

                .FIELD OBJETO_VALIDADOR * VARCHAR(30);

                .FIELD NewLine * VARCHAR(1);

     .DML LABEL LABELA;

    INSERT INTO DTV_HMG_CTL.VALIDACION_TERADATA9 

    (

    AMBIENTE

    , MODELO

    , COM_ID

    , APERTURA_1

    , APERTURA_VALOR1

    , APERTURA_2

    , APERTURA_VALOR2

    , CANTIDAD

    , OBJETO_VALIDADOR

    )

VALUES

(

 AMBIENTE = :AMBIENTE 

,MODELO = :MODELO 

,COM_ID = :COM_ID

,APERTURA_1 = :APERTURA_1 

,APERTURA_VALOR1 = :APERTURA_VALOR1 

,APERTURA_2 = :APERTURA_2 

,APERTURA_VALOR2 = :APERTURA_VALOR2 

,CANTIDAD = :CANTIDAD 

,OBJETO_VALIDADOR = :OBJETO_VALIDADOR

);

.import infile C:\Teradata\Pirri\IBS_Ventas_Colombia.TXT  

format vartext '|'

             layout LAYOUT1

             apply LABELA;   

    .END MLOAD;

    .LOGOFF;

EOF

# END of MultiLoad

when i import here is the error:

0021 EOF

     # END of MultiLoad

**** 10:30:31 UTY3410 A semi-colon was not found to terminate the current

     statement.

1 REPLY
Junior Contributor

Re: Problem to import a delimited text file in teradata table

There should be no 

EOF

# END of MultiLoad

and you don't have to define the new line

                .FIELD NewLine * VARCHAR(1);

this is part of the VARTEXT definition, 1 or 2 bytes depending on UNIX/Windows.

And you should check if there's a linebreak after the last row in your input file

DIeter