Issue while loading space delimited flat file using fastload on TPT

Tools & Utilities
Enthusiast

Issue while loading space delimited flat file using fastload on TPT

I am trying to loada flat file that is delimited with space having 8 columns.  While loading this data using TPT, I am getting erorr "Delimited Data Parsing error: Column length overflow(s) in row 1". Even when i try to convert the space delimiter to pipe, i am getting number of columns mismatch in flat file.

Any sugesstions as to why flat file with SPACE delimiter is not working with TPT. Also there are missing columns in this flat file but we have used ACCEPTMISSINGCOLUMNS option. Is this correct.

Sample data (which has multiple spaces between columns) is given below along with the log and the script that is run.

Data:

====

d1      d2              d3              d4      d5      d6      d7      d8

1001    09/01/1995      09/03/1997      2       112     14233   1001    0    

1001    05/02/2008      12/02/2008      2       447     14189   9001    Odkp27uEjCEaByuZLQgXw6kbb88bmPwUfGAEnaH0mg0=  

1001    09/01/1995      09/03/1997      2       112     84      1001    kVbMu8z5RMJprze4ob1AX/IU6X3lDT6oIMbPgJyPbt0=  

1001    18/07/2003      18/07/2003      2       325     35      9001    0  

1001    05/02/2008      12/02/2008      2       447     14172   9001    0  

1001    05/02/2008      12/02/2008      2       447     8687    9001    0  

1001    05/02/2008      12/02/2008      2       447     14173   9001    0  

1001    03/06/2010      03/06/2010      2       551     15987   219001  MJTjTPGHOlOGg1LgSqUMXrB/4yfz4EGHeUfsrzGj780=  

1001    20/07/2010      20/07/2010      2       561     2632    0       27YEsg3G78SuGTcI4zcPCiIHK8wNsWcTrtU6glepyCA=  

1001    18/12/2001      19/12/2001      2       271     13607   9001    0   

1001    18/12/2001      19/12/2001      2       271     7578    9001    0   

1001    18/12/2001      19/12/2001      2       271     77      9001    0   

1001    16/05/2001      17/05/2001      2       253     63      9001    Lc8ybk+g5Iu9iF9eyCF0hL+9E8AlC3wTTOvRPzOQYf4=   

1001    16/10/2003      13/11/2003      2       349     13745   9001    1lKpwbahAH9QZW7hSqCaBqrIhSGoKF4TDYCWwNf/ZbE=   

1001    16/10/2003      28/10/2003      2       346     7926    9001    OXPTHvQ39elodq8CjRdpj5iqH40bhSLscBZjGicPIPU=   

1001    11/02/2008      11/02/2008      2       444     7326    0       2R/fiQqH9AlFd2tpGWii+peuoh9x2DywdGn+wG1QpVc=   

1001    03/11/1998      03/12/1998      2       173     1004    9001    0    

1001    26/05/2010      26/05/2010      2       548     7326    0       MJTjTPGHOlOGg1LgSqUMXr0rffpBEfqVFyScrFH5Gzc=  

1001    20/01/2010      21/01/2010      2       523     10289   219001  0   

1001    16/10/2003      20/10/2003      2       339     8491    9001    0   

1001    16/10/2003      23/10/2003      2       344     8491    9001    0NaMPHiuGhfoZy7cv1QNxqHxIsPjhQRUny2OZ69CMqA=

1001    16/05/2001      17/05/2001      2       252     66      9001    0   

1001    16/05/2001      17/05/2001      2       252     23      9001    0   

1001    18/12/2001      19/12/2001      2       271     13721   9001    0   

1001    16/05/2001      17/05/2001      2       252     24      9001    0   

1001    16/05/2001      17/05/2001      2       252     32      9001    0   

1001    20/01/2010      21/01/2010      2       522     64      219001  lfYay0a9dVu2wcF1OHpuBguP5n57iCep6GxIliM1DV0=   

1001    29/05/2009      29/05/2009      2       485     14563   219001  0   

TPT Log:

==========

Teradata Parallel Transporter Version 14.00.00.10

Job log: /opt/teradata/client/14.00/tbuild/logs/plk1-40.out

Job id is plk1-40, running on us111

Teradata Parallel Transporter DataConnector_C2: TPT19006 Version 14.00.00.10

DataConnector_C2 Instance 1 directing private log report to 'STG_DB.TPT_TEST'.

DataConnector_C2: TPT19008 DataConnector Producer operator Instances: 1

Teradata Parallel Transporter SQL Inserter Operator Version 14.00.00.10

Insert_TPT_TEST2: private log specified: STG_DB.TEST_Space

DataConnector_C2: TPT19003 ECI operator ID: DataConnector_C2-382

Insert_TPT_TEST2: connecting sessions

DataConnector_C2: TPT19222 Operator instance 1 processing file '/COPY/RAW_DATA/test_10000.txtab'.

Insert_TPT_TEST2: Total Rows Sent To RDBMS:      0

Insert_TPT_TEST2: Total Rows Applied:            0

Insert_TPT_TEST2: disconnecting sessions

Insert_TPT_TEST2: Total processor time used = '0.16 Second(s)'

Insert_TPT_TEST2: Start : Wed Mar 19 12:04:37 2014

Insert_TPT_TEST2: End   : Wed Mar 19 12:04:41 2014

Job step Load_TPT_TEST2 terminated (status 12)

Job plk1 terminated (status 12)

DataConnector_C2: TPT19350 I/O error on file '/COPY/RAW_DATA/test_10000.txtab'.

DataConnector_C2: TPT19003 Delimited Data Parsing error: Column length overflow(s) in row 1

DataConnector_C2: TPT19003 TPT Exit code set to 12.

DataConnector_C2: TPT19221 Total files processed: 0.

TPT Script:

===========

DEFINE JOB Load_TPT_TEST2

  DESCRIPTION 'Load a Teradata table from a space delimited flat file' (

      DEFINE SCHEMA Schema_TAB2 (

        D1 VARCHAR(20),

        D2 VARCHAR(10),

        D3 VARCHAR(10),

        D4 VARCHAR(3),

        D5 VARCHAR(20),

        D6 VARCHAR(20),

        D7 VARCHAR(20),

        D8 VARCHAR(50)        

);

      DEFINE OPERATOR DataConnector_C2

      TYPE DATACONNECTOR PRODUCER

      SCHEMA Schema_C2

      ATTRIBUTES (

          VARCHAR PrivateLogName     = 'STG_DB.TPT_TEST',

          VARCHAR FileName       = '/COPY/RAW_DATA/test_10000.txtab',

          VARCHAR TraceLevel       = 'All',

          VARCHAR FORMAT         = 'Delimited',

          VARCHAR TextDelimiter      = 'space',

          VARCHAR OpenMode        = 'read',

          VARCHAR AcceptMissingColumns = 'Y'

      );

      DEFINE OPERATOR Insert_TPT_TEST2

      TYPE INSERTER

      SCHEMA *

      ATTRIBUTES (

          VARCHAR PrivateLogName   = 'STG_DB.TEST_Space',

          VARCHAR TdpId           = 'xx.xxx.xxx.xx',

          VARCHAR UserName        = 'USER1',

          VARCHAR UserPassword    = 'USER1',

          VARCHAR TargetTable      = 'STG_DB.TPT_TEST',

          VARCHAR LogTable        = 'STG_DB.TPT_TEST_L',

          VARCHAR ErrorTable1       = 'STG_DB.TPT_TEST_E1',

          VARCHAR ErrorTable2       = 'STG_DB.TPT_TEST_E2',

          VARCHAR WorkTable        = 'STG_DB.TPT_TEST_WT'

      );

      STEP Load_TPT_TEST2 (

          APPLY (

              'INSERT INTO STG_DB.TEST_Space (

                D1,

                D2,

                D3,

                D4,

                D5,

                D6,

                D7,

                D8

              )

              VALUES (

                     :D1,

                     :D2,

                     :D3,

                     :D4,

                     :D5,

                     :D6,

                     :D7,

                     :D8

                     );'

          )

          TO OPERATOR (

              Insert_TPT_TEST2[1]

          )

        SELECT

         D1,

                D2,

                D3,

                D4,

                D5,

                D6,

                D7,

                D8

          FROM OPERATOR (

              DataConnector_C2[1]

          );

      );

   );

7 REPLIES
Enthusiast

Re: Issue while loading space delimited flat file using fastload on TPT

Can you skip the first row and try. i believe the header is also being read

Teradata Employee

Re: Issue while loading space delimited flat file using fastload on TPT

The TextDelimiter you have defined will look for a delimiter made up of the characters (the word) "space". You need to actually tell us the characters, and they must all be the same for every column separator on every row.

In other words, your delimiter cannot be 4 spaces one time and 3 spaces another.

Thus, if you want the delimiter to be 4 space characters, you need to do:

VARCHAR TextDelimiter = '    '    <----- 4 spaces characters within the quotes

-- SteveF
Enthusiast

Re: Issue while loading space delimited flat file using fastload on TPT

In the flat file, there is no header.

Though the delimiter is define to be '    ', the number of spaces between the columns was not even in the flat file. As stated this could be the issue. So we modified the flat file to ensure that we have only one single space as delimiter between columns. With this change too, we are getting the same error. Is there any alternate solution for this issue.

Enthusiast

Re: Issue while loading space delimited flat file using fastload on TPT

As feinholz said above, please try changing

VARCHAR TextDelimiter = 'space',

to

VARCHAR TextDelimiter = ' ',

in the script and try.

Enthusiast

Re: Issue while loading space delimited flat file using fastload on TPT

Tried with TextDelimiter =' ' (Bioth single and multiple spaces) but got the same error.

Enthusiast

Re: Issue while loading space delimited flat file using fastload on TPT

Is the original file a tab delimited? Replace the space in the file with a comma and try. Also modify the VARCHAR textdelimiter=',' and try.

Teradata Employee

Re: Issue while loading space delimited flat file using fastload on TPT

Please provide a sample of the data after making the modifications to it so that only a single space separates each field.

-- SteveF