One Star

Merge two rows of a Delimited file to get one single register

Hi,
I'm parsing Delimited Files files to get information that I will put in a Database Table.
Now, I have a file where I have to merge each two rows, to get information about one single register (one row of the Database Table) - the line 1 has some fields from a database row, and line 2 has some other fields to put in the same row.
How can I process two rows at a time?
Thanks.
7 REPLIES
Six Stars

Re: Merge two rows of a Delimited file to get one single register

Hi,
The rows you want to merge, are they from the same file or are the rows from different files?
And you want to merge rows, not columns?
If you want to merge columns, that can be done using tmap. You have to only drag the columns you want to merge to a single output column. In the expression field of the output column, append the input columns using '+'.
Thanks,
Remy
One Star

Re: Merge two rows of a Delimited file to get one single register

Remy, I want to merge rows that are in the same file : row 1 + row 2; row 3 + row 4 ; ...
Six Stars

Re: Merge two rows of a Delimited file to get one single register

These rows, do they have any identifier columns associated with them? like row1 has value 1 and row2 has value 2 , and row 3 has value 1 and row 4 has value 2, so that we know that all the rows with value 1 have to be appended with the next row having the value 2.
(values 1 and 2 are examples, if you have any identifier like these , you can use them to filter out rows and store the filter and reject results in separately and then later append them using tmap)
One Star

Re: Merge two rows of a Delimited file to get one single register

Assuming that I have a file with 6 rows, it corresponds to 3 entries in my Database Table, that has 9 columns. From "odd lines" I get the Columns 1, 3, 4, 5, 8 and 9. From "even rows", I get the remaining info (Columns 2, 6 and 7):
IN | COLUMN1 | xxxxxxx | COLUMN3 | COlUMN4 | COLUMN5 | xxxxxxx | xxxxxxx | COlUMN8 | COlUMN9
OUT | xxxxxxx | COlUMN2 | xxxxxxx | xxxxxxx | xxxxxxx | COlUMN6 | COLUMN7 | xxxxxxx | xxxxxxx
IN | COLUMN1 | xxxxxxx | COLUMN3 | COlUMN4 | COLUMN5 | xxxxxxx | xxxxxxx | COlUMN8 | COlUMN9
OUT | xxxxxxx | COlUMN2 | xxxxxxx | xxxxxxx | xxxxxxx | COlUMN6 | COLUMN7 | xxxxxxx | xxxxxxx
IN | COLUMN1 | xxxxxxx | COLUMN3 | COlUMN4 | COLUMN5 | xxxxxxx | xxxxxxx | COlUMN8 | COlUMN9
OUT | xxxxxxx | COlUMN2 | xxxxxxx | xxxxxxx | xxxxxxx | COlUMN6 | COLUMN7 | xxxxxxx | xxxxxxx
Six Stars

Re: Merge two rows of a Delimited file to get one single register

Does IN and OUT signify anything? i.e is that part of the columns? Or can anything like that be added?
Now what I understand from this is that your output from odd lines should be the appended result of columns 1,3,4,5,8 and 9. And output from even lines is the appended result of rest of the columns. Am I correct?
Or, From this example,Do you want to append all INs with OUTs?
Could you tell what is your expected output from this?
I am sorry if I am asking too many questions! Smiley Happy
One Star

Re: Merge two rows of a Delimited file to get one single register

IN and OUT are the first field from the delimited file. This field will not be processed, it just mean that one 'IN' row with the next 'OUT' row are one single register that will be put in the database.
The first entry to be placed in the database consists of Columns 1, 3, 4, 5, 8 and 9 from the first row, and Columns 2, 6 and 7 from the second row.
The second entry consists of Columns 1, 3, 4, 5, 8 and 9 from the third row, and Columns 2, 6 and 7 from the fourth row.
Six Stars

Re: Merge two rows of a Delimited file to get one single register

Then I think you should try something like this:
First, add a column to your data, preferably using tmap which gives same values to your consecutive INs and OUTs. Probably an expression like "DataOperation.CHAR(Numeric.sequence("s1",2,1)/2)". This will give the values like (the last column added)
IN | COLUMN1 | xxxxxxx | COLUMN3 | COlUMN4 | COLUMN5 | xxxxxxx | xxxxxxx | COlUMN8 | COlUMN9 |1
OUT | xxxxxxx | COlUMN2 | xxxxxxx | xxxxxxx | xxxxxxx | COlUMN6 | COLUMN7 | xxxxxxx | xxxxxxx | 1
IN | COLUMN1 | xxxxxxx | COLUMN3 | COlUMN4 | COLUMN5 | xxxxxxx | xxxxxxx | COlUMN8 | COlUMN9 |2
OUT | xxxxxxx | COlUMN2 | xxxxxxx | xxxxxxx | xxxxxxx | COlUMN6 | COLUMN7 | xxxxxxx | xxxxxxx |2
Then I think you should filter on the first field of the file. You could filter out all the INs and save it to a temporary file or temporary table and then take out all the rejects, that would be the OUTs to another temp file or table.
Then you could join both these tables or files using the new column you added i.e column 10 from the above example as The consecutive INs and OUTs would have the same value in Column 10. And you can append all the columns and rows you need to append in the tmap easilly.
Let me know if you have any questions regarding this.