Automatic migration of excel files into Azure sql database

Question

I am writing a c# script that calls the third-party website by API and in return, I am getting excel files. I want to execute this script using azure functions at some schedule, which means that those excel files will be replaced every time when the scheduled function is executed by the new ones, but with the same names. I need to update the Azure SQL database with the data from those files automatically so that the data in the database is always congruent with the files generated by the C# script.
Where should I save the excel files and what method should I use to update the Azure SQL database, to make the workflow efficient and easy to implement?

Answer 1

Hi @Asaldin-2272 , welcome to Microsoft Q&A forum.

You can first use the Azure Function to save excel in Azure Blob Storage in CSV format. Please refer to below thread on stack.

Use Azure Functions to automate saving Excel to CSV

Then we can use ADF to load this CSV data into Azure SQL Database. Before you load latest data, you can truncate the tables in ADF itself and then do a fresh load. You can schedule the ADF as well to automate this entire process.

Populate Azure SQL Database from Azure Blob Storage using Azure Data Factory

Please let me know if this helps.

----------

If answer helps, please mark it 'Accept Answer'