Six Stars

Writing regular expression in tmap

Hi all,

 

How to write regular expression in tmap.

 

Source column data.

02 04OCT 10:48:16 220001 .

 

Output:

col1  col2      col3      col4

02,  04OCT,  10:48:16, 220001

 

Thanks

Shree

20 REPLIES
Six Stars

Re: Writing regular expression in tmap

What about doing below?
col1=source_col.split(' ')[0]
col2=source_col.split(' ')[1]
col3=source_col.split(' ')[2]
and so on??
Eight Stars

Re: Writing regular expression in tmap

Hi,

 

You could use tFileInputRegex or tExtractRegexFields components.

 

Eric

Six Stars

Re: Writing regular expression in tmap

Hi @eric44,

 

i have multiple columns for single file like that, i can't use single tFileInputRegex or tExtractRegexFields components if am correct.

 

Coulm1                                       column 2                    column 3

02 04OCT 10:48:16 220001 .     13:38:16 220001      220001 12OCT 2.4CR

 

i need to insert records single records to multiple columns based on regular expression in tmap or substr functions.

 

Thanks

Shree

Six Stars

Re: Writing regular expression in tmap

Hi @chirgal,

 

i am getting error. PFA screen shot for references.

 

Thanks

Shree

Six Stars

Re: Writing regular expression in tmap

Please use the below expression for splitting
source_col.split("\\s+")
Six Stars

Re: Writing regular expression in tmap

Hi @chirgal,

I am facing error.

Source data for single column,
02 2OCT 10:57:37

Expected output:
col1 col2 col3
02 2OCT 10:57:37.  

PFA files.

 

And, Let me clear my requirement.

 

Source:

 

Coulm1                                              column 2                                             column 3
02 04OCT 10:48:16  220001 .   13:38:16 220001 220001                        12OCT 2.4CR

 

Output:

colu1  col2       col3           col4         col5          col6     col7       col 8       col9 

02         04OCT 10:48:16  220001 13:38:16  220001 220001   12OCT  2.4CR

 

Thanks

Shree

Six Stars

Re: Writing regular expression in tmap

Use below expression:
col1=source_col.split("\\s+")[0]
col2=source_col.split("\\s+")[1]

and so on
Six Stars

Re: Writing regular expression in tmap

Hi @chirgal

 

Thanks, As per requirement is working fine.

 

But one issue i am facing. it not handling space or blank..

Source data for single column,
02 2OCT 10:57:37  <space> 20

02 2OCT 10:57:37     23        20

 

Current output:
col1 col2 col3        col4
02 2OCT 10:57:37. 20

02 2OCT 10:57:37  23 

 

Expected output:

col1 col2 col3             col4     col5
02 2OCT 10:57:37. <space> 20

02 2OCT 10:57:37     20        23 

 

i am attching two records for your references.

 

Thanks

Shree

 

Six Stars

Re: Writing regular expression in tmap

Use below expressionSmiley Sadremove + sign)
col1=source_col.split("\\s")[0]
col2=source_col.split("\\s")[1]

and so on
Six Stars

Re: Writing regular expression in tmap

Hi @chirgal,

 

I have tested problem is, the source is coming with single space and multiple space.

So it create mutiple columns unnecessary and some records moving next columns.

 

Ex: 

input:

colun1

12 <space>

13 <space><space>  26

 

output:

colu1      col2           col3          col4

12        <space>

13        <space>      <space>           26

 

Thanks

Shree

Six Stars

Re: Writing regular expression in tmap

Hi @chirgal

 

Any idea on how to over come on issue with multiple space.

 

Thanks

Shree

Six Stars

Re: Writing regular expression in tmap

Let's try to replace multiple spaces on source:

Try this:

Source_col.trim().replaceAll("\\s+", "\\s");
Six Stars

Re: Writing regular expression in tmap

Hi @chirgal,

 

Records are inserting whole column names into single columns.

 

I am attaching screen shot for references.  Kindly help me on this we are near to close issue.

 

Thanks

Shree

Six Stars

Re: Writing regular expression in tmap

First try to read whole line into single column using TfileInputFullRow.

Then use tmap to replace multiple space to single space. Source_col.trim().replaceAll("\\s+", "\\s");

After that use TExtractDelimitedFields to split the column based on space to multiple columns.

 

Got some idea?

Sorry. I don't have access to studio. Cant attach job or snaps

 

 

Six Stars

Re: Writing regular expression in tmap

Hi @Chirgal,

 

Sure,  TfileInputFullRow ----> tmap --> tfileoutputfiledelimited.

 

I am getting values multiple space and single space with "s".

 

Ex:

colu:

02 24OCT 10:57:37

 

Output:

02s24OCTs10:57:37s4246899014

 

How to replace "s" wiht space or any other delimiter. Before jumping to TExtractDelimitedFields.

 

Or, using TExtractDelimitedFields  it will handle "s" values between fileds?

 

 

Thanks

Shree

Six Stars

Re: Writing regular expression in tmap

No need to replace. In textractdelimetedfields, use s as delimetre. It will separate column based on s
Six Stars

Re: Writing regular expression in tmap

Hi @Chirgal,

 

Everything is fine, challenge i am facing column issue. Please check below example.

 

Ex: Source data

 

12 <space> 03oct <space>13:21:21  <space>             <space>    121212 ...

13 <space> 05oct  <space>12:2:11    <space>   0101  <space> 121212 ...

 

Intermediate result after removing multiple space.

colum1:

12s03octs13:21:21s121212s...

13s05octs12:2:11s0101s121212s...

 

Current output am getting

col0   col1        col2         col3 ..     col4

12     03oct     13:21:21    121212  ...

13     05oct     12:2:11        0101    121212 ...

 

Expected ouptut

col0   col1         col2      col3        col4

12     03oct   13:21:21                  121212 ...

13     05oct   12:2:11       0101     121212 ...

 

Thanks

Shree

Six Stars

Re: Writing regular expression in tmap

Can you check, if source columns are of fixed width?
Are column starting positions are always same in source file??
Six Stars

Re: Writing regular expression in tmap

Hi Chirgal,

 

No, If you ask me  length of the columns is fixed: No,

 

i am tried with regex and positional file. it not working as expecting.

 

Same issue with <space>

 

Thanks

Shree

Six Stars

Re: Writing regular expression in tmap

Hi @Chirgal,

 

Let me explain over view about file.

 

1) Source file come with header,footer and along with not related lines in the file between records. (using tfileinputfullrow ,tfilter and extractdelimeitedfiled done)

2) Single records come with multiple lines 2,3 or 4 lines some records. (tmap done)

3) based on records need to split columns.Some contains space, multiple space and no fixed length for columns.(not done)

 

Hints:

 

1) Records start with digit, we need to pick only 2 lines which lines start with digits.

2) Need to concatenate 2 lines into single line.

3) remove space and split fields and store into database.

  

 

My design: 

 Step 1 and step 2 i have resolved.  Problem my facing with spaces.

 

Thanks

Shree