TPT - delemiter character in last column

Tools & Utilities
Fan

TPT - delemiter character in last column

Hi everyone,

 

I'm currently trying to import data in a Teradata table using TPT.

I have a flat file which contains 7 columns with | (pipe) as a delemiter character and schema is defined like that:

     DEFINE SCHEMA W_0_s_JOB_ACTIONS_CAMPAGNES
     (
         COL1 VARCHAR(255),
         COL2 VARCHAR(255),
         COL3 VARCHAR(255),
         COL4 VARCHAR(255),
         COL5 VARCHAR(255),
         COL6 VARCHAR(255),
         COL7 VARCHAR(1000)
     );

Here are the attributes about the file:

          ATTRIBUTES
          (
              FileName = @FileName,
              Format = 'DELIMITED',
              OpenMode = 'Read',
              DirectoryPath = @DirectoryPath,
              IndicatorMode = 'N',
              TextDelimiter = '|'
          )

 

And this is a line causing problem:

 

142120265|45694523|Trigger_J_Enews|2017/04/26 13:27:56|0|1|https://aaa.bbbb.ccc.com/nl/jsp/m.jsp?c=<%=escapeUrl(cryptString(message.deliveryPartId.toString(16)+'|'+message.id.toString(16)))%>

 

As you can see, the last column (7th) contains URLs, and some of them contain some code (java i think). In this code the character " | " appears which cause the TPT to detect it as an additional column.

My question is: As the pipe may appear on the last column and always in the same format (+'|'+) , is there a possibility for me to tell TPT to ignore this character when it is between single quotes and insert the whole URL a single string value ?

 

I'm not very experienced in TPT and i've been trying lot of options I found on this forum. That's why I post this topic in order to determine if there is a way to do this or if it is impossible.

 

Thanks!

 

 

 

 

Tags (2)

Accepted Solutions
Teradata Employee

Re: TPT - delemiter character in last column

Set OpenQuoteMark to the double-quote character.

Set CloseQuoteMark to the double-quote character.

Make sure your quotes fields are enclosed by double-quotes.

If those are set up, you can use any other character for the EscapeTextDelimiter.

-- SteveF
1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: TPT - delemiter character in last column

There are 2 ways to allow a field delimiter to appear as data (in any field, it does not have to be the last one):

 

Either quote the field, or escape the field delimiter.

 

To escape the field delimiter, you need to define the escape character and assign that value to the EscapeTextDelimiter attribute. That escape character will only be treated as an escape character if it immediately precedes a field delimiter. Otherwise that character is treated as data.

 

-- SteveF
Fan

Re: TPT - delemiter character in last column

Hi,

 

thanks a lot for this answer, helps a lot to understand how it works.

I tried to set a "EscapeTextDelimiter" but doesn't work since the only escape character I can set is the single quote : when I specify a VARCHAR EscapeTextDelimiter = ''' or VARCHAR EscapeTextDelimiter = '\'' I get the following error "Quoted string lacks a single quote terminator character (')". I'm not sure it's even possible to specify a single quote...

 

Teradata Employee

Re: TPT - delemiter character in last column

Set OpenQuoteMark to the double-quote character.

Set CloseQuoteMark to the double-quote character.

Make sure your quotes fields are enclosed by double-quotes.

If those are set up, you can use any other character for the EscapeTextDelimiter.

-- SteveF