How to import data from Microsoft Excel into Microsoft SQL Server

Рет қаралды 282,937

3 жыл бұрын

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.

Пікірлер: 145

@SQLServer101 Жыл бұрын

If you can't use the SQL Server Native Client, then you should use the Microsoft OLE DB Driver. See kzfaq.info/get/bejne/iadkf8RkyrK5gI0.html for details. If you want to import data from SQL Server into Excel, please have a look at our video: kzfaq.info/get/bejne/hJOHnK-Fm7C1lac.html

@sunilraja6149 Жыл бұрын

I'm searching for simple method to import data wow what an explanation thank you very much

@amittechnicalagra 10 ай бұрын

kzfaq.info/get/bejne/fb2In6-j1avMgmg.htmlsi=-O8OWKeS3jtY0OFH

@willitivity Жыл бұрын

This was very informative and awesome. My hours of aimlessly roaming around solved in minutes.

@gabrielbarros8543 10 ай бұрын

Yeah, same to me 😂 I spent like 3 hours trough C code trying to cast the types before importing

@amittechnicalagra 10 ай бұрын

kzfaq.info/get/bejne/fb2In6-j1avMgmg.htmlsi=-O8OWKeS3jtY0OFH

@amittechnicalagra 10 ай бұрын

@@gabrielbarros8543kzfaq.info/get/bejne/fb2In6-j1avMgmg.htmlsi=-O8OWKeS3jtY0OFH

@skshahbazali6238 2 жыл бұрын

Thank you very much brother. I have subscribed to your channel, coz of this video only.

@OscarBalwin Ай бұрын

You are such a life saver! Thank you! Really saved me time in figuring things out :)

@dongnguyen-id3pe Жыл бұрын

this is so useful and detailed. Thank you for the video!

@amittechnicalagra 10 ай бұрын

kzfaq.info/get/bejne/fb2In6-j1avMgmg.htmlsi=-O8OWKeS3jtY0OFH

@vasQpr2 10 ай бұрын

Great explanation, thank you!

@greetika-mb4rq 9 күн бұрын

Thank you 🙏😊 so much for this ,,, really it solves my problem queries regarding importing data

@isaiahparker6170 2 жыл бұрын

Thank you for this video!

@rahulramesh100 Жыл бұрын

Thanks bro! this was neat!

@mevkaptan5187 Ай бұрын

thank you so much your clear and understandable expression, i benefited from this video🤘

@vlasov786 2 жыл бұрын

Спасибо. получилось. Раз 100 смотрел, пока не понял как делать. СПАСИБО!!!!!!!

@alexsklemin3708 Жыл бұрын

тоже просмотрел что файл csv?))

@varunakarniya 9 ай бұрын

wow thank you so much, this really helped

@adegbitetemitope9342 Ай бұрын

Super helpful! thanks

@arkanish0657 2 жыл бұрын

great video for learning

@jrdesaria Жыл бұрын

Very neat explanation

@JeffersonMartinSynfluent 5 ай бұрын

If most of us can ever solve the OLEDB 12.0 Provider Registry issues, we might be able to use your import schemas.

@pawanT23 6 күн бұрын

real, have you solved it yet? please give the link of the solution, I'm stuck.

@riskhibayusarwosaputro2034 Жыл бұрын

thank you very much for your great video dude

@chittchirag4326 10 ай бұрын

Thank you. Very Informative.

@SQLServer101 10 ай бұрын

Hi Chitt. You are welcome. Phillip

@khonrak 11 ай бұрын

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 ! 😮

@SQLServer101 11 ай бұрын

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

@rabiagcr1421 3 ай бұрын

Thx bro finally i imported data

@SoniaMohibi Жыл бұрын

thank u so much.. so helpful

@ShashirajPS 9 ай бұрын

Very good video to understand

@souravkumar3452 7 ай бұрын

Very useful video❤❤❤

@hasanyarar9020 5 ай бұрын

That was helpfull. Thanks

@kingsfordgokah6240 2 жыл бұрын

Thank you so much

@rkvisiontv7728 3 жыл бұрын

Thank you sir

@harshshah9006 Жыл бұрын

Thanksssssssssss!!!!!!! U saved me

@zoomcam7706 Жыл бұрын

Nice Tutorial

@sangeetharamtenki992 3 жыл бұрын

Import flat data option is available in ssms v 17.3 or later..can you show how to import in ssms v10

@prakashsrinivasan7840 8 ай бұрын

Thank u very much sir

@user-nh1ph2no9v Ай бұрын

Thanks 🙏

@ayushisahu9926 2 жыл бұрын

Thanks the vedio was so helpful but facing few error

@danny63843 2 жыл бұрын

Thank you

@anjanx44 Жыл бұрын

Thank you.

@amalroshans1122 Жыл бұрын

thank you

@ahmedzaghal1736 7 ай бұрын

Thanks for the great video How can you continuously update/refresh the data initially imported from excel using the method described in your video?

@SQLServer101 7 ай бұрын

Hi Ahmed. I would create an SSIS package to do this. Phillip

@xinyue3317 3 жыл бұрын

pretty cool way

@haroldpascua4285 Жыл бұрын

Thank you so much! for this tutorial! i gave you Like and Subscribe.

@juanniyebe3557 5 ай бұрын

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.

@SQLServer101 5 ай бұрын

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

@kareemmansour6726 2 жыл бұрын

How to append more excel file to the same tables created

@jacekk7819 Жыл бұрын

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

@SQLServer101 Жыл бұрын

Hi. I assume you are referring to importing data. They should be a Cancel button which will stop the import from continuing. Phillip

@labiribiri1901 7 ай бұрын

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?

@SQLServer101 7 ай бұрын

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

@labiribiri1901 7 ай бұрын

@@SQLServer101 thank you for responding. but, how do I separate the data after loading it into a SQL server?

@mehrosenasir9974 Жыл бұрын

Hi, I followed the same steps for importing a excel file and it throws this error Invalid object name 'dbo.Sheet1$'.

@terrillchristians 2 ай бұрын

thank you, can you please make the next step in this video- where you import that data from this new table into existing tables

@SQLServer101 2 ай бұрын

Hi Terrill. I would just use a MERGE or INSERT statement, now that everything is in the same SQL Server database. Phillip

@elrevesyelderecho Жыл бұрын

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?

@SQLServer101 Жыл бұрын

Hi. You can, using SSIS or Power Automate. Phillip

@elrevesyelderecho Жыл бұрын

@@SQLServer101 Automate needs premium licenses...so, no. SSIS. Si you know how? Is there a video that explains it? Thanks.

@tvryoda6699 Ай бұрын

Does using the flat file method avoid the 32 bit vs 64 bit issue with the microsoft ole db driver

@SQLServer101 Ай бұрын

Hi Tvryoda. Thank you for your question. The only way to find out in your circumstances is to try it on your machine. Phillip

@jitencaleb7075 2 жыл бұрын

Hi this video is very helpful, but how would you upload live data from excel to SQL sever?

@SQLServer101 2 жыл бұрын

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

@miloniparekh5246 Жыл бұрын

@@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?

@albertopagnin4516 Жыл бұрын

Thank you very much. I need to do the same but using python. Probably with SqlAlchemy, do you know how to do? thanks!

@SQLServer101 Жыл бұрын

No - I don't use Python. I'm glad you enjoyed the video. Phillip

@emmanuelwayagi3913 11 ай бұрын

Hello Phillip, do you have a video on how to connect and import data from Google analytics to SQL server 2022? Thanks in advance.

@SQLServer101 11 ай бұрын

Hi Emmanuel. No, I don't - but please see hevodata.com/learn/google-analytics-to-sql-server/ . Phillip

@amittechnicalagra 10 ай бұрын

kzfaq.info/get/bejne/fb2In6-j1avMgmg.htmlsi=-O8OWKeS3jtY0OFH

@hallythejester1234 Жыл бұрын

I'm getting a very different menu when I right right click on my database. it doesn't have a tasks button at all. :(

@njamrutharikka4105 4 күн бұрын

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

@SQLServer101 4 күн бұрын

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

@davidenriquecastrofernande7596 8 ай бұрын

me sirvió mucho. Yo va de buscar como importar el archivo excel y es mejor el csv

@InamHyder Жыл бұрын

Hi, I would like to import ~8 Million rows. through 8 different Excel/CSV Files. Will this method work?

@SQLServer101 Жыл бұрын

Hi Inam. As long as the table is set up correctly, that should work. Phillip

@lukatcheishvili16 4 ай бұрын

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?

@SQLServer101 4 ай бұрын

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

@blanklate073 Ай бұрын

Is there a way to import from sharepoint? Or excel online

@abelmaseko4040 7 ай бұрын

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.

@SQLServer101 7 ай бұрын

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

@amjadmahmood5904 Жыл бұрын

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.

@SQLServer101 Жыл бұрын

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

@nyali2 Жыл бұрын

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.. (?)

@SQLServer101 Жыл бұрын

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

@CasuallySerious1 Жыл бұрын

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.

@SQLServer101 Жыл бұрын

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 - kzfaq.info/get/bejne/n62qdcqVldfbhGg.html . Phillip

@amittechnicalagra 10 ай бұрын

kzfaq.info/get/bejne/fb2In6-j1avMgmg.htmlsi=-O8OWKeS3jtY0OFH

@selsoft Жыл бұрын

What if there are some wrapped text columns?

@SQLServer101 Жыл бұрын

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

@user-ij6od8pp3b 11 ай бұрын

To get rid of underline, what do you press, ctrl, shift and ?????

@SQLServer101 11 ай бұрын

Hello. You press Ctrl-Shift-R to update the cache. Phillip

@anilredddykanthala 7 ай бұрын

How make relationship between tables when importing from excel workbook..

@SQLServer101 7 ай бұрын

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

@anilredddykanthala 7 ай бұрын

@@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

@tebbytan4415 2 жыл бұрын

how to refresh the data in the table when excel file change?

@SQLServer101 2 жыл бұрын

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

@bhushandeo2864 Жыл бұрын

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.

@SQLServer101 Жыл бұрын

Hi. You are correct - I deal with this in kzfaq.info/get/bejne/iadkf8RkyrK5gI0.html . Phillip

@kunchapusrikanth4919 Жыл бұрын

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

@SQLServer101 Жыл бұрын

Hi Kunchapusrikanth. What error are you getting? Phillip

@anuradhaanuradha7262 Жыл бұрын

It was not showing server native 11.0 in destination what should I do ??

@SQLServer101 Жыл бұрын

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

@tayyabaabro5345 Жыл бұрын

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.

@SQLServer101 Жыл бұрын

Hi Tayya. Make sure you are importing your data into a blank table. If necessary, you may need the sp_configure stored procedure. Phillip

@riskhibayusarwosaputro2034 Жыл бұрын

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?

@SQLServer101 Жыл бұрын

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

@valentinvas6454 5 ай бұрын

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...

@SQLServer101 4 ай бұрын

Hi Valentin. Thank you for your question. I'm afraid I don't know. I would upgrade to a later version of SSMS. Phillip

@valentinvas6454 4 ай бұрын

@@SQLServer101 I figured it out later thankfully.

@deepaksrinivas3727 8 ай бұрын

How to add foriegn key for imported data of 2 tables😮😮😮

@SQLServer101 8 ай бұрын

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

@unknwn_usr3077 2 жыл бұрын

How to import into existing table and update it

@SQLServer101 2 жыл бұрын

Thank you for your message. This video should help with that. Phillip

@unknwn_usr3077 2 жыл бұрын

@@SQLServer101 👍

@gayathriiruku Жыл бұрын

If it's showing error what can be done???

@SQLServer101 Жыл бұрын

Hello. It depends on what error you are seeing. I would import into a fresh table and see if that shows the errors. Phillip

@KyleRebstock 2 ай бұрын

you can import excel files...

@donald01 Жыл бұрын

When I try to import, it says "Class not registered" what does that mean

@SQLServer101 Жыл бұрын

Hi Donald. Please have a look at www.sqlshack.com/import-data-excel-file-sql-server-database/ . Phillip

@mahmoudadas5602 3 ай бұрын

I cannot link excel with SQL. What should i do plz help

@SQLServer101 3 ай бұрын

Hi Mahmoudadas. Thank you for your question. I'm afraid that without more details, I Can't help. Phillip

@numseprut319 Жыл бұрын

Im unable to see the flat or txt files, the are just gone

@SQLServer101 Жыл бұрын

Hello- thank you for your question. You need to change the "type of files" dropdown from "Microsoft Excel" to "All types of files". Phillip

@MrKamilof4 2 жыл бұрын

Why it gives me an error when import it

@nikhilbhute07 Жыл бұрын

Change Data Types if Asked. I got same Error.

@ColonelKurtz 2 жыл бұрын

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?

@SQLServer101 2 жыл бұрын

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

@Jmtreciever2 Жыл бұрын

Doesn't look like mine

@SQLServer101 Жыл бұрын

Hi Jm. It should do - I don’t think they have changed the interface for quite a while. Maybe try it again. Phillip

@goliathstark9142 7 ай бұрын

@pew4410 2 жыл бұрын

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 ------------------------------

@SQLServer101 2 жыл бұрын

Hi Pew. You may find that this helps: www.microsoft.com/en-us/download/details.aspx?id=54920 . Phillip

@pew4410 2 жыл бұрын

@@SQLServer101 I have downloaded it, but I still get the same error

@vaibhavverma1340 Жыл бұрын

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 ------------------------------

@SQLServer101 Жыл бұрын

Hi Vaib. Thank you for your question. Have you got the csv file open (for example, in Notepad)? Phillip

@Nchewi_ng 25 күн бұрын

Apparently, you can't import a dirty CSV file, it has to be cleaned

@yuvamamidipalli7127 Жыл бұрын

Intelligent was not updating after pressing ctrl,shift,r

@SQLServer101 Жыл бұрын

Hi Yuvama. Intellisense should automatically update after pressing those buttons. Make sure that you are in the database you think you are in. Phillip