loading...

Read Excel file from SharePoint Online into Azure SQL database

sven5 profile image Sven Glöckner ・1 min read

We're already syncing files from on-premises network into our SPO (SharePoint Online) using OneDrive for Business.
For a fast mock-up we decided to automatically read an Excel file into our Web App's Azure SQL database.
I've been using Azure Data Factory for this task and I've been really amazed how mature this tool is.

Azure Data Factory

I've started with this tutorial, it was really helpful.

In the task CopyDataFromSPO I've decided to store the Excel file in my already existing blob storage (sink).

Alt Text

Afterwards, I created a data flow named SaveExcelFileToDb where I read the Excel file from my blob and save it to my Azure SQL database. I always want to recreate the table with new data. This way I can easily workaround merge conflicts. However, this is a really simple solution for only demonstration purposes. In a real production environment there should be some additional and more fault-tolerant steps necessary.

Discussion

pic
Editor guide