Step-by-Step Data Migration: Excel to SQL Server with Power Automate Flow

In this comprehensive tutorial, we'll walk you through the process of migrating data seamlessly from OneDrive Excel to a SQL Server database using Power Automate Flow.
Data migration is a critical task for many organizations, and automation can significantly simplify the process.
In this video, you'll learn:
- Setting up a connection between OneDrive and SQL Server.
- Creating a Power Automate Flow to automate the data transfer.
- Mapping data fields from Excel to SQL Server.
- Testing and monitoring your data migration process.
- What is on-Premise Gateway
- How to Install and setup On-Premise Gateway
Whether you're a beginner looking to streamline your data management tasks or an experienced user seeking to optimize your workflows, this tutorial will provide you with the knowledge and skills you need to accomplish this important task efficiently.
Don't forget to like, subscribe, and click the notification bell for more tutorials and tips on data management and automation. If you have any questions or need further assistance, feel free to ask in the comments section below.
SQL Code
IF DB_ID('PA_Test') IS NULL
BEGIN
Create Database PA_Test
END
ELSE
BEGIN
PRINT 'Database Exists'
END
GO
USE PA_Test
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'Emp')
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
Create table Emp (
EmployeeID Int,
FirstName varchar(255),
LastName varchar(255),
Email varchar(255),
Phone varchar(255),
HireDate DateTime,
Department varchar(255)
)
END
GO
Select * from Emp
#DataMigration #PowerAutomate #ExcelToSQLServer #Tutorial #Automation

[@vishnupp5944]

pls do more videos. thanks for the video

[@pthapa55]

This is what I was looking for!!!. One question though, will this process work if my file is in SharePoint folder and the file extension is .csv?
Thank you.

[@goverdhanjayaram3683]

Awesome ! Thank you very much!

[@lastminutecoders]

Most welcome ✨

[@dominicatuahene7303]

This was amazing sir, its something that I have been searching for while and have not found any good video on it until now. this was so simple and very explanatory . Please do you have any video on how to extract it from a CRM software into a SQL server using power automate? and also Vinay do you have any class you teach that I can join? Thank you

[@lastminutecoders]

Thank you so much for your inspiring words✨
If you are looking for personal training you can contact me at [e-mail]

[@mccoyji]

Very nice!

[@lastminutecoders]

Thanks✅✨

[@jrohlman78]

Very helpful video! What would be the next steps (or do you have any videos) on updating rows that change in the excel table and those rows in the SQL data table? The flow would need to insert and update.

[@Pbr318]

the new data entered to excel sheet is reflected in database how to automate this ??

[@nipunsrirangam]

Great video Vinay :)

[@lastminutecoders]

Thanks Nipun 💫

[@SnehaMuthyala-i5b]

How to do the automatic data transfer form excel to sql sever by interfaces like python or powershell

[@user-sb2gy3zs8o]

Im seeing that the SQL connectors on power Automate are premium connectors, means we have to get paid accounts for this?

[@marccucala7606]

Would anyone know how to do this so that if existing data is added it doesn't do anything, but if that data doesn't exist in the sql, it adds it... I'm making a condition but it's going crazy
thanks!

[@saifkazi8504]

If i have to trigger this flow through powerautomate than what do i have to do

[@majapamajapa9548]

Hi LMC, I have a great confusion on converting data into transpose and update to SharePoint list....
Like I have rawdata in CSV which has columns of Name, Subject1, Subject 2, Subject3. and I need to update in SharePoint list which has 3 columns Name, Student and Marks...now I want to update the CSV data by getting each rows of student marks and thier subject in a column so transposed......is there any ways we can make it ... I have been trying this for so long but no idea I am getting.

[@lastminutecoders]

Hi @majapamajapa9548, yeah we can transpose the data and then update/ patch it to SharePoint list.
Can you mail your issue at [e-mail] with screenshot. I need to review what exactly you are trying to do.
Thanks, Vinay

[@pipepi4888]

I love you

[@lastminutecoders]

Show me by doing 'Super Thanks' 😁❤️

[@indranilpaul8750]

what was the error that came after you solved the date format error

[@shubhabratadey]

2 questions:
1. What if the excel file that we intend to import has huge rows i.e. 3K - 200K? Do we still use this process? It might end up taking a considerable amount of time to import.
2. How can we import the file name as well so as to distinguish data imported in between dates or times or may be different times in the same day? I use Power Automate to save excel files with the date and time of getting them. Is there any way that I can import the name as well?
This video is great. Can you post similar videos wherein we can use PA to delete, modify in SQL database. Sorry, I am pretty late in your channel, hence asking these silly questions.

[@lastminutecoders]

Point 1: Yes definitely it will take a lot of time so remember if it is one time process or a process which you want to run monthly or weekly go with this approach.
Point 2: File Name can be easily extracted from file (For this you can contact me at [e-mail])
Thankyou, Sure in future I will cover more videos on these topics.
No Questions are Silly😁

[@anubhavaman6067]

Hi your video was helpful.
I have a question .What to do in case the table column have not null uniqueidentifier constraint defined on them.

[@lastminutecoders]

What's the issue with not null unique identifier constraint?

[@anubhavaman6067]

@@lastminutecoders so there is a column named id in the table. Id column has been declared as an uniqueidentifier, so normally when you try to insert data into this table usinng insert command, this column is automatically populated. We don't have to populate. Now when we using the flow, since this column is marked as nkt null, in the sql step we need to enter the record for id column in for each loop. What can be done to get thisfield automatically populated.

[@Shashajhon]

Hi Vinay I have data of 10 lakh even though after splitting it by region some regions have data more then 1 lakh can we load more then 1 lakh data from Excel to SQL server using cloud flow ( list rows present in a table limit is 1 lakh) is there any way to increase the pagination limit in list rows present in table ?

[@lastminutecoders]

Have you tried looping? Even I have never tested this big of a scenario with Power Automate. But If we use loop and keep Get items action inside Loop, (Let's say you have 2.5 Lakh records so loop will run 3 times - First 1lakh items, then next set of 1 lakh items and finally 50k items.
You will need to use filtering based on ID or Index column.
If you are able to resolve this issue with this approach do let me know. I will also do some research on this.