Five Stars

need to convert delimited file to key value pair

I have input file ..

eg.

-------------------------------------------------------------      

name,number,country                                                        

yogesh,420420,India                                                         This is my csv file with headers and data

-------------------------------------------------------------

 

and  i want output file like

--------------------------------------------------------------

"name":yogesh,"number":420420,"country":India 

--------------------------------------------------------------

  • Data Integration
2 ACCEPTED SOLUTIONS

Accepted Solutions
Nine Stars

Re: need to convert delimited file to key value pair

I personally do this by next way (sure it possible todo different):

First of all, You need:

apache library - commons-lang-2.6.jar

than You can download from Talend Exchange (when talend Make it live !!! :-) ) routines - StringUtils and setup decencies in Studio:

 

Screen Shot 2017-05-29 at 8.29.03 PM.pngScreen Shot 2017-05-29 at 8.29.14 PM.png

 

 

I use standard tFileInputDelimited component:

 

Screen Shot 2017-05-29 at 6.40.35 PM.png

 

 

 

for example in this Job, file have comma separator, but I use TAB as separator in component:

1st iteration for header:

 

 

Screen Shot 2017-05-29 at 6.37.12 PM.png

 

 

which use limit 1 line - read only header line, component have only 1 column in schema - line, type String

than I parse header line to array:

 

 

Screen Shot 2017-05-29 at 6.38.47 PM.pngScreen Shot 2017-05-29 at 6.39.03 PM.png

 

 

Than read same file again, but with skipped line 1

 

 

Screen Shot 2017-05-29 at 6.37.28 PM.png

 

 

split each row to array

 

 

Screen Shot 2017-05-29 at 6.39.31 PM.png

 

 

 

and finally - concat JSON from 2 arrays:

 

 

Screen Shot 2017-05-29 at 6.39.45 PM.png

 

 

as result You will have output JSON column, You can remove quotes { } from code if You do not need them

 

-----------
Eleven Stars

Re: need to convert delimited file to key value pair

I've not looked at your other code, but would assume you mean this.....

 

System.out.println(((List<String>)globalMap.get("v header")).get(1));

The square brackets were incorrect.

Rilhia Solutions
14 REPLIES
Eleven Stars

Re: need to convert delimited file to key value pair

OK, set your tInputFileDelimited component up to have 3 columns (for each of your file columns) and to ignore the first row (as a header). The separator should be a comma. Connect this to a tMap. On the output of the tMap, connect all of your columns (name to name, number to number and country to country). In each of the output columns, hard code the relevant String to describe the value. As an example.....

 

"\"name\":"+ row1.name

 

The Strings are encapsulated in quotes because they are literal values. The \" is used to allow quotes to be included in the value. The above would result in a value like below if the name for that row was Richard....


"name":Richard

 

 

Hope that helps

Rilhia Solutions
Nine Stars

Re: need to convert delimited file to key value pair

You can use array functions (import java.util.Arrays)

 

read file row by row:

1) step 1 - split header line to array and save array size

2) step 2 - for each row split row to array

3) in loop (x from 0 to (array size -1) ) concat

"\""+header[x]+"\":"+value[x]

or concat

 

"{" than in loop 
"\""+header[x]+"\":"+"\""+value[x]+"\""
than "}"

for have valid JSON

 

I use this for create json structure from unknown csv (any number of columns)

 

-----------
Five Stars

Re: need to convert delimited file to key value pair

 
Five Stars

Re: need to convert delimited file to key value pair

Hi rhall, Thanks for quick reply but I am looking for solution when number of columns are not known.

Nine Stars

Re: need to convert delimited file to key value pair


yogeshmalekar wrote:

Hi, Thanks for quick reply but I am looking for solution when number of columns are not known.


:-)

 

vapukov wrote:

 

I use this for create json structure from unknown csv (any number of columns)

 

-----------
Five Stars

Re: need to convert delimited file to key value pair

Hi Vapukov, I will try and will ask if come across any problem. I need to find how to read file row by row in routine or in java code.

Nine Stars

Re: need to convert delimited file to key value pair

I do this in tJavaFlex

 

tFileInputDelimited - but delimiter different than real. Like | with real delimiter comma (,)

than tJavaFlex

 

I repeat it twice - 1st iteration for only 1st row, 2nd for all other with skipped 1st

-----------
Five Stars

Re: need to convert delimited file to key value pair

Hi vapukov,

    Please tell me how to read entire row at a time to store in a array and count the number of values in it using delimiter.

Nine Stars

Re: need to convert delimited file to key value pair

I personally do this by next way (sure it possible todo different):

First of all, You need:

apache library - commons-lang-2.6.jar

than You can download from Talend Exchange (when talend Make it live !!! :-) ) routines - StringUtils and setup decencies in Studio:

 

Screen Shot 2017-05-29 at 8.29.03 PM.pngScreen Shot 2017-05-29 at 8.29.14 PM.png

 

 

I use standard tFileInputDelimited component:

 

Screen Shot 2017-05-29 at 6.40.35 PM.png

 

 

 

for example in this Job, file have comma separator, but I use TAB as separator in component:

1st iteration for header:

 

 

Screen Shot 2017-05-29 at 6.37.12 PM.png

 

 

which use limit 1 line - read only header line, component have only 1 column in schema - line, type String

than I parse header line to array:

 

 

Screen Shot 2017-05-29 at 6.38.47 PM.pngScreen Shot 2017-05-29 at 6.39.03 PM.png

 

 

Than read same file again, but with skipped line 1

 

 

Screen Shot 2017-05-29 at 6.37.28 PM.png

 

 

split each row to array

 

 

Screen Shot 2017-05-29 at 6.39.31 PM.png

 

 

 

and finally - concat JSON from 2 arrays:

 

 

Screen Shot 2017-05-29 at 6.39.45 PM.png

 

 

as result You will have output JSON column, You can remove quotes { } from code if You do not need them

 

-----------
Five Stars

Re: need to convert delimited file to key value pair

Capture1.PNG

 

 

Capture2.PNG

 

 

Capture3.PNG

 

 

Capture4.PNG

 

 

Capture5.PNG

 

 getting this error... couldn't understand the problem....

Capture6.PNG

Five Stars

Re: need to convert delimited file to key value pair

thanks a lot....Its working now...Man Happy

Nine Stars

Re: need to convert delimited file to key value pair

Welcome! :-)

-----------
Five Stars

Re: need to convert delimited file to key value pair

 

System.out.println([(List<String>)globalMap.get("v header")].get[1]);

 

 

trying to get value from the List but getting this error..

Error in the component's propertiesSmiley Frustratedyntax error on token "(", Expression expected after this token

Eleven Stars

Re: need to convert delimited file to key value pair

I've not looked at your other code, but would assume you mean this.....

 

System.out.println(((List<String>)globalMap.get("v header")).get(1));

The square brackets were incorrect.

Rilhia Solutions