Tutorials & Examples Web-help Blog

Iterate through Sheet Names if data exist on the sheet then run a project


#1

I have customer data excel files that contain 4 different sheets.
Sheet 1 = Corporate Data
Sheet 2 = Region/Division - Optional
Sheet 3 = Customer Site Type - Optional
Sheet 4 = Customer Locations

Each sheet must be processed in order because of table dependencies.

For example Corporate Data is processed first, unique id’s are created for each line of the file. These Id’s must be added as an additional column on the other 3 sheets in order to load into the database.

If the sheet contains data I want to call a project that transforms the data and then loads to the database
If the sheet is empty I want to continue on to the next sheet and check if it has data and if so transforms the data and then loads to the database etc

How can I do this?


#2

When an empty sheet is loaded the result is just an empty table. It works well with the conditionally derived tables because you can choose to run actions in a conditionally derived table only when it’s not empty. When such table is empty all actions in it are skipped. Therefore you can put the “Call” action (for export to database) into that derived table.

Finally, use the “Synchronize” action to put loading sheets in order.

Here is an example that demonstrates how to load sheets only when they are not empty. The example loads two sheets from a spreadsheet. One sheet is called “Has data” and unsurprisingly it does have some data. The other sheet is called “Has no data” and it’s indeed empty.

After each sheet is loaded there is a conditionally derived table that is only executed when the sheet is not empty. The conditional derived tables export data into a file, then call do-something.morph which loads the data from the file and can be extended to export into a database table.

load-sheet-if-not-empty.morph (4.3 KB)
do-something.morph (1.1 KB)
Book1.xlsx (8.1 KB)


#3

Thank you, I will take a look at the example… Good thing to know the workflow if a derived table is empty it skips all the actions.

Is there any more documentation on how morph works as far as logic for the work flow.


#4

The best documentation for it is our tutorial: https://easymorph.com/learn.htm. It also covers workflow topics:

  • Parameters
  • Subroutines
  • Loops
  • Conditional workflows