About EasyMorph Tutorials & Examples Web-help

Use excel cell as a query to database


#1

Hello Team,

I am looking for a solution in easymorph to execute a query which is stored in an excel cell. So we run a macro and generate an ‘Insert query’ in a cell in excel file. This macro is run every 2 hours. Is there a way to access this cell in excel through easymorph and run the insert query?

Regards
Vaibhav


#2

Hi @vaibhavtandon87,

You can do that by passing your SQL queries as parameters to a subproject with “Iterate” action. Subproject should execute those queries with “Database command” action in “Custom command” mode or with “Import from database” action in “Custom SQL” mode.

Here is an example project:


Run SQL from Excel.zip (38.3 KB)

The main project file is called “Run SQL from Excel.morph” and the subproject file is called “Run SQL.morph”.

“Import SQL queries” and “Run SQL queries with a subproject” tables is your actual workflow.

“Create target database table” creates a table, used by this example and the other two tables just display “before” and “after” state of the target table.

Have in mind that connector to the target database should either be copy-pasted to the subproject or subproject should use a connector from a shared connectors repository.


#3

Hello Andrew,

Thanks for the quick response. I am testing the same and come back to you. On the first look of it, it seems for the solution we need to have a query column?

In my case the query is in cell B38 which gets updated every 2 hours post the macro runs.

Regards
Vaibhav


#4

Hello Vaibhav,

If you only need to execute one query at a time, you just have to make your dataset to only have one row.

In order to achieve that:

  • In “Import Excel spreadsheet” action:
    • Check “Columns don’t have headers” checkbox
    • Select “Column 2” in “Load only selected columns” list
    • Set “Skip first lines” to 37
  • Add “Trim table” action with “Keep only”, “Top” and “1 rows” settings
  • Change column name in the “Iterate” action to “Column 2”

Here is an updated example: Run SQL from Excel 2.zip (38.6 KB)