From Excel to SQL DB

[PowerRon]

Hi,

the user is changing an Excel (on OneDrive for Business) on irregular moments.
When a change takes place, we have to read the Excel, process it in Azure Data Factory and update the SQLDB table that contains the data of the Excel.

What is the way to go?

1. Power Automate is triggered by the creation or update of the Excel
2. The Excel is transferred to Blob Storage
3. Azure Data factory is triggered by Power Automate because of the creation of the Excel in blob
4. Azure Data factory runs and updates the table?

Or is there another, maybe easier way? Is there somewhere an example?

regards

Ron

[IDonknowwhay]

Hi @PowerRon 

I am doing something similar,  Keen to know if there is a better way in doing it.  The only difference is that I'm going through a on-prem gateway.

Here is what I'm currently doing:

[PowerRon]

Hi @IDonknowwhay 

OK. So you 'list the rows in a table', then you truncate the target table and then insert the rows?
That's indeed a fast and straight way.
What does 'list the rows in a table' exactly do?

Are there people with additional solutions?

[IDonknowwhay]

Hi @PowerRon 

How I see it, List rows presented in a table, takes that data from the Excel table and place it into an array, which you can use later.  in my case in the "Apply to each" row from the Excel table list.

List rows present in a table