Data360 Analyze

I am reading a file which contains month-wise data and updates the file based on some logic. If the excel contains the "current month" tab then I need to append new data to the existing data sheet in the current month tab and not touch other tabs.  If the current month is not present then I need to add new tab and add the data there. Upload the enhanced file on the same path. I have attached the sample file. Can someone please help with the dataflow how to do it.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

This might not be the most intuitive data flow but hopefully it more or less achieves what you need. What it does:

1. If CurrentMonth exists on the input Excel file, then we will read that sheet, append your new data to the end.

2. If CurrentMonth does not exist, we just create a dataset that has the new data on it.

3. We will make a copy of your input file to the new file name (I set it as a data flow parameter). 

4. If step 1 was true, we will overwrite the CurrentMonth sheet with the updated dataset on our newly created file.

5. If step 2 was true, we will create a new sheet named CurrentMonth to the end of the newly created Excel file, with all the other sheets remaining on the output file.

Attached files

Dynamic Excel - 23 Sept 2020.lna
File-NoCurrentMonth.xlsx
File.xlsx

You are awesome Gerry!!! Thank you so much for the quick support and the sample shared.

I will test it and share the update... Really Appreciate your efforts

It worked fine Gerry but for the output excel, it is just writing new data. The old worksheets present in the file are not getting reflected in the new file. Also, for the tab name in the new sheet generated, how can I make it dynamic based on the current year and month.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Hi Rubal, I don't follow when you say "The old worksheets present in the file are not getting reflected in the new file."

I just ran the data flow again against the files uploaded (File.xlsx and File-NoCurrentMonth.xlsx) in each case the final output maintained the existing sheets on the workbooks and either appended the new data that existed in the node "Some New Data To Append" to the end of the existing data (in the case of File.xslx) or just wrote out that data as new onto a sheet named CurrentMonth.

With regard to making the sheet name dynamic I don't think that is possible (or at least not easily) on the output as the name is taken from the pin name of the node and that cannot be tweaked dynamically unfortunately. The only way I can see to rename the sheet after writing out the file would be to have some custom code from Python or Java to do the rename. This is possible and it looks like there is plenty of information online about it but not something I have tried before unfortunately.