Azure Automation / Sharepoint / Excel

Hi All,

I have a task that requires me to periodically extract data from an excel file which stored on a sharepoint online site, and import it into a SQL Azure database.

I was thinking of doing this via Azure Automation / Powershell for maximum control.

I cannot find the exact answer online during my research, so hopefully someone has tackled a similar issue?

Can Powershell within Azure Automation use the Excel COM objects? My PoC code for accessing the Excel sheet functions on-premise, however it does use this Excel COM object to read the data.

If not, any other ideas on how best to tackle this issue? (i understand this maybe slightly outside of powershell q+a’s…so apologies!)

TIA

https://www.powershellgallery.com/packages/ImportExcel/5.4.3

Damn! I was stuck thinking natively… I didnt think about the PS gallery. And I note they have a “deploy to azure automation”… which is nice.

Sometimes the best answers are the simplest.

Thanks Sam!