All Forums Tools
Muzammil126 6 posts Joined 12/15
02 Dec 2015
Record rejected to error table if date feild is enclosed with double quotes

Hi,
 
I am trying to load below two records using TPT( version 15.10.00.00) and able to load succusfully second record but first record is going to error table. The same records gets loaded in TPT version 14.00.00.08 .
File :
"1194","N",,"20150422","","20150422"
"1195","N",,"20150423","","20150423"
 
This is comma delimitted file and 4th,5th,6th places are date field
 
i am already usind attribute
Quaoteddata='Optional"
OpenQuoteMark="""
CloseQuoteMark="""
Nullcolumns='Y'
 
I suscept this problem may be because of version change as same file and same code is able to load the file to table correctely when TPT 14.00.00.08 is used and same has issue when using TPT 15.10.00.00
 
Any input on this woul be of great help
 
Thanks
Muzammil
 

feinholz 1234 posts Joined 05/08
02 Dec 2015

On which platform are you running?
Have you looked at the error table to see which column is being flagged as the error column?
Obviously, it does not make sense (to me) because the data is pretty much identical.
Also, I am hoping that your attributes were hand-written and not copied-and-pasted, because "QuotedData" is misspelled in your post.
It appears in the post as "Quaoteddata". A misspelled attribute name would not enable the feature (just checking).
 

--SteveF

Muzammil126 6 posts Joined 12/15
02 Dec 2015

Thanks Streve for responsding
Platform :TD 15.10
Yes error table show the column which is date
Error table :EXX_ATTR_e1
ErrorCode   ErrorFieldName  
2665           BGLR_DATE
Yes that quotes and all are typo errors
 
Thanks
Muzammil
 
 

Muzammil126 6 posts Joined 12/15
02 Dec 2015

Yes that quotes and all are type errors but i have them correctly in script. Concern here is same data is loaded correctly in 14.10 where as it is rejecting in 15.10
 
Thanks
Muzammil

feinholz 1234 posts Joined 05/08
02 Dec 2015

Can you provide me with the table definition and script you are using?

--SteveF

feinholz 1234 posts Joined 05/08
02 Dec 2015

And at first you said it worked in 14.00.00.08, and then you say it works in 14.10.
Does it work in both 14.00 and 14.10, but not in 15.10?

--SteveF

Muzammil126 6 posts Joined 12/15
03 Dec 2015
++++++++++++++++++++++++++++++++++DDL++++++++++++++++

CREATE MULTISET TABLE DEV.EXX_ATTR ,NO FALLBACK ,
      NO BEFORE JOURNAL,
      NO AFTER JOURNAL,
      CHECKSUM = DEFAULT,
      DEFAULT MERGEBLOCKRATIO
      (
       Col1 INTEGER,
       Col2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
       Col3 VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
       Col4_DATE DATE FORMAT 'YYYY-MM-DD',
       Col5_DATE DATE FORMAT 'YYYY-MM-DD',
       Col6_DATE DATE FORMAT 'YYYY-MM-DD')
 PRIMARY INDEX EXT_STORE_SECURITY_PI ( Col1 );

++++++++++++++++++++++++++++++++script+++++++++++++++++++++

DEFINE JOB load_EXX_ATTR( 
  DEFINE OPERATOR W_1_op_loadEXX_ATTR
  TYPE LOAD
  SCHEMA *
  ATTRIBUTES
  (
   VARCHAR UserName, VARCHAR UserPassword, VARCHAR LogTable, VARCHAR TargetTable,
   INTEGER BufferSize, INTEGER ErrorLimit, INTEGER MaxSessions, INTEGER MinSessions,
   INTEGER TenacityHours, INTEGER TenacitySleep, VARCHAR AccountID, VARCHAR DateForm,
   VARCHAR ErrorTable1, VARCHAR ErrorTable2, VARCHAR WorkTable ,VARCHAR NotifyExit, VARCHAR NotifyExitIsDLL,
   VARCHAR NotifyLevel, VARCHAR NotifyMethod, VARCHAR NotifyString, VARCHAR PauseAcq,
   VARCHAR PrivateLogName, VARCHAR TdpId, VARCHAR TraceLevel, VARCHAR WorkingDatabase,
  VARCHAR QueryBandSessInfo );
  DEFINE SCHEMA W_0_sc_loadEXX_ATTR
  (
       "Col1" VARCHAR(25),
       "Col2" VARCHAR(5),
       "Col3" VARCHAR(250),
       "Col4_DATE" VARCHAR(15),
       "Col5_DATE" VARCHAR(15),
       "Col6_DATE" VARCHAR(15)
  );
  DEFINE OPERATOR W_0_op_loadEXX_ATTR
  TYPE DATACONNECTOR PRODUCER
  SCHEMA W_0_sc_loadEXX_ATTR
  ATTRIBUTES
  (
   VARCHAR FileName, VARCHAR Format, VARCHAR OpenMode, INTEGER BlockSize,
   INTEGER BufferSize, INTEGER RetentionPeriod, INTEGER RowsPerInstance, INTEGER SecondarySpace,
   INTEGER UnitCount, INTEGER VigilElapsedTime, INTEGER VigilWaitTime, INTEGER VolumeCount,
   VARCHAR AccessModuleName, VARCHAR AccessModuleInitStr,  VARCHAR AcceptExcessColumns,
   VARCHAR FileList, VARCHAR ExpirationDate, VARCHAR RowErrFileName,VARCHAR EscapeTextDelimiter,
   VARCHAR IndicatorMode, VARCHAR PrimarySpace, VARCHAR PrivateLogName, VARCHAR RecordFormat,
   VARCHAR RecordLength, VARCHAR SpaceUnit, VARCHAR TextDelimiter, VARCHAR VigilNoticeFileName,
   VARCHAR VigilStartTime, VARCHAR VigilStopTime, VARCHAR VolSerNumber, VARCHAR UnitType,
   VARCHAR NullColumns, VARCHAR QuotedData, VARCHAR OpenQuoteMark, VARCHAR CloseQuoteMark
  );
  APPLY
   (
    'INSERT INTO DWBATCHDEV.EXX_ATTR(
     "Col1",
     "Col2",
     "Col3",
     "Col4_DATE",
     "Col5_DATE",
     "Col6_DATE"
     )
     VALUES
     (
     :Col1 ,
     :Col2 ,
     :Col3 ,
     :Col4_DATE (DATE,FORMAT ''YYYYMMDD''),
     :Col5_DATE (DATE,FORMAT ''YYYYMMDD''),
     :Col6_DATE (DATE,FORMAT ''YYYYMMDD'')
    );'
   )
  TO OPERATOR
  (
   W_1_op_loadEXX_ATTR[1]
   ATTRIBUTES
   (
    UserName = @UserName,
    UserPassword = @PassWord,
    LogTable = 'TMP.EXX_ATTR_lg',
    ErrorTable1 = ' TMP.EXX_ATTR_e1',
    ErrorTable2 = ' TMP.EXX_ATTR_e2',
    WorkTable = ' TMP.EXX_ATTR_wk',
    TargetTable = 'DEV.EXX_ATTR',
    MaxSessions = 10,
    PrivateLogName = 'LOAD',
    TenacityHours = 1,
    TenacitySleep = 1,
    TdpId = @TdServer
   )
  )
  SELECT
     "Col1",
     "Col2",
     "Col3",
     "Col4_DATE",
     "Col5_DATE",
     "Col6_DATE"
  FROM OPERATOR
  (
   W_0_op_loadEXT_LP_LOC_SECURITY_ATTR[4]
   ATTRIBUTES
   (
    FileList = 'Y' ,
    FileName = '/dev/log/tdtst_DEV_EXX_ATTR.lst',
    Format = 'DELIMITED',
    QuotedData = 'Optional',
    OpenQuoteMark = '"',
    CloseQuoteMark = '"',
    OpenMode = 'Read',
    AcceptExcessColumns = 'Y',
    PrivateLogName = 'READ',
    NullColumns = 'Y',
    TextDelimiter = ','
   )
  );
 );


Hi,
It worked on 14.00.00.08 not in 15.10.00.00
 
I have attached script and DDl
 
Thanks
Muzammil

feinholz 1234 posts Joined 05/08
03 Dec 2015

Thank you for all of the information.
 
I ran the same job on my system with TPT 15.10 and find that both rows end up in the error table. And this is correct in that both rows need to be treated the same way. With that data, there should never be a case where one row loads into the target table and the other one ends up in the error table.
 
Of course, that does not explain the problem, but on my system (and you can try the same thing), I exported the data from the DataParcel field in the error table to see what is causing the problem and I see what is happening.
 
If you do this:
1. run BTEQ
2. .export data file=<some file name>
3. sel dataparcel from <tablename>;
4. .export reset
5. exit out of BTEQ
 
Go take a look at the data file. If you are on Unix/Linux and can use 'od -c' you can dump out the contents of the file and look at the characters. And you will see that the quotes were shipped as part of the data. And this is what was causing the DATE fields to be rejected (the DBS only reports the first one it encounters that is bad, but all of the DATE fields are bad).
 
We are looking into why we are keeping the quotes with the data. That should not be happening. They should be stripped out.
 

--SteveF

Muzammil126 6 posts Joined 12/15
03 Dec 2015

Yes Steve , you are right both should go to error table , i think i was testing with different set and i have mention the same , my bad
Do you know why same thing is correctly loaded in TPT 14.00.00.08 but not in TPT 15.10.00.00
 
This is part of our testing where we are upgrating from 14.10 to 15.10 and file format cannot be chnaged because of too much work at our side and also there is no convience answer for changing all the source files
 
Thanks
Muzammil

feinholz 1234 posts Joined 05/08
03 Dec 2015

We are investigating.

--SteveF

feinholz 1234 posts Joined 05/08
03 Dec 2015

Not sure if this was a typo, but in one of your responses you indicated you tried 14.10.
Is this true?
If so, which specific version of 14.10 did you try?
 

--SteveF

Muzammil126 6 posts Joined 12/15
03 Dec 2015

Server details :-

Server where it is not working:-

TD database version
 *** Teradata Database Release is 15.00.04.04
 *** Teradata Database Version is 15.00.04.04

TPT Version:15.10.00.00

Server where it is able to load correctly:-

TD database version
 *** Teradata Database Release is 14.00.07.07
 *** Teradata Database Version is 14.00.07.08

TPT Version:14.00.00.08

================**********************======================

 

feinholz 1234 posts Joined 05/08
03 Dec 2015

I just tried the exact same data you gave me using TPT 14.00.00.08, 14.10.00.13 and 15.10.00.04 and all versions of TPT complete with the same result.
 
All jobs result in both rows being loaded into the error table.
I checked the DataParcel contents and I was wrong before.
The quotes are NOT being sent to Teradata (which is correct).

--SteveF

feinholz 1234 posts Joined 05/08
03 Dec 2015

All of my jobs were run against TD 15.10.
Can you please try running TPT 14.0 against your TD 15.00?
I have a feeling this might be a DBS issue, not a TPT issue.
 

--SteveF

07 Dec 2015

Tried the same thing with an INTEGER field with QuotedData Attribute;

   Format = 'DELIMITED',

   QuotedData = 'Optional',

   OpenQuoteMark = '"',

   CloseQuoteMark = '"',

   OpenMode = 'Read',

   AcceptExcessColumns = 'Y',

   PrivateLogName = 'READ',

   NullColumns = 'Y',

   TextDelimiter = ','

 

When I'm enclosing the INTEGER data with Quotes, the data is getting populated in the target table as ZERO "0"; But when I'm not enclosing the same with quotes, It's populating with NULL data in the target table.

 

After loading the file containing data shown below (Integer,Varchar,Date), its populating 0 in the target table for 2nd row (1st field) 

"1","sanjoy","20150111"

"","xxx","20150112"

 

After loading the file containing data shown below (Integer,Varchar,Date), its populating NULL in the target table for 2nd row (1st field)

1,"sanjoy","20150111"

,"xxx","20150112"

 

 

Regards....Sanjoy

07 Dec 2015

·         In earlier version there was no way to distinguish between BLANK (Empty) data and NULL data
·         I believe in TTU 15.10, they have came up with a way to distinguish between BLANK and NULL.
o   If data is enclosed with Quotes in the incoming file, it should be considered as BLANK (Empty). If there are no Quote, then it should be considered as NULL
·         Tested the below as part of Proof of Concept with TTU 15.10
o   If an INTEGER field is having BLANK data (enclosed with double quote) à In Target table its populating 0
o   If an INTEGER field is having NULL data (Not enclosed with double quote) à In Target table its populating NULL
o   If an DECIMAL field is having BLANK data (enclosed with double quote) à In Target table its populating 0.00
o   If an DECIMAL field is having NULL data (Not enclosed with double quote) à In Target table its populating NULL
o   If an VARCHAR field is having BLANK data (enclosed with double quote) à In Target table its populating BLANK
o   If an VARCHAR field is having NULL data (Not enclosed with double quote) à In Target table its populating NULL
·         Tested the below as part of Proof of Concept with TTU 14.00
o   If an INTEGER field is having BLANK data (enclosed with double quote) à In Target table its populating NULL
o   If an INTEGER field is having NULL data (Not enclosed with double quote) à In Target table its populating NULL
o   If an DECIMAL field is having BLANK data (enclosed with double quote) à In Target table its populating NULL
o   If an DECIMAL field is having NULL data (Not enclosed with double quote) à In Target table its populating NULL
o   If an VARCHAR field is having BLANK data (enclosed with double quote) à In Target table its populating NULL
o   If an VARCHAR field is having NULL data (Not enclosed with double quote) à In Target table its populating NULL

Regards....Sanjoy

feinholz 1234 posts Joined 05/08
07 Dec 2015

Yes, you are correct, that in earlier versions of TPT there was no way to distinguish between BLANK and NULL.
 
However, when QuotedData is enabled, the rules are simple. The existence of 2 consecutive double-quotes will mean an empty string (not NULL). The only way to NULL a column is:
1. if the first character of the record is the field delimiter, the first column is NULL
2. if the last character of the record is the field delimiter, and there is a field in the schema that is defined for the record after that field delimiter, that last field is NULL
3. for any other field in the input record, the existence of 2 consecutive field delimiters will NULL the field.
 
The existence of 2 consecutive double-quotes should never be construed as NULL-ing a field (the above rules are the only way to NULL a field).
 
We had issues with TPT 14.0 following those rules, and have fixed those issues in subsequent releases.
 
Also, please try to remove the NullColumns attribute and see how that changes your outcome.
 

--SteveF

feinholz 1234 posts Joined 05/08
07 Dec 2015

Actually, can you try 2 things?
 
1. Change QuotedData = 'optional'   to  QuotedData = 'yes' (since all of your fields are quoted, it will not cause a problem, and might actually work for you).
 
AFTER you do #1, and send me the result:
 
2. Upgrade to the latest patch of TPT 14.00.00 (at least 14.00.00.13)
 
The developer thinks that what you are experiencing was a bug in how we processed "" when QuotedData was set to 'optional'.
 
Thanks!

--SteveF

08 Dec 2015

Hi Steve,
Change QuotedData = 'yes' can not be done in some cases as because sometimes INTEGER and DECIMAL/NUMBER fields are not enclosed by Quotes but the DATE/VARCHAR fields are enclosed with quotes in CSV file.
However a CASE statement (If Blank then NULL) in the DML section in TPT will solve this.

Regards....Sanjoy

feinholz 1234 posts Joined 05/08
08 Dec 2015

Ok, I understand about the option needing to be "optional".
Since you sent me data that was all quoted, I thought maybe all data would be that way.
Is it possible to upgrade to 14.00.00.13?
We think we fixed an issue regarding how we handle the empty string when the option is "optional".
Did you ever try TPT 14.00.00.08 against TD 15.00?
 

--SteveF

KBK1987 1 post Joined 02/16
10 Feb 2016

 Record rejected to error table if date feild is enclosed with double quotes
While define QUOTE OPTIONAL ' " ' have to give space in ' " ' quotes.To eliminate Record rejected issue.
---KBK 
 
 

You must sign in to leave a comment.