Flow copy excel data(Placed in Sharepoint) to SQL

[amyy]

my flow details are,
1)I upload an excel file in SharePoint 
2)after uploading file, flow reads all the rows of excel 
3)then update the same data in azure SQL.

The excel file contains multiple rows and columns (tickets data), and every week I get an updated excel with new data added.
My approach needs a solution like, In the SharePoint folder whenever I upload excel file,azure SQL database will also get updated.
I am very new to flow, please suggest how to achieve this.

[Rahber · Accepted solution]

Hello,

if i understand correctly you need a flow that will read the excel file from Sharepoint and update that in SQL.

Start with a automated flow that will trigger when a new file is created in Excel. First i check if its an excel file. If its an excel file i will get all its rows and using the condiiton i will go update my SQL server.

Here is what the flow looks like.

Please click Accept as Solution if it resolved your problem or give it a Thumbs Up if it helped you in anyway this will allow other people to search correct solutions effectively.

Thanks,

Rahber

[anupam8555]

Hello @amyy ,

There is no direct way to create records to Azure SQL from Excel. You need to use multiple connectors to achieve this. 1) Microsoft excel 2) SQL Server connector

Here is some logic you build to achieve your requirements : 

1) Read Excel file using "List rows present in a table" action 

2) Iterate through each rows of Excel 

3) Create records in SQL server using Insert Row action.

Please refer below articles in order to build your logic. This is not matching exact requirements but you can get some parts from these articles and use it. 

https://piyushksingh.com/2020/04/13/power-automate-read-excel-file/

https://www.briteglobal.com/blogs/community/integrate-cds-sql-onprem-data-gateway/

Thanks

Anupam

(If it helps you , please mark it as resolved.)

[amyy]

My data has more then 5000 rows and around 20 column ,so will it process normally or it will take some batch approach ? how to do in this case?

[Rahber]

If you enable Pagination in your "Excel Online" connector and increase the Limit it will fetch the all the records as batches and will process.

You can read about other limitations here: https://docs.microsoft.com/en-us/connectors/excelonline/

Please click Accept as Solution if it resolved your problem or give it a Thumbs Up if it helped you in anyway this will allow other people to search correct solutions effectively.

Thanks,

Rahber

[amyy]

Hello @Rahber 
after 3rd step, Apply to each I selected Value,
then next in  Insert Rows ,I connected to my DB server,selected DB, then selecting  table name
,all the columns name appears but I am not getting listed value for them from my excel.

kindly suggest what I am missing?

@anupam8555 

[Rahber]

Have you used the List rows present in a table action in your flow?

[anupam8555]

Hi @amyy ,

You can use following function expression to get value from Excel row inside Apply to each : 

items('Apply_to_each_2')['-----ColumnName']

Let me know if this works for you.

Thanks

[amyy]

@anupam8555 
it's working,thanks a ton.
But again I got stuck moving column having Date. i am attaching the screenshot of error 

[anupam8555]

Hi @amyy ,

Its data conversion error. Can you check what is data type of field where you are setting up "43858" ? You need to set DateTime value in that field instead you are setting up string value . If possible ,please send expanded screenshot of Apply to each . 

Thanks

[amyy]

@anupam8555 Here is acreenshot

and my data in excel is in DD-MM-YY format

when I directly import this data into SQL, it goes without any probelm.

[Rahber]

Its because when you fetch date from Excel Online Business it will come as number of days .

Use this formula to get the actual date

addDays('1899-12-30',int(YOUR DATE VARIABLE),'yyyy-MM-dd')

[amyy]

@Rahber  where to add this formula ,i mean where in flow? this column value in not been updated in SQL.

[anupam8555]

Hi @amyy , 

You may be using Online excel (Business) and when you use online excel it will return you dates in plain numbers. You have to make it into DateTime format. This applies to all dates field you are using in Excel (columns). When you assign a value to DateTime field of SQL Column , you have to convert it before you assign. So in your case : 

SQL Column                      VALUE 

Project Requested Date = Function addDays('1899-12-30',int(items('Apply_to_each')?['{YOUR EXCEL COLUMN Name}']))

Try above and let me know. 

Thanks

[amyy]

@Rahber @anupam8555 
Using this flow,everytime it runs,it just add new data to SQL along with old data.
Is there a solution possible like when flow runs it just override old data and I can see data from the latest run in SQL.

(i have incremental data,so every month few rows are added to data.)

kindly suggest

[amyy]

it's throwing this error