Unable to insert rows into Excel (BUG)

[jensej]

Hi 

Im working with Talend Open Studio for Data Integration Version 7.3.1 and I have a problem that's driving me insane.

I try to explain this part of my job (picture). First of all i have a context.output that has either csv or excel. If the context.output is excel as in this case the job is going to create a output.xlsx file and for csv a output.csv.
 
Up left I create an Excel or CSV File and then i'm inserting a header. 

In the next subjob I'm iterating over a list of DB connections. With this connections i'm then running a non select query. In this case an Insert. If the connection is wrong im catching that error and makes an insert to the Excel saying that it was connection error for this connection. If it gets rejected I do the same and if it's succesfully executed I wan't to make a insert with infos about the insert for that connection. 

For csv everything works as I want but for excel I can't manage to Insert row where the red frame is. On the other OutputExcel (green frame) the inserts works as expected. I have doublechecked all settings and it component has exact the same settings/schema and so on as the others. Im appending rows to existing file and sheet from the file that were created in the headers part. 

I made a copy of the schema to a logrow to see if really had some values to insert and I even tried to add a tjavarow between the tmap and my exceloutput to see if that could solve why I just can't insert this row. I just passed the values thru the tjavarow and printed each row. I marked the tjavarow and tlogrow with colors so that you can see what it returns to the log but even though tjavarow and tlogrow has value my file holds rows from all the green frames but not from the red one. 

This has to be a bug or not? Im inserting into a postgres database if that is neccesarry to know. 




In this part of my job 

[Shicong_Hong]

Hi

Can you still see the header information in the Excel file? I guess the file has been re-created for each iteration. All tFileOutputExcel components are set to write data to the same file? If so, make sure the 'Append existing file' checkbox is checked on all tFileOutputExcel components, otherwise, a new file with the same file name will be created for each iteration, you will see the file is empty if there is no data for the last iteration. From your job screenshot, I see 0 row was passed to tFileOutputExcel (in red) for the last iteration.

Regards

Shicong

[jensej]

Hi 
Yes I can see the header. In the run from the Picture in my first post I iterate over 4 connections and to try if all the Paths in the job works I set the connection 1,2 with wrong hostname so that it would cause an connection error. The 3rd should be succesful and print the row that I'm having problem to insert and the 4th connection will create a reject message since the table for the insert is not in the database. 

It's correct that 0 rows passed for the last iteration becuase it went the reject path. For iteration number 3, 1 row goes to the tjavarow. 

The file is not being recreated and as I wrote I have exact the same settings for Excelouput that's red as for the green ones that work. Only difference in the settings is for the header component. 

I now changed the order of the connections to make the one that doesn't insert as the last connection so that you can see 1 row in flow.

I also create a csv in the same run to show you that it works as wished. 

So im expecting my csv and excel to have 6 rows. 
1 header
4 rows, 1 for each connection
1 last row (footer) that i add in the last subjob

In the picture you see that the last iteration return 1 rows that shows all values in the log. In the csv the row is there but in excel not... 

[Shicong_Hong]

Can you show the Java code on tJavaRow? Make sure you have passed the data from tJavaRow to tFileOutputExcel. eg:

output_row.columnName=input_row.columnName;

[jensej]

Of course. I added a picture of the schema + tjavarow code. It's funny that the output in log works as wished for both output and input but the insert in the excel not. I also added a picture with the settings of the exceloutput. It has exact the same settings as other exceloutput that works on other places inside the same job/subjob. I have no idea how to debug this more? Directly in the java sourcecode? 

[jensej]

Anyone that can help me further with this Issue? I want to use the Excel and CSV output as a form of receipt of what the query did and in this case the CSV will tell me the thruth about the insert but the since the Excel is missing one row even though inserts were made in the database this gives the user the wrong information. 

[Shicong_Hong]

Can't see any issues from the job design. For testing, keep only one correct connection in the source to see if the data are written into Excel file. 

[jensej]

Well iv'e tried that also and then I get just the Header + Footer row in the Excel without information about the insert. 

[Shicong_Hong]

Hi Jense 

I have tried to insert data into an existing Excel file and it works in TOS7.3.1. Can you simplify your job and provide an example that might be helpful for us to reproduce the issue. 

Thank you!

Shicong

[jensej]

So I tried to simplify my job and now I've found out what's causing the problem but I don't understand why. 

When I add the reject link to catch rejects then suddenly the excel insert won't work anymore even though 0 rows are rejected. 

See pictures