How to import data from Microsoft Excel into Microsoft SQL Server
Vložit
- čas přidán 27. 04. 2021
- In this video, we'll have a look at how to import data from Excel into SQL Server.
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: rebrand.ly/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/microsoft-powerpiv...
----
My Excel for PC courses are:
Beginner to Excel - rebrand.ly/microsoft-excel-sp...
Intermediate to Expert Advanced - rebrand.ly/microsoft-excel-ex...
Power Table, Get and Transform and Power Pivot - rebrand.ly/visualizing-data-e...
VBA macros for Excel - rebrand.ly/excel-vba-pc
----
In my previous video, I discussed how to import data from SQL Server to Excel. Now, it is the other way round.
In this video, I show you how to import csv and txt files into SQL Server, and Excel spreadsheets as well. - Věda a technologie
![How to EASILY Import data from EXCEL to SQL Server Tables [2024]](http://i.ytimg.com/vi/8EByG5t4UyE/mqdefault.jpg)
![How to EASILY Import data from EXCEL to SQL Server Tables [2024]](/img/tr.png)
If you can't use the SQL Server Native Client, then you should use the Microsoft OLE DB Driver. See czcams.com/video/SA4La3eLUPY/video.html for details.
If you want to import data from SQL Server into Excel, please have a look at our video: czcams.com/video/N-WiLT6JQes/video.html
You are such a life saver! Thank you! Really saved me time in figuring things out :)
I'm searching for simple method to import data wow what an explanation thank you very much
czcams.com/video/GWXlLrpEhR4/video.htmlsi=-O8OWKeS3jtY0OFH
This was very informative and awesome. My hours of aimlessly roaming around solved in minutes.
Yeah, same to me 😂
I spent like 3 hours trough C code trying to cast the types before importing
czcams.com/video/GWXlLrpEhR4/video.htmlsi=-O8OWKeS3jtY0OFH
@@gabrielbarros8543czcams.com/video/GWXlLrpEhR4/video.htmlsi=-O8OWKeS3jtY0OFH
If most of us can ever solve the OLEDB 12.0 Provider Registry issues, we might be able to use your import schemas.
real, have you solved it yet? please give the link of the solution, I'm stuck.
Great explanation, thank you!
Thank you very much brother. I have subscribed to your channel, coz of this video only.
this is so useful and detailed. Thank you for the video!
czcams.com/video/GWXlLrpEhR4/video.htmlsi=-O8OWKeS3jtY0OFH
Thank you for this video!
thank you so much your clear and understandable expression, i benefited from this video🤘
Thank you 🙏😊 so much for this ,,, really it solves my problem queries regarding importing data
Thanks bro! this was neat!
Super helpful! thanks
great video for learning
Import flat data option is available in ssms v 17.3 or later..can you show how to import in ssms
v10
Very neat explanation
wow thank you so much, this really helped
How to append more excel file to the same tables created
Listening to this gave me 1 Big Thumping Headache, what happened to all the dynamic sounds of 1970's Punk Rock, hope these guys have improved their musical skills, since 1980, as I remember New Wave - Punk Rock being such an electrifying experience, as I'm planning on attending PIL tour this year, hope they won't disappoint. Shake you're Balls up ! 😮
Yo! Thanks for your post! I also do hope that PIL won't disappoint too! Did they import data from Excel in SQL Server as well? Phillip
Thx bro finally i imported data
Very good video to understand
thank you very much for your great video dude
thank u so much..
so helpful
Спасибо. получилось. Раз 100 смотрел, пока не понял как делать. СПАСИБО!!!!!!!
тоже просмотрел что файл csv?))
That was helpfull. Thanks
Thanks the vedio was so helpful but facing few error
Hi, I followed the same steps for importing a excel file and it throws this error
Invalid object name 'dbo.Sheet1$'.
Nice Tutorial
Very useful video❤❤❤
Thank you. Very Informative.
Hi Chitt. You are welcome. Phillip
I'm getting a very different menu when I right right click on my database. it doesn't have a tasks button at all. :(
Thanksssssssssss!!!!!!!
U saved me
Thank you so much
Thanks for the great video
How can you continuously update/refresh the data initially imported from excel using the method described in your video?
Hi Ahmed. I would create an SSIS package to do this. Phillip
thank you, can you please make the next step in this video- where you import that data from this new table into existing tables
Hi Terrill. I would just use a MERGE or INSERT statement, now that everything is in the same SQL Server database. Phillip
Thank u very much sir
Thank you.
Thank you sir
As a newbie your resources are a gold mine Mr. Phillip, you are a hero! Is it possible to connect to multiple excel files or from a working folder and be able to append all in 1 database inside sql? Thank you.
Hi Juanni. Thank you for your question. I would combine all of the Excel files together using Power Query, which you can do inside Excel, first. Phillip
Thanks, Philip useful as always,can you show us how to stop the query if running too long. For example, a query that has 1k lines run sometimes too long like 10 min and I want to stop this query before reaches 6 min
Hi. I assume you are referring to importing data. They should be a Cancel button which will stop the import from continuing. Phillip
Thanks 🙏
Hello Phillip, do you have a video on how to connect and import data from Google analytics to SQL server 2022? Thanks in advance.
Hi Emmanuel. No, I don't - but please see hevodata.com/learn/google-analytics-to-sql-server/ . Phillip
czcams.com/video/GWXlLrpEhR4/video.htmlsi=-O8OWKeS3jtY0OFH
Thank you
pretty cool way
amazing video! Question, what do we do if we want to create seperate tables for the import? Let's say you have one large csv file but you wish to break it up into more tables, how would you do that?
Hi Labiribiri. I would break up the csv file into separate files, or I would load all of the data into SQL Server, and then separate it. Phillip
@@SQLServer101 thank you for responding. but, how do I separate the data after loading it into a SQL server?
thank you
Thank you very much. I need to do the same but using python. Probably with SqlAlchemy, do you know how to do? thanks!
No - I don't use Python. I'm glad you enjoyed the video. Phillip
Hi this video is very helpful, but how would you upload live data from excel to SQL sever?
Hi Jiten. I don't think that's possible. You could have an SSIS job which would upload any changed data every so often. Phillip
@@SQLServer101 Hey, thanks for this video, was quite helpful. Do you have any video on creating the SSIS job you mentioned for uploading/appending live excel data?
Does using the flat file method avoid the 32 bit vs 64 bit issue with the microsoft ole db driver
Hi Tvryoda. Thank you for your question. The only way to find out in your circumstances is to try it on your machine. Phillip
Thanks for this video. Question: it's possible to run it automatically? Do some process that overnight itself grab the data from the Excel file?
Hi. You can, using SSIS or Power Automate. Phillip
@@SQLServer101 Automate needs premium licenses...so, no. SSIS. Si you know how? Is there a video that explains it? Thanks.
Thank you so much! for this tutorial! i gave you Like and Subscribe.
Is there a way to import from sharepoint? Or excel online
me sirvió mucho. Yo va de buscar como importar el archivo excel y es mejor el csv
Can I link the SQL to a specific file location, so that whenever I add a new CSV in folder it automatically gets and appends the data?
Hi Lukatcheishvili. Thank you for your question. You would need an automatic process for that, such as Power Automate, together with a gateway if the files are on your local machine. Phillip
i am looking for a vba code performing the same procedure of flat file import in SQLExpress
Looking forward to your kind advice, its urgent please.
Hi Amjad. Please have a look at social.msdn.microsoft.com/Forums/en-US/afb454b2-e8d0-4997-95c6-45478425d8da/how-to-directly-insert-csv-data-into-sql-server?forum=vblanguage . Phillip
Is there a way to link these excel tables, like you can in MSaccess? every time you update excel sqlserver table would update as well.. (?)
Hi Nyali. Thank you for your question. Unfortunately not. You could use an SSIS package or a Power Automate or Logic Apps flow to periodically update the table - but there’s no way to actually link them together. Phillip
Hi, I would like to import ~8 Million rows. through 8 different Excel/CSV Files. Will this method work?
Hi Inam. As long as the table is set up correctly, that should work. Phillip
To get rid of underline, what do you press, ctrl, shift and ?????
Hello. You press Ctrl-Shift-R to update the cache. Phillip
So tell me, if I make changes to the excel file that I have exported to SQL will the data on my SQL be updated when refresh.
Hello Abel. Thank you for your question. No - this is a one-time import. If you want to do this more frequently and automatically, then you will need to use something like SSIS. Phillip
how to refresh the data in the table when excel file change?
Hi Tebby. That is a very complicated question. At a simple level, you could delete the imported information, then reimport it again. At a more complicated level, you can use SSIS with Slowly Changing Dimensions (SCD) - see docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/slowly-changing-dimension-transformation?view=sql-server-ver15 for more information. Phillip
Hi, thank you for this brilliant video. However, I installed the ssms and after creating a new database, each time I try to import data into the database, the import and export wizard doesn’t pop up. I’ve tried uninstalling and reinstalling to no avail. Can you help out on what could be wrong? Thank you.
Hi Aymoi. Thank you for your question. It should come up - you might want to look at my more recent video, which I recorded just a couple of months ago - czcams.com/video/iGzBgd0qwT4/video.html . Phillip
czcams.com/video/GWXlLrpEhR4/video.htmlsi=-O8OWKeS3jtY0OFH
What if there are some wrapped text columns?
Hi Selsoft. If this in Excel, then wrapped text columns don't matter when you try to import it into SQL Server. You might find it more difficult if your data is in CSV or TXT format. Phillip
How make relationship between tables when importing from excel workbook..
Hi Anil. Thank you for your question. I would make the relationship AFTER the tables have been imported into SQL Server - otherwise, the input might fail. Phillip
@@SQLServer101
Fine.. Is there any video for that..
In diagram mode I find the answer to assign primary & foreign key
If any other way plz suggest
The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new application development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server or the latest Microsoft ODBC Driver for SQL Server going forward. For SQLNCLI that ships as a component of SQL Server Database engine (versions 2012 through 2019), see this Support Lifecycle exception.
Hi. You are correct - I deal with this in czcams.com/video/SA4La3eLUPY/video.html . Phillip
Hi....my pc has 2021 version of excel,when I try to import data from excel workbook to sql server by selecting excel version to 2016 which is the highest version available .it gave me an error -"The operation could not be completed,the microsoft ACE.OLEDB.16.0 provider is not registered with the local machine".How can get rid of this error.Please help me out from this
Hello. Thank you for your question. Please see answers.microsoft.com/en-us/msoffice/forum/all/the-microsoftaceoledb160-provider-is-not/45dd60f3-69f5-4e9c-ba8d-2b2bcc4bc78c . Phillip
i have an issue with Excel import to SQL in ssis i am getting error and i download 32bit also but no use if done mine can please help me that
Hi Kunchapusrikanth. What error are you getting? Phillip
i do not have the task option in my list even when pressing on the database itself what should I do ?
Hi DCh. Thank you for your question. Can you please elaborate. Phillip
It was not showing server native 11.0 in destination what should I do ??
Hi - please use the updated Microsoft OLE DB Driver for SQL Server. I'll create a video on it - it will be released on Thursday. Phillip
I have followed your each step in downloading SQL server 2019 and also have followed for import data but again it gives me an error of culture.
Hi Tayya. Make sure you are importing your data into a blank table. If necessary, you may need the sp_configure stored procedure. Phillip
I tried to import small file data and it went successfully, but when I tried large file csv (590 MB) with 5 millions rows but it cannot work with your method. any suggestions dude?
Hi Riskhi. It should be able to work. Make sure you are importing into a table that is created as part of the process. Also check the data types and that they are compatible with all of your data. Phillip
How to import into existing table and update it
Thank you for your message. This video should help with that. Phillip
@@SQLServer101 👍
you can import excel files...
How to add foriegn key for imported data of 2 tables😮😮😮
Hi Deepak. That's a good question. I cover foreign keys in my two courses, Querying Microsoft SQL Server with T-SQL and Database Fundamentals: IT Specialist ITS-201 certification - see idodata.com/sql/ for details . Phillip
There are no tasks for me why could that be? So there is no such option as import flat file only things like shrink, back up, restore...
Hi Valentin. Thank you for your question. I'm afraid I don't know. I would upgrade to a later version of SSMS. Phillip
@@SQLServer101 I figured it out later thankfully.
Im unable to see the flat or txt files, the are just gone
Hello- thank you for your question. You need to change the "type of files" dropdown from "Microsoft Excel" to "All types of files". Phillip
If it's showing error what can be done???
Hello. It depends on what error you are seeing. I would import into a fresh table and see if that shows the errors. Phillip
When I try to import, it says "Class not registered" what does that mean
Hi Donald. Please have a look at www.sqlshack.com/import-data-excel-file-sql-server-database/ . Phillip
Why do you choose to import your data as its own table instead of into an existing table?
Why don't you specify your desired datatypes on import; why do you choose to alter the column datatype afterwards?
Hi Derrick. Thank you for your question. Often there will be difficulties importing into an existing table, usually around the data type. Additionally, you may need to be manipulated before it can be in its final place - maybe it needs to be split into subtables. Phillip
Why it gives me an error when import it
Change Data Types if Asked. I got same Error.
I cannot link excel with SQL. What should i do plz help
Hi Mahmoudadas. Thank you for your question. I'm afraid that without more details, I Can't help. Phillip
Doesn't look like mine
Hi Jm. It should do - I don’t think they have changed the interface for quite a while. Maybe try it again. Phillip
Apparently, you can't import a dirty CSV file, it has to be cleaned
TITLE: SQL Server Import and Export Wizard
------------------------------
The operation could not be completed.
------------------------------
ADDITIONAL INFORMATION:
The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)
------------------------------
BUTTONS:
OK
------------------------------
Hi Pew. You may find that this helps: www.microsoft.com/en-us/download/details.aspx?id=54920 . Phillip
@@SQLServer101 I have downloaded it, but I still get the same error
n1
Intelligent was not updating after pressing ctrl,shift,r
Hi Yuvama. Intellisense should automatically update after pressing those buttons. Make sure that you are in the database you think you are in. Phillip
TITLE: Microsoft SQL Server Management Studio
------------------------------
Error inserting data into table. (Microsoft.SqlServer.Import.Wizard)
------------------------------
ADDITIONAL INFORMATION:
Error inserting data into table. (Microsoft.SqlServer.Prose.Import.SDS)
------------------------------
The process cannot access the file 'C:\Users\verma\Downloads\archive (1)\Credit card transactions - India - Simple.csv' because it is being used by another process. (mscorlib)
------------------------------
BUTTONS:
OK
------------------------------
Hi Vaib. Thank you for your question. Have you got the csv file open (for example, in Notepad)? Phillip
thank you sir
Thanks 🙏