Six Stars

How to convert every 2 records of a file, convert into multiple column.

Hi all,

 

How to convert every 2 records of a file, convert into multiple column.

Source files: .txt(plain text)

1230 sagar bang 230.00CR 

  date:23-10-2017 time:12:20PM 

1220 amit mum 210.00CR 

  date:22-09-2017 time: 12:00 AM

 

Output: format(.CSV/.xlsx)

col1                                                         col2

1230 sagar bang 230.00CR      date:23-10-2017 time:12:20PM 

1220 amit mum 210.00CR        date:22-09-2017 time: 12:00 AM

 

Kindly how to implement above logic.

 

Thanks

Shree

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars

Re: How to convert every 2 records of a file, convert into multiple column.

OK, I have tweaked my example to support your FEE rule. This example will handle up to 5 columns. It won't "just work" with your data. You will need to map your columns correctly (the errors you previously had were likely because of column name issues). Give it a go.

Rilhia Solutions
13 REPLIES
Twelve Stars TRF
Twelve Stars

Re: How to convert every 2 records of a file, convert into multiple column.

Hi,

tMemorizeRows is the component you need for this case.

Here is an example based on your sample data:

Capture.PNG

Here, tFixedFlowInput contains the 4 rows from your example (1 column called "line").

tMemorizeRows is configured like this:

Capture.PNG

As shown, 2 records are memorized, giving an array with 2 entries.

tMap is configured like this:

Capture.PNG

A local variable called "rank" is created just to know if the record's rank is odd or even (see filter on the right side).

When rank is even (var.rank % 2 == 0) col1 is populated using "line_tMemorizedRows_1[1]" which the previous line value.

Also, col2 is populated with current line value.

When rank is odd the current record is ignored (but it is memorized).

Here is the result:

Starting job test at 10:37 06/11/2017.

[statistics] connecting to socket on port 4070
[statistics] connected
.------------------------+------------------------------.
|                      tLogRow_11                       |
|=-----------------------+-----------------------------=|
|col1                    |col2                          |
|=-----------------------+-----------------------------=|
|1230 sagar bang 230.00CR|date:23-10-2017 time:12:20PM  |
|1220 amit mum 210.00CR  |date:22-09-2017 time: 12:00 AM|
'------------------------+------------------------------'

[statistics] disconnected
Job test ended at 10:37 06/11/2017. [exit code=0]

That's what you're expected.


TRF
Twelve Stars

Re: How to convert every 2 records of a file, convert into multiple column.

This is kind of difficult to explain without a demonstration, so I have written a small job which shows how to do this. Essentially what I am doing is using a modulus operation to identify every second row and making use of the fact that tMap variables store their values between rows. Take a look at the job.

By  the way, the job is written in 6.2.1. Later versions of Talend will be able to open this.

Rilhia Solutions
Twelve Stars

Re: How to convert every 2 records of a file, convert into multiple column.

We have to stop answering the same questions at the same time @TRF

Rilhia Solutions
Twelve Stars TRF
Twelve Stars

Re: How to convert every 2 records of a file, convert into multiple column.

@rhall_2_0you're right Smiley Wink
It seems yours is more efficient as soon it doesn't require a tMemorizeRows.

 

Note: but mine will work also is the requirement is to get values from 3 records or more  Smiley Wink


TRF
Twelve Stars

Re: How to convert every 2 records of a file, convert into multiple column.

It's essentially the same way though @TRF. I tend to make a lot of use of the fact that tMap variables keep values between rows. It's a trick I used to use during my Informatica days :-)

Rilhia Solutions
Six Stars

Re: How to convert every 2 records of a file, convert into multiple column.

Hi @TRF. and @rhall_2_0

 

Thanks for your inputs. The logic is working fine.

 

There is small change like, number records is not constant need to convert.

 

Some time i need to convert 3 records into 3 columns and  convert 2 records to 2 columns.

Just checked source files.

 

Thanks

Shree

Twelve Stars TRF
Twelve Stars

Re: How to convert every 2 records of a file, convert into multiple column.

Right, it's not exactly the same case.

However, can you share a representative data sample?


TRF
Twelve Stars

Re: How to convert every 2 records of a file, convert into multiple column.

Is this for different jobs or within the same job? If it is for different jobs, you can extrapolate from what we have shown. If it is for the same job (sometimes 2 values, sometimes 3 values) you will need a method to know when it is 2 values and when it is 3 values.

 

I have attached an example of how this can be done if you pre-process the data to identify if 2 or 3 rows make up a single row. 

 

As @TRF has said, you have moved the goalposts a little bit here.

Rilhia Solutions
Six Stars

Re: How to convert every 2 records of a file, convert into multiple column.

Hi Richard,

 

I am sharing the design, error and sample data(3 records). To identify record last line the line start with FEE.

 

I am getting error with error screen shot. 

 

As per your design is working me.

 

Thanks

Shree

Twelve Stars

Re: How to convert every 2 records of a file, convert into multiple column.

OK, I have tweaked my example to support your FEE rule. This example will handle up to 5 columns. It won't "just work" with your data. You will need to map your columns correctly (the errors you previously had were likely because of column name issues). Give it a go.

Rilhia Solutions
Six Stars

Re: How to convert every 2 records of a file, convert into multiple column.

Hi Richard,

 

I have tried with your logic. 

 

I am not getting any error. But data is not displaying on console.

 

I have tried with print hearer option. i am getting print option. PFA screen shot for references.

 

Look like delimiter is issue? or can check in notepad++ sample records.

 

Thanks

Shree

 

Six Stars

Re: How to convert every 2 records of a file, convert into multiple column.

Hi Richard,

 

Its working fine, i changed source components to tfileinputdelimietd_1.

 

Thanks you so much.

 

Regards

Shree

Six Stars

Re: How to convert every 2 records of a file, convert into multiple column.

Hi Richard,

 

I have small question same topic.

 

Source i am getting black records. Same sample records i am getting before 1st records and between some records i am getting null or blank records.

 

How to remove before convert column to rows.  PFA files.

 

Thanks

Shree

 

Thanks

Shree