[resolved] Unpivot / Talend component question

[Anonymous]

Hi,
I'm wanting to convert:
userid, usersignature, prop1, prop2, prop3,prop*
blah,20090303170000,val1, val2, val3, val*
into 2 files:
userid, usersignature
blah, 20090303170000
userid, propname, propvalue
blah, prop1, val1
blah, prop2, val2
blah, prop3, val3
...
I've searched these forms and found that there's a component that has been created to do this:
18764
I've only just worked out (as I'm writing this!) that java projects need java components, and perl projects need perl components - which is why that component doesn't appear within my Java project palette. I don't really understand how these components work, so I don't know how easy it would be to make the above component available in Java. Could someone please comment on this?
Alternatively, does anyone have any advice on how to get the result that I'm looking for above? The input file will be text, and I'd like the output to be a bulk insert into an Oracle DB.
Any help would be appreciated

[Anonymous]

Hello

userid, usersignature
blah, 20090303170000

It is very easy to get the first output file, I think you have got it.

userid, propname, propvalue
blah, prop1, val1
blah, prop2, val2
blah, prop3, val3
...

See the related 5668, please try to reproduce the job.
Feel free to ask for help on forum if you meet any questions!
Best regards

shong

[Anonymous]

Thanks for the suggestion, however I'm having trouble getting it working.
For an input of:
col1, col2, col3, col4, col5
val11, val12, val13, val14, val15
val21, val22, val23, val24, val25
I'm getting an output of:
col1|val15
col2|val15
col3|val15
col4|val15
col5|val15
i.e. it's giving me the column names correctly, but it always returns the column value as whatever the last value of the 2nd line is.
Any idea what would cause that? I've compared the settings against the example over and over.
AND, it only ever returns the first line of values - nothing from the second line of values is ever output.
Cheers,

[Anonymous]

Thanks for the update on the other thread Shong, however that now only gives me the output of the first row of data - I now need to work out how to get it to return all rows in my file.
I've tried changing the tSampleRow_2 to return a greater sample range, but it still only returns the first row of data. I tried removing the sampleRow component altogether with the same result. I'm assuming this is because it's tied to only bringing back 1 row from the top half of the process (the headers).
So how do I get the top half to re-run for every row of the bottom half?
Cheers

[Anonymous]

I gave up trying to do it with built-in components and ended up writing my own. I've submitted it to the exchange in case anyone else needs it:
148

[Anonymous]

Hello daztop
Thanks for your component!!!
Can you share us a use case here?
Thanks again.
Best regards

shong

[Anonymous]

The component seems to work very well, thank you for creating this!
However, it does not handle null values correctly.

row2.pivot_key = field;
try {
	row2.pivot_value = row1.getClass()
			.getDeclaredField(field).get(row1)
			.toString();
} catch (Exception e) {
	// Can't happen - the field was there to have
	// created the array in the first place
}

The problem with this is that if the field contains a null value, it will throw a NullPointerException. Since this is caught by the catch-clause, pivot_value will just keep the value of the previous field, which is quite confusing. I'd suggest replacing the "Can't happen"-comment with "row2.pivot_value = null;".

[Anonymous]

Hi daztop,
I have tested your Java component tUnpivotRow followiing the use case developped by plegall in 18764].
The scenario works with the Perl component but with the Java component I get the following error message. I have tested the scenario a few times with different files or settings but the error is always the same.
I am not a Java specialist, could someone explain where the error comes from so I can fix it?
Thanx

[Anonymous]

Has anyone got this component to work? From the description, this is exactly what I'm looking for, but I haven't got it to work. I've loaded the component (and surfaced it within the component pallet), but I'm still getting errors when attempting to run.
Can anyone provide a quick how-to/tutorial (with screenshots) on the java-based tUnpivotRow component?

[Anonymous · Accepted solution]

Sorry for not responding to your direct email sooner bbeliaso.
The most basic example would be:
tFileInputDelimited -> tUnpivotRow -> tFileOutputDelimited
Connect the 3 components together in that order, and configure the file input and file output accordingly - ensure that you set up the schema for the file input and then cascade it through to the tUnpivotRow.
Now click the tUnpivotRow component and view the component configuration tab.
Under the Row Keys area, add all the columns that you want to remain fixed.
e.g. when the input is:
columnKey,property1,property2,property3,property4
------------------------------------------------------------
myKey,value1,value2,value3,value4
And you want:
columnkey,keyName,keyValue
-----------------------------------
myKey,property1,value1
myKey,property2,value2
myKey,property3,value3
myKey,property4,value4
Then your row key will just be columnkey - that's the only that you need to add. All other columns then become rows in your output file. You can add as many columns as you like to this, and they'll all be passed through for every line of output.
The output schema for the tUnpivotRow component will then be equal to all of your row keys, plus the fixed pivot_key and pivot_value columns.
Does that help you out?