Five Stars

Excel With dynamic Sheets , output as dynamic csvs

@TRF @Victor Tagging you as this is a relative post previous. 

 

Hi All, I have an excel source with multiple sheets, all different schemas (number of columns vary). I need to create as many output CSVs same as the number of sheets within excel file. Ongoing basis the number of sheets may vary. I need to handle this dynamically, i.e. everytime when the new Excel is provided, ETL creates the same number of different CSV output files depends on the sheets.

 

So far I am able to create a single consolidated output with all sheets data in it, and each row having a sheet name as a new column ("sheetname") to identify which sheet row is coming from.  I can just put filters to route the data to different CSVs to different CSV output files, however, that will not be dynamic. 

 

Is there a way to do this dynamically? Not necessary to have output file names same as sheet names (can be 1.csv, 2.csv etc) 

3 REPLIES
Forteen Stars TRF
Forteen Stars

Re: Excel With dynamic Sheets , output as dynamic csvs

Hi,

You'll probably find the desired tools in the tFileExcel-Components suite proposed for free by @jlolling on Talend Market Place.

I never tried but seems very interesting.

Here is the link:

 

https://exchange.talend.com/#marketplaceproductoverview:marketplace=marketplace%252F1&p=marketplace%...


TRF
Five Stars

Re: Excel With dynamic Sheets , output as dynamic csvs

@TRF - I am new to Talend and don't know how to configure/use those components. Moreover, the component descriptions don't point to the desired state. For now, i have created a static job which creates multiple csvs using a filter in tmap and routing data based on the filter to different csvs. However, i need a dynamic solution. 

 

In Below job example first flow does that, i will create multiple output flows and then finally pass it to the second flow. 

 

Please revert if you have any solution to handle dynamic number of sheets in single excel, creating multiple csv per sheet. 

 

 Capture.PNG

Forteen Stars TRF
Forteen Stars

Re: Excel With dynamic Sheets , output as dynamic csvs

Unfortunately there is no solution ready to use from out the box for this purpose (well, I don't know one).

However, this is a common usecase and some guys have covered it in the past.

You may be inspired by these topics:

from @shong https://www.talendforge.org/forum/viewtopic.php?id=9649 (simple, but not the more efficient as soon as there is a lot of records to manage IMHO)

from Stackoverflow https://stackoverflow.com/questions/25684433/how-to-create-multiple-output-files-in-talend-based-on-... (same approach as Shong)

from @rhall_2_0 https://www.rilhia.com/tutorials/load-data-dynamic-number-files (nice solution, technically harder)

...and so on


TRF