Jan 28, 2012

Import data from Excel to SQLServer

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.


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$]');

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



--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$]');

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

Msg 15281, Level 16, State 1, Line 1
SQL 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.
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.
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.
Msg 7302, Level 16, State 1, Line 1Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.
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.




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! :)

42 comments:

  1. Sir,
    can 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

    ReplyDelete
  2. Its not working in "Microsoft.Jet.OLEDB.4.0".
    Can you tell me, how to solve this ?

    ReplyDelete
  3. 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.
    digital marketing training in annanagar

    digital marketing training in marathahalli

    ReplyDelete
  4. 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.
    full stack developer training in annanagar

    full stack developer training in tambaram

    full stack developer training in velachery

    ReplyDelete
  5. 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.
    python training institute in chennai
    python training in Bangalore
    python training in pune

    ReplyDelete
  6. 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.
    Blueprism training in Pune

    Blueprism online training

    Blue Prism Training in Pune

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



    Data Science training in Chennai
    Data science training in bangalore
    Data science online training
    Data science training in pune

    ReplyDelete
  8. 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.
    Microsoft Azure online training
    Selenium online training
    Java online training
    Java Script online training
    Share Point online training

    ReplyDelete
  9. Excellent post, must say thanks for the information you added to this post. I appreciate your post and looking forward for more.

    Data Science

    ReplyDelete
  10. 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.
    data analytics certification courses in Bangalore
    ExcelR Data science courses in Bangalore

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. It 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.
    machine learning course malaysia

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

    Data Science Course

    ReplyDelete
  14. 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.
    python training in bangalore

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. I 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

    ReplyDelete
  17. I 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

    ReplyDelete
  18. This 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.
    pmp certification malaysia

    ReplyDelete
  19. health care analytics
    https://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.

    ReplyDelete
  20. Usually 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.
    Machine Learning Training In Hyderabad

    ReplyDelete
  21. 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.
    Artificial Intelligence Training In Hyderabad

    Artificial Intelligence Course In Hyderabad

    ReplyDelete
  22. 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
    python 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

    ReplyDelete

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

    ReplyDelete
  24. A well explained article. I want to thank you for the efforts you take for this article. keep sharing

    Data Science Training in Pune

    ReplyDelete
  25. Really an awesome blog and informative content. Keep sharing more content with us. Thanks for sharing with us.
    Data Science Training and Placements in Hyderabad

    ReplyDelete
  26. Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post.
    data analytics course in hyderabad

    ReplyDelete
  27. 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.
    Data Analytics Courses in Nashik

    ReplyDelete
  28. 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.
    Data Analytics Courses In Chennai

    ReplyDelete
  29. 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.
    Visit - Data Analytics Courses in Delhi

    ReplyDelete
  30. Thank you for sharing this detailed tutorial on how to export the data from excel to an SQL server. keep posting more.
    Visit - Data Analytics Courses in Delhi

    ReplyDelete
  31. Thanks for sharing comprehensive and informative tutorial on how to Import data from Excel to SQLServer.
    Digital Marketing Courses in Italy

    ReplyDelete
  32. 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.
    Data analytics framework

    ReplyDelete
  33. Fantastic article. Very useful and easy to comprehend. Thanks for sharing..
    Investment banking analyst jobs

    ReplyDelete