How to import data from Microsoft Excel into Microsoft SQL Server

[@SQLServer101]

If you can't use the SQL Server Native Client, then you should use the Microsoft OLE DB Driver. See kzbin.info/www/bejne/iXKXfZRpmrGIhrs for details. If you want to import data from SQL Server into Excel, please have a look at our video: kzbin.info/www/bejne/hF66mn-Ka6-Em9U

[@willitivity]

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

[@gabrielbarros8543]

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

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=-O8OWKeS3jtY0OFH

[@amittechnicalagra]

​@@gabrielbarros8543kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=-O8OWKeS3jtY0OFH

[@JeffersonMartinSynfluent]

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

[@pawanT23]

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

[@sunilraja6149]

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

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=-O8OWKeS3jtY0OFH

[@khonrak]

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]

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

[@OscarBalwin]

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

[@ahmedzaghal1736]

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]

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

[@kareemmansour6726]

How to append more excel file to the same tables created

[@emmanuelwayagi3913]

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]

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

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=-O8OWKeS3jtY0OFH

[@labiribiri1901]

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]

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]

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

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

[@pujadey2771]

The video was very helpful.. thanks

[@anilredddykanthala]

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

[@SQLServer101]

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]

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

[@srinandhegde3791]

This was the best video I found so far, thanks!!! I have a question, if we import before deleting the table from database, does this new imported table replace the existing one? (Assuming names of both tables are same)

[@SQLServer101]

Hello. Thank you for your message. It depends on the import settings. By default, the wizard will throw an error saying that the table already exists. However, you may be able to either append the data, or drop the existing table before importing the new data. I hope that helps. Phillip

[@mehrosenasir9974]

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

[@oyeladunraphealkunle]

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

[@SQLServer101]

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

[@sangeetharamtenki992]

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

[@vlasov786]

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

[@alexsklemin3708]

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

[@mevkaptan5187]

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

[@juanniyebe3557]

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]

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

[@lukatcheishvili16]

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]

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

[@arulkavi4931]

If you have null value in the integer column of external data ,using the import wizard gives me a problem. Similarly, in constraint alloted columns null value too. If you present a video with possible problems and solutions, it will be appreciated.

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

[@jitencaleb7075]

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

[@SQLServer101]

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?

[@SoftwareDev7]

The CSV route appears much simpler. Is there any benefit in keeping the file as .xlsx and using the import Data method? Thank you. I purchased your SQL Server course on Udemy. :)

[@SQLServer101]

Hi Development. Thank you for your question. The main reason is if you don't want to save it as an CSV file. However, importing CSV files are generally a lot easier than importing Excel files. Thanks for joining me in our Udemy course. Phillip

[@rabiagcr1421]

Thx bro finally i imported data

[@deepaksrinivas3727]

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

[@SQLServer101]

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

[@skshahbazali6238]

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

[@blanklate073]

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

[@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 kzbin.info/www/bejne/iXKXfZRpmrGIhrs . Phillip

[@terrillchristians]

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]

Hi Terrill. I would just use a MERGE or INSERT statement, now that everything is in the same SQL Server database. 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

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

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

[@jrdesaria]

Very neat explanation

[@dongnguyen-id3pe]

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

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=-O8OWKeS3jtY0OFH

[@abelmaseko4040]

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]

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

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

[@souravkumar3452]

Very useful video❤❤❤

[@DCh-ks6ug]

i do not have the task option in my list even when pressing on the database itself what should I do ?

[@SQLServer101]

Hi DCh. Thank you for your question. Can you please elaborate. Phillip

[@MrMelvinmoore]

AMAZING VID. Thank you Can you do this with an ever-changing dynamic excel file?

[@SQLServer101]

Hi Melvin. Thank you for your question. You would need to use SSIS and have regular imports. Phillip

[@ShashirajPS]

Very good video to understand

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

[@valentinvas6454]

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]

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]

@@SQLServer101 I figured it out later thankfully.

[@tebbytan4415]

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

[@SQLServer101]

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

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

[@varunakarniya]

wow thank you so much, this really helped

[@AdventureHeba]

thanx but the flat file option does not appear? there is only import data option

[@SQLServer101]

Hi Adventure. My guess is that you have installed the Express edition. It should work with other editions. 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

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

[@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. :(

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

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

[@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 - kzbin.info/www/bejne/n3jdc5qaZdaqipY . Phillip

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=-O8OWKeS3jtY0OFH

[@chittchirag4326]

Thank you. Very Informative.

[@SQLServer101]

Hi Chitt. You are welcome. Phillip

[@zoomcam7706]

Nice Tutorial

[@mahmoudadas5602]

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

[@SQLServer101]

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

[@ayushisahu9926]

Thanks the vedio was so helpful but facing few error

[@greetika-mb4rq]

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

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

[@unknwn_usr3077]

How to import into existing table and update it

[@SQLServer101]

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

[@unknwn_usr3077]

@@SQLServer101 👍

[@davidenriquecastrofernande7596]

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

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

[@rahulramesh100]

Thanks bro! this was neat!

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

[@hasanyarar9020]

That was helpfull. Thanks

[@riskhibayusarwosaputro2034]

thank you very much for your great video dude

[@SoniaMohibi]

thank u so much.. so helpful

[@ColonelKurtz]

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]

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

[@arkanish0657]

great video for learning

[@harshshah9006]

Thanksssssssssss!!!!!!! U saved me

[@KyleRebstock]

you can import excel files...

[@isaiahparker6170]

Thank you for this video!

[@prakashsrinivasan7840]

Thank u very much sir

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

[@MrKamilof4]

Why it gives me an error when import it

[@nikhilbhute07]

Change Data Types if Asked. I got same Error.

[@haroldpascua4285]

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

[@pew4410]

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]

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

[@pew4410]

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

[@renat9804]

Thanks 🙏

[@Nchewi_ng]

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

[@anjanx44]

Thank you.

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

[@kingsfordgokah6240]

Thank you so much

[@rkvisiontv7728]

Thank you sir

[@danny63843]

Thank you

[@xinyue3317]

pretty cool way

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

[@goliathstark9142]

n1

[@njamrutharikka4105]

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]

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

[@adegbitetemitope9342]

Super helpful! thanks

[@FatimahOptics]

Thanks 🙏

[@habibhajji1764]

thank you sir

[@amalroshans1122]

thank you