topic Re: Convert key, values file to data base in Talend Studio

Convert key, values file to data base

Jérôme35000 — Fri, 15 Nov 2024 21:58:07 GMT

Hello,

here my need i'm looking for a while

my input text file has the form :

"column 1" value xx

"column 3", value yy

"column 5", value zz

"column 1", value aa

"column 2", value bb

"column 6", value cc

"column 7", value dd

"column 1", value gg

"column 4", value ee

....

and i want to convert to a data base whose columns are : column 1, column 2, column 3 ... column 20

I'v tryed many components (tmap, tdenormalize, tnormalize, tagregate ...) but can't find

an issue is not all column are present for each reccord

many thanks for help

Re: Convert key, values file to data base

Anonymous — Mon, 27 Mar 2023 06:02:30 GMT

Take a look at this KB article which shows how to convert rows to columns, add a sequence id for each "column x" before tPivotToColumnsDelimited, the sequence if will be the group element on tPivotToColumnsDelimited.

tFileInputDelimited--tMap--tPivotToColumnDelimited

on tMap, add a new column called "id", and set its value as:

Numeric.sequence(row1.firstColumnName,1,1)

Please try and let me know if it works.

Regards

Shong

Re: Convert key, values file to data base

Jérôme35000 — Mon, 27 Mar 2023 06:18:08 GMT

Hi,

thanks for your answer

The issue is that I've no "id" field to link the rows

All I have, is that data of "column 1" have to be in the same column.

For now, the best solution i've tried, is to do a Java routine to parse the imput file to make a CSV file

Re: Convert key, values file to data base

Anonymous — Mon, 27 Mar 2023 09:37:09 GMT

After generating the file using tPivotToColumnsDelimited, read the generated file and remove the "id" column, this is the only way using tPivotToColumnsDelimited component to achieve the needs without Java codes.

Re: Convert key, values file to data base

Jérôme35000 — Mon, 27 Mar 2023 15:35:05 GMT

I have no ID column in the input file

I have just the label "column 1" that is repeating each time a new object is declared