topic Re: How to iterate through csv and read row by row and assigning them in tJavaRow in Talend Studio

How to iterate through csv and read row by row and assigning them in tJavaRow

Anonymous — Sat, 16 Nov 2024 07:16:59 GMT

I have a flow below where I am reading the csv file

tFileInputDelimited_1 -> tFlowtoIterate -> tJavaFlex -> tFileInputDelimited_2 -> tJavaRow -> tFlowToIterate -> tHiveInput -> tFileOutputDelimited.

I am preparing a SQL query using above flow and sending it to Hive.

I have delimited data in tFileInputDelimited_1 which i wanted to iterate through and assign them in tJavaFlex and tJavaRow accordingly (i.e. to contextVariables).

For each row field in csv (tFileInputDelimited_1), which matches a condition(i.e. based in tFileInputDelimited_2), i want to assign to context variables accordingly.

tFileInputDelimited_1:

123-D1-abc-rtey121-a1b1-1416

123-D1-abc-bd2tr-a1y14-1311

141-B1-fhf--a1fb32-utoot21b-157

141-B1-fhf--vefb32-utoot21b-157

141-B1-fhf--k191-utoot21b-157

456575-C1-21-ytuueoro-ghfyt-43

4567-B1-abc-grth-a1b1-1287

tFileInputDelimited_2:

sno,section_name,col_1,col_2,col_3

1, section_name_1,<string_a1>,<string_a2>,<string_a3>

2, section_name_2,<string_b1>,<string_b2>,<string_b3>

Code in tJavaRow:

String v_filter = ""

if(input_row.sno == 1 && input_row.name.equals("section_name_1")) /* conditions from tFileInputDelimited_2*/
{
    while((String)globalMap.get("row.line") != null) /*lines from tFileInputDelimited_1*/
    {
      //the below code should loop 
      if (context.AP_CODE.equals("D1") && context.I_NAME.equals("abc"))
      {
         v_filter = "AP_CODE_V =" + "'" + context.AP_CODE_V + "'"
          + " AND I_NAME_V=" + "'" + context.I_NAME_V + "'"
          + " AND ID_V =" + context.ID_V 
      } 
    } 
} 
..
..

I need output like below (i.e. for each matching condition).. 
AP_CODE_V = 'rtey121'
I_NAME_V = 'a1b1'
ID_V = 1416

AP_CODE_V = 'bd2tr'
I_NAME_V = 'a1y14'
ID_V = 1311

The Problem is that I am able print only first set. I am not able to loop through other set of values(Seems like while condition is not working).

Please help me, where i am doing wrong.

Note: I thought of organizing these files in JSON, so that it would be easy to parse through and iterate. but some how started with delimited files to get some quick turn-around.

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Anonymous — Thu, 15 Nov 2018 21:34:23 GMT

I think we'll need to see the component linkages to help with this. The issue seems to be that globalMap.get("row.line") isn't iterating through the rows correctly: are you sending it an Iterate? Your workflow is:

tFileInputDelimited_1 -> tFlowtoIterate -> tJavaFlex -> tFileInputDelimited_2 -> tJavaRow -> tFlowToIterate -> tHiveInput -> tFileOutputDelimited.

which has other components between the tFlowtoIterate and tJavaRow. Or is the code you posted from tJavaFlex?

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Anonymous — Fri, 16 Nov 2018 02:56:01 GMT

hi @DVSCHWAB,

The flow is same as mentioned.

1) tFileInputDelimited_1 -> has the test data as mentioned. I connected this to tFlowtoIterate

2) tJavaFlex -> I am assigning the above test data to context variables. (i have no other code here apart from variable assignment)

3) tFileInputDelimited_2 -> has the data (as mentioned above) to form the SQL query template.

4) tFlowtoIterate_1 -> connects to tJavaRow to loop the data

5) tJavarow_1 -> this is where my logic to assign test data from tFileInputDelimited_1 to template in tFileInputDelimited_2 (i.e. the code which i poisted)

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Anonymous — Fri, 16 Nov 2018 10:15:38 GMT

Any update ? Please let me know..

CC:

@rhall

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Anonymous — Fri, 16 Nov 2018 16:30:37 GMT

What data are you intending to loop in your tJavaRow?

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Anonymous — Fri, 16 Nov 2018 17:57:52 GMT

Hi @rhall

tFileInputDelimited_1:

123-D1-abc-rtey121-a1b1-1416

123-D1-abc-bd2tr-a1y14-1311

141-B1-fhf--a1fb32-utoot21b-157

141-B1-fhf--vefb32-utoot21b-157

141-B1-fhf--k191-utoot21b-157

456575-C1-21-ytuueoro-ghfyt-43

4567-B1-abc-grth-a1b1-1287

tFileInputDelimited_2:

sno,section_name,col_1,col_2,col_3

1, section_name_1,<string_a1>,<string_a2>,<string_a3>

2, section_name_2,<string_b1>,<string_b2>,<string_b3>

eg:- I want to loop over 2 times on tFileInputDelimited_1, if (sno == 1 && sec_name_1) from tFileInputDelimited_2 and while((context.AP_CODE.equals("D1") && context.I_NAME.equals("abc"))) //based on matching criteria from each line. (i..e D1 and abc)

i.e.

123-D1-abc-rtey121-a1b1-1416

123-D1-abc-bd2tr-a1y14-1311

so that I will get output like this (i.e., the values which i need based on matching condition)..

AP_CODE_V = 'rtey121'
I_NAME_V = 'a1b1'
ID_V = 1416

AP_CODE_V = 'bd2tr'
I_NAME_V = 'a1y14'
ID_V = 1311

Unfortunately, I don't have a join condition to match between these two files.

Similarly For, (sno == 2 && sec_name_2) and while((context.AP_CODE.equals("B1") && context.I_NAME.equals("fhf")))

AP_CODE_V = 'a1fb32'
I_NAME_V = 'utoot21b'
ID_V = 157

AP_CODE_V = 'vefb32'
I_NAME_V = 'utoot21b'
ID_V = 157

AP_CODE_V = 'k191'
I_NAME_V = 'utoot21b'
ID_V = 157

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Anonymous — Sat, 17 Nov 2018 12:22:15 GMT

Please let me know where I am doing wrong! I am not sure if I am doing wrong in while condition, I.e to loop over tFileInputDelimited_1 (to retrieve only that row which satisfies the 2nd if condition)?

(or) should I use tJavaFlex instead of tJavarow for this purpose?

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Anonymous — Sat, 17 Nov 2018 14:10:32 GMT

OK, you need to understand the strict flow here. For every row from your first file, every row of your second file will be returned. That is how iterate links work. You cannot force subsequent rows from the first file in middle of an iteration. If you first file has two rows of data (holding values "1" and "2") and your second file has 4 rows (holding values "a", "b", "c" and "d") and if you ran them through your configuration, the rough permutations of data would be below....

"1", "a"

"1", "b"

"1", "c"

"1", "d"

"2", "a"

"2", "b"

"2", "c"

"2", "d"

I suspect you might need to use a tMap to join your data from your files and perform your logic there.

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Anonymous — Sat, 17 Nov 2018 14:14:52 GMT

I confess that I'm not sure why your code isn't working; it looks correct to me. At this point, what I would do (if this were my project) is create a new job with only the tfileDelimited2 and tJavaRow and see if it works (output to tLogRow). If it does, add back the other components one at a time until you find the source of the problem (if it doesn't work with just the minimal components, strip the Java code down to the bare minimum to prove the functionality exists).

Best of luck,

David