Many times we come across a situation where we have readily available excel sheets having enormous data that needs to be imported in database system for enhanced querying capabilities or as a backend datasource for some sort of software development.
We could achieve this goal either by copy-pasting the excel rows directly to the destined database table in SQL Server Management Studio or by querying directly on the excel sheet from SQL Server Management Studio itself!
The former way (copy-pasting) looks pretty simple but consider a situation where you are having 10K records in a single excel sheet and wanted to fetch some selective/filtered records only! At that time, the earlier way is much efficient and ideal.
I've created this sample excel file to import/retrieve the data in SQL server, and is also the file I will be using in this article. Here is the screenshot for the same.
OPENROWSET() to query excel files
Here, I've used the OPENROWSET() function to query excel files. This is a T-SQL function that can be used to access any OLE DB data source. All you need is the right OLE DB driver.
The following are the queries to retrieve data from Excel files.
These queries return excel data in the results window when executing with SQL Server Management Studio. To insert the data into a table, just uncomment the INTO clause. That will create a local temporary table with the retrieved data in the tempdb database.
Here, HDR=YES means the 1st row in the excel sheet is a column name. If that's not the case set HDR = No instead YES.
Note : If you receive an error message in executing the query, i've given some possible errors with their respective solution below.
If you've executed uncommented INTO clause query then you can query the temporary table (here, it's Cars table) just like any other normal sql table.
CHECK DATA TYPES OF TEMP TABLE (#Cars)
Let's have a look how such temporary tables are structured when created using OPENROWSET(). Execute the following command in query window.
The following screenshot shows the data types of each field in the #Cars temp table.
I discovered that even with the formatted cells in the excel file the derived type and length of the columns in the temporary table is solely based on SQL Server's own algorithm.
I've formatted ProductId column in the Excel sheet as a number with zero decimal places. Even though, it is stored as a float type in the temporary table!
If you wish to avoid such behavior, it's always better to import data directly in SQL tables that are created already with the desired data-types.
QUERY TO IMPORT DATA DIRECTLY IN SQL TABLES
It's up to you to decide where to store records. You can store either in Temporary table or in SQL Table with the desired table structure and types.
Possible Issues
Enable 'AD HOC DISTRIBUTED QUERIES'
AD HOC DISTRIBUTED QUERIES option must be enabled on the server to use the OPENROWSET() function. If it's not enabled on your server then you will get the following error message.
OLE DB DRIVER IS NOT INSTALLED
OPENROWSET() function needs OLE DB drivers installed. If the right driver is not installed you will see error something like following one.
In the absence of required drivers you can download the same from the Microsoft's site.
Excel 97-2003 JET 4.0 driver
Excel 2007 ACE driver
Excel 2010 ACE driver (beta)
That's it! Have fun! :)
We could achieve this goal either by copy-pasting the excel rows directly to the destined database table in SQL Server Management Studio or by querying directly on the excel sheet from SQL Server Management Studio itself!
The former way (copy-pasting) looks pretty simple but consider a situation where you are having 10K records in a single excel sheet and wanted to fetch some selective/filtered records only! At that time, the earlier way is much efficient and ideal.
I've created this sample excel file to import/retrieve the data in SQL server, and is also the file I will be using in this article. Here is the screenshot for the same.
Excel sheet - Cars |
OPENROWSET() to query excel files
Here, I've used the OPENROWSET() function to query excel files. This is a T-SQL function that can be used to access any OLE DB data source. All you need is the right OLE DB driver.
The following are the queries to retrieve data from Excel files.
--Excel 2007-2010
SELECT * --INTO #Cars
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\cars.xlsx','SELECT * FROM [cars$]');
SELECT * --INTO #Cars
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\cars.xlsx','SELECT * FROM [cars$]');
--Excel 97-2003
SELECT * --INTO #Cars
FROM OPENROWSET('Microsoft.ACE.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=C:\cars.xls','SELECT * FROM [cars$]');
These queries return excel data in the results window when executing with SQL Server Management Studio. To insert the data into a table, just uncomment the INTO clause. That will create a local temporary table with the retrieved data in the tempdb database.
Here, HDR=YES means the 1st row in the excel sheet is a column name. If that's not the case set HDR = No instead YES.
Note : If you receive an error message in executing the query, i've given some possible errors with their respective solution below.
If you've executed uncommented INTO clause query then you can query the temporary table (here, it's Cars table) just like any other normal sql table.
SELECT * FROM #Cars
CHECK DATA TYPES OF TEMP TABLE (#Cars)
Let's have a look how such temporary tables are structured when created using OPENROWSET(). Execute the following command in query window.
USE tempdb;
GO
sp_help '#Cars'
The following screenshot shows the data types of each field in the #Cars temp table.
Structure of Temporary Table #Cars in SQL Server |
I've formatted ProductId column in the Excel sheet as a number with zero decimal places. Even though, it is stored as a float type in the temporary table!
If you wish to avoid such behavior, it's always better to import data directly in SQL tables that are created already with the desired data-types.
QUERY TO IMPORT DATA DIRECTLY IN SQL TABLES
--Excel 2007-2010
INSERT INTO DATABASE.SCHEMA.TABLENAME
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\cars.xlsx','SELECT * FROM [cars$]');
INSERT INTO DATABASE.SCHEMA.TABLENAME
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\cars.xlsx','SELECT * FROM [cars$]');
--Excel 97-2003
INSERT INTO DATABASE.SCHEMA.TABLENAME
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=C:\cars.xls','SELECT * FROM [cars$]');
It's up to you to decide where to store records. You can store either in Temporary table or in SQL Table with the desired table structure and types.
Possible Issues
Enable 'AD HOC DISTRIBUTED QUERIES'
AD HOC DISTRIBUTED QUERIES option must be enabled on the server to use the OPENROWSET() function. If it's not enabled on your server then you will get the following error message.
You can enable this option through SQL Server Surface Area Configuration tool (Start > All Programs > Microsoft SQL Server > Configuration Tools > SQL Server Surface Area Configuration > Surface Area Configuration for Features). See the following screenshot.
Msg 15281, Level 16, State 1, Line 1SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.
Enabling ADHOC Distributed queries |
OLE DB DRIVER IS NOT INSTALLED
OPENROWSET() function needs OLE DB drivers installed. If the right driver is not installed you will see error something like following one.
To check the driver is installed or not open ODBC Data Source Administrator (Start > Run > type ODBCAD32.EXE and hit enter) and check under the Drivers tab. The following screenshot shows the both drivers JET 4.0 for Excel 97-2003 and new ACE Driver for Excel 2007.Msg 7302, Level 16, State 1, Line 1Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.
In the absence of required drivers you can download the same from the Microsoft's site.
Excel 97-2003 JET 4.0 driver
Excel 2007 ACE driver
Excel 2010 ACE driver (beta)
That's it! Have fun! :)
Sir,
ReplyDeletecan you provide me help for creating vice-versa for this above application.That means code to import data from sql server to ms excel please provide me urgent help.........
I am using VS 2008 3.5 framework C# Environment and
backend as SQL SERVER 2008
my email id:mahesh.chikhale007@gmail.com
wow so nice blog this is!!!
DeleteData science course in Mumbai
Business Analytics course
Its not working in "Microsoft.Jet.OLEDB.4.0".
ReplyDeleteCan you tell me, how to solve this ?
This is a terrific article, and that I would really like additional info if you have got any. I’m fascinated with this subject and your post has been one among the simplest I actually have read.
ReplyDeletedigital marketing training in annanagar
digital marketing training in marathahalli
Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
ReplyDeletefull stack developer training in annanagar
full stack developer training in tambaram
full stack developer training in velachery
It seems you are so busy in last month. The detail you shared about your work and it is really impressive that's why i am waiting for your post because i get the new ideas over here and you really write so well.
ReplyDeletepython training institute in chennai
python training in Bangalore
python training in pune
Thank you for allowing me to read it, welcome to the next in a recent article. And thanks for sharing the nice article, keep posting or updating news article.
ReplyDeleteBlueprism training in Pune
Blueprism online training
Blue Prism Training in Pune
Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information..
ReplyDeleteData Science training in Chennai
Data science training in bangalore
Data science online training
Data science training in pune
Awesome! Education is the extreme motivation that open the new doors of data and material. So we always need to study around the things and the new part of educations with that we are not mindful.
ReplyDeleteMicrosoft Azure online training
Selenium online training
Java online training
Java Script online training
Share Point online training
Excellent post, must say thanks for the information you added to this post. I appreciate your post and looking forward for more.
ReplyDeleteData Science
I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
ReplyDeletedata analytics certification courses in Bangalore
ExcelR Data science courses in Bangalore
This comment has been removed by the author.
ReplyDeleteIt was a very good post indeed. I thoroughly enjoyed reading it in my lunch time. Will surely come and visit this blog more often. Thanks for sharing.
ReplyDeletemachine learning course malaysia
I wanted to leave a little comment to support you and wish you a good continuation. Wishing you the best of luck for all your blogging efforts.
ReplyDeleteData Science Course
Attend The Python training in bangalore From ExcelR. Practical Python training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python training in bangalore.
ReplyDeletepython training in bangalore
This comment has been removed by the author.
ReplyDeleteI just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page digital marketing course in singapore
ReplyDeleteI just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page! digital marketing course in singapore
ReplyDeleteThis is a smart blog. I mean it. You have so much knowledge about this issue, and so much passion. You also know how to make people rally behind it, obviously from the responses.
ReplyDeletepmp certification malaysia
health care analytics
ReplyDeletehttps://360digitmg.com/india/life-sciences-and-healthcare-analytics-certification-programme
In accordance with the trends in the broader Healthcare industry in the US, 360DigiTMG has developed the Certification Program in Healthcare Analytics. This course is meticulously designed to suit both Healthcare practitioners, IT Business Analysts and Data Scientists.
Thanks for giving me the time to share such nice information. Data Science Course Training in Bangalore
ReplyDeleteUsually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job, Keep it up.
ReplyDeleteMachine Learning Training In Hyderabad
fantastic blog!very useful keep it up
ExcelR data analytics courses
fantastic blog!very useful keep it up
ReplyDeleteExcelR data analytics courses in mumbai
This is the information that ive been looking for. Great insights & you have explained it really well. Thank you & looking forward for more of such valuable updates.
ReplyDeleteArtificial Intelligence Training In Hyderabad
Artificial Intelligence Course In Hyderabad
The Blog is Fantastic. This blog is really clarifying the queries and the Blog is very Impressive.
ReplyDeleteData Science Training Course In Chennai | Data Science Training Course In Anna Nagar | Data Science Training Course In OMR | Data Science Training Course In Porur | Data Science Training Course In Tambaram | Data Science Training Course In Velachery
ReplyDeleteThe Content You Shared With Us Is Excellent & Extraordinary Useful to all Aspirants Thanks For Sharing With Us!
Best Degree College In Hyderabad
Top Degree College In Hyderabad
Top And Best BBA College In Hyderabad
Top And Best B.Com College In Hyderabad
I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. It looks like you spend a lot of effort and time on your blog
ReplyDeletepython training in chennai
python online training in chennai
python training in bangalore
python training in hyderabad
python online training
python flask training
python flask online training
python training in coimbatore
Nice and very informative article. As keywords are the things which help us in building our online business promotion platform. You narrated such simple things in a simple manner.Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site. Data Science Training In Chennai | Certification | Data Science Courses in Chennai | Data Science Training In Bangalore | Certification | Data Science Courses in Bangalore | Data Science Training In Hyderabad | Certification | Data Science Courses in hyderabad | Data Science Training In Coimbatore | Certification | Data Science Courses in Coimbatore | Data Science Training | Certification | Data Science Online Training Course
ReplyDelete
ReplyDeleteIts as if you had a great grasp on the subject matter, but you forgot to include your readers. Perhaps you should think about this from more than one angle.
machine learning courses in bangalore
A well explained article. I want to thank you for the efforts you take for this article. keep sharing
ReplyDeleteData Science Training in Pune
Really an awesome blog and informative content. Keep sharing more content with us. Thanks for sharing with us.
ReplyDeleteData Science Training and Placements in Hyderabad
Great post. keep sharing such a worthy information.
ReplyDeleteRPA Training in Chennai
RPA Training Online
RPA Training In Bangalore
Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post.
ReplyDeletedata analytics course in hyderabad
smm panel
ReplyDeletesmm panel
iş ilanları
İnstagram takipçi satın al
HIRDAVATÇI BURADA
beyazesyateknikservisi.com.tr
SERVİS
tiktok jeton hilesi
I appreciate the author for providing a comprehensive guide to importing data from Excel into SQL Server. The detailed steps and SQL queries make it a valuable resource for anyone dealing with data integration. A must-read for database professionals! Thanks a lot for sharing.
ReplyDeleteData Analytics Courses in Nashik
Hello Blogger, Your contribution in simplifying this process is highly appreciated, thank you for sharing your knowledge and expertise on data importation, Keep sharing your valuable insights.
ReplyDeleteData Analytics Courses In Chennai
Hi dear blogger, I really loved that you explained this wonderful tutorial on how to export the data from excel to an SQL server. It was so helpful for sure.
ReplyDeleteVisit - Data Analytics Courses in Delhi
Thank you for sharing this detailed tutorial on how to export the data from excel to an SQL server. keep posting more.
ReplyDeleteVisit - Data Analytics Courses in Delhi
Thanks for sharing comprehensive and informative tutorial on how to Import data from Excel to SQLServer.
ReplyDeleteDigital Marketing Courses in Italy
The step-by-step guide in the blog simplifies the process, making it accessible for users of all levels. This seamless integration between Excel and SQL Server is efficient for data management. nice blog. keep posting.
ReplyDeleteData analytics framework
Fantastic article. Very useful and easy to comprehend. Thanks for sharing..
ReplyDeleteInvestment banking analyst jobs