FileNotFoundException when using tOutputDelimited after a tFixedFlowInput

[Deewens]

Hello,

I want to get data from a db and then store them into an xlsx file with one file per "COD_CMP" as described in my tFlowIterate component.

First problem : storing data into xls or xlsx file take realy too long. I got almost 138 000 rows from my DB and it'll take between 9 and 10 hour to store all data into an xls or xlsx file. Because the storing is doing one by one or idk why.

So, I tried to store it into a csv file which is much faster. And then after I want to re-store everyting into an xls (which will be quick because it won't be line by line). That's the only way I found to have enough performance for my job.

But, I got a problem as described in screenshots. When I'm store data into the csv file, at some point, the job stop and I got an Exception FileNotFoundException which tell me that my file is already used by another process. But it's not. I searched all the day to solve that, but I can't find any solution.

So, i'm looking for a solution for my second problem, or by miracle, my first if you have a best solution.

Thanks by advance.

(sorry for english)

[Anonymous]

Hi
Do you create one new file for each line? If so, simply check the 'Split output in several files' box in the advanced settings of tFileOutputDelimited, and set the Rows in each output file as 1, you don't need to use tFlowToIterate to iterate each row.

Regards
Shong

[Deewens]

Hello thanks for answering,

In fact, I want one file per "COD_CMP", not one file per row. (For example : COD_CMP = ICI, So I want all row with COD_CMP ICI in one file, then, for another COD_CMP, I want the same but in another file), All same COD_CMP from my query need to be stored in the same file.

I found that doc : https://makina-corpus.com/blog/metier/2014/tutorial-talend-decomposer-le-contenu-dun-fichier-flux-ve...
And I used the solution 3 for solved my problem. All is working because the file stay open while the process isn't done. Unlike my old solution which open the file for one row, store that row, close the file, and, re-open for next row, etc... That why I had a problem.

But I'm storing in a .csv, and I want if possible a solution to store in a xls file. I found Apache POI for that, but I don't know how to use it in Talend, I can't import the nedded package for using it in tJavaFlex.

[akumar2301]

If you other solution from your link , you could crease xls file using tfileoutputexcel

For tjavaflex , you need to manage your own lib and routine/java code.

[Deewens]

I know, but when I'm using tfileoutputexcel, the data transfer take too
long (almost 10 hour) because the file is always open, transfer, close,
open, transfer, close, etc.

That's why I'm trying to use a tJavaFlex to close file only when job is
finished. But I have trouble to make it work

[akumar2301]

I got some code on internet For create xla file

https://www.tutorialspoint.com/javaexamples/write_data_into_excel_sheet.htm

You need to add tLibrary ( at the start of job) and load apache poi jars to make it work.

[Deewens]

Thanks, I'm trying to make it works..

[Deewens · Accepted solution]

So, I tried the solution with Apache POI, but, it's take also too long... I can't append to the xlsx file without read the file, so each time the file size is up, the file reader take more time to read it

I abandoned this solution, and I do the following :

- store all data in .csv file (because I can append directly with Java Class FileWriter, without need to read the file each row) so it's realy quick

- then, store all this .csv into a tFileOutputExcel (realy quick also, because it's not line by line)

That's not the best, but that's the only solution I found.

Thanks for your time