topic Re: Excel With dynamic Sheets , output as dynamic csvs in Talend Studio

Excel With dynamic Sheets , output as dynamic csvs

Anonymous — Sat, 16 Nov 2024 08:54:13 GMT

@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)

Re: Excel With dynamic Sheets , output as dynamic csvs

TRF — Wed, 03 Jan 2018 11:25:42 GMT

Hi,

You'll probably find the desired tools in the tFileExcel-Components suite proposed for free by @lli 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%252F1%252Fproducts%252F542&pi=marketplace%252F1%252Fproducts%252F542%252Fitems%252F2739

Re: Excel With dynamic Sheets , output as dynamic csvs

Anonymous — Wed, 03 Jan 2018 13:37:24 GMT

@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.

Re: Excel With dynamic Sheets , output as dynamic csvs

TRF — Wed, 03 Jan 2018 14:31:29 GMT

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://community.talend.com/t5/Design-and-Development/resolved-Dynamic-filename-in-tFileOutputDelimited-component/td-p/113409 (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-a-column-from-an-sql-inpu (same approach as Shong)

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

...and so on

Re: Excel With dynamic Sheets , output as dynamic csvs

Anonymous — Wed, 24 Jun 2020 15:19:22 GMT

Hi,

I have a similar requirement, I suppose. For instance, I want to load 10 database tables iteratively into a job which has to create the corresponding number of .csv files without any transformations. So, I need those 10 tables each to be converted to different .csv files. I have real time studio. Kindly help me with this.