How to Concatenate Multiple Rows in File to Single Row in Oracle Load?

One Star

How to Concatenate Multiple Rows in File to Single Row in Oracle Load?

Hello..
I'm relatively new to Talend, and I'm trying to evaluate it for use within my department for ETL/ELT purposes. I'm running into a few "how to" snags. This is one of them.
I've been searching the forums here for a while with no luck. Perhaps I'm not searching the correct terms. I'm also a novice when it comes to Java coding, so if I need to create something new/custom, I'll have to spend some time on that, as well.
Here is what I'm attempting to do:
I have a delimited file with many logical records that span 16 physical rows each. I need to load every 16 rows in the file as a single row into an Oracle database. In Oracle SQL*Loader, you can simply set the CONCATENATE option in the Control file accordingly and the SQL*Loader would load every 16 physical rows into a single logical row in the database table:
CONCATENATE 16
As a shortened example with every 4 rows needing to be concatenated for the table load, the contents of the file would look like this (example is 2 records over 4 rows each):
Sam|Jones|Jr.|
123 Main St.|Sometown|CA|91202|
M|19320212|80|
818-555-2343|213-555-6883 x345|SJones@email.com
Jane|Smith||
456 Broadway Ave.|Some City|UT|72513-2135|
F|19090123||19991203
909-555-0092||
These 2 records would need to be inserted as a concatenation of every 4 physical rows into an Oracle table that looks like this:
FIRST_NM|LAST_NM|SUFFIX|ST_ADDR|CITY|STATE|ZIP|GENDER|DOB|AGE|DOD|HOME_PHONE|WORK_PHONE|EMAIL
1st record in the table:
Sam|Jones|Jr.|123 Main St.|Sometown|CA|91202|M|19320212|80|818-555-2343|213-555-6883|SJones@email.com
2nd record in the table:
Jane|Smith||456 Broadway Ave.|Some City|UT|72513-2135|F|19090123||19991203909-555-0092||
How do I get Talend to load these 8 physical rows as 2 logical records without having to reconstruct or recreate the file in a different format? We do not have control over how the source system generates the data files. Recreating the files daily would be time consuming. There are no record delimiters other than every (4n+1)th line is the first line of a new logical record. We do not add any sequences or keys to the table, just load the data that exists in the file. The files themselves are approximately 1GB each and can contain as many as 100,000 logical records each, with each logical record spanning 16 physical rows.
The tDenormalize does not appear to be the answer, as I'm not trying to group or aggregate on a particular field in the records. tUnite seems to be for multiple sources into the same table at once..?
Does anyone have any experience with what I'm trying to do who would be able to help?
Much thanks in advance.
One Star

Re: How to Concatenate Multiple Rows in File to Single Row in Oracle Load?

You can try to use:
tDelimited with RowSeparator = "\n" and Field Separator = "" (empty) - Edi Schema and add just 1 row
It will read line by line
after that add a tJavaRow and in Context add a variable called counter with starting value 0
Inside the tJavaRow use:
"context.counter = context.counter + 1;
string linebreak = "";
if(context.counter == 4)
{
context.counter = 0;
linebreak = "¬";
}
output_row.line = input_row.line + linebreak;"
Every 4 rows it will add the "¬" making this your actual row breaker.
You can save it to another delimited and read this file with TFileDelimited RowSeparator = "¬" and Field Separator = "|" (empty) - Edi Schema and add the fields
Now your 4 lines is 1 row defined by the "¬"
One Star

Re: How to Concatenate Multiple Rows in File to Single Row in Oracle Load?

Thanks for your reply. I need that to go one step farther and avoid having to rewrite or recreate the files. They're large, and refresh window for the data will not allow for the time to rewrite the file.
Is there a way to do this concatenation of rows directly into a database insert (tOracleOutput) whether through a tMap or some other device?
Seventeen Stars

Re: How to Concatenate Multiple Rows in File to Single Row in Oracle Load?

Are you able to let the files created with enclosure chars like " ?
In this case if you have an file like this:
"Sam"|"Jones"|"Jr."|"
123 Main St."|"Sometown"|"CA"|"91202"|"
M"|"19320212"|"80"|"
818-555-2343"|"213-555-6883 x345"|"SJones@email.com"
These 4 line will be read as 2 lines from the tFileIinputDelimited because of the ability to ignore line breaks within enclosed values.
One Star

Re: How to Concatenate Multiple Rows in File to Single Row in Oracle Load?

Thanks for your suggestion.
Unfortunately, no. The examples I posted are how we received the data and we're unable to have the source system change them.