I’ve an Excel that has multiple worksheets. All of them have the same format. It is distributed to multiple parties for data input. Each party enters data in their related worksheets.
If there is a new party, I will add one more worksheet for him. Is there a way that EDF can detect this newly added worksheet automatically? Thanks!
In batch processing mode you can set a wildcard for worksheet names and then append them all to a single output file. Example 2 here shows how to do it for multiple CSV files:
Change from *.csv to Mysheet.xlsx[*] to do the same for sheets of a spreadsheet.
So that will automatically Stack all the sheets in a new file (perhaps single CSV). You will then have to process the new dataset in a second .transform.
Your other option is to drag the spreadsheet on to Easy Data Transform. Select only the new sheet and then drag a connection from the new sheet to an existing Stack transform.
It would be better if you could put a wildcard in the input file path (file name and/or sheet name) and it would stack all matching files/sheets for you automatically. That is on the wishlist.
Sound great! Let me try. Thanks a lot!
You can now use the new folder input feature to read all the sheets of 1 or more Excel files in a folder into a single Input item. See:
If you are using Easy Data Transform with Excel files that keep getting new worksheets added, the newer folder input feature is probably the cleanest solution. EDT can automatically grab matching Excel sheets and compile them into a single input using a worksheet wildcard so you don’t have to manually reconnect every new worksheet. This makes it much easier to maintain and scale workflows that involve adding parties over time with Excel worksheets.
By the way, if you need to know later on from which worksheet (or file) data is imported I recommend to add meta information (in each row), e.g. $(FileSheetName), …