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

93 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. Thanks for such a great article here. I was searching for something like this for quite a long time and at last I’ve found it on your blog. It was definitely interesting for me to read  about their market situation nowadays.
    java training in chennai | java training in bangalore


    java training in tambaram | java training in velachery

    ReplyDelete
  9. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
    angularjs-Training in pune

    angularjs-Training in chennai

    angularjs Training in chennai

    angularjs-Training in tambaram

    angularjs-Training in sholinganallur

    ReplyDelete
  10. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.


    AWS Training in Bangalore | Amazon Web Services Training in bangalore , india

    AWS Training in pune | Amazon Web Services Training in Pune, india

    AWS Training in Chennai|Amazon Web Services Training in Chennai,India



    aws online training and certification | amazon web services online training ,india

    ReplyDelete
  11. 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
  12. Nice tips. Very innovative... Your post shows all your effort and great experience towards your work Your Information is Great if mastered very well.
    devops online training

    aws online training

    data science with python online training

    data science online training

    rpa online training

    ReplyDelete
  13. 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
  14. 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
  15. This comment has been removed by the author.

    ReplyDelete
  16. Personally I think overjoyed I discovered the blogs.
    Data Science Course in Pune

    ReplyDelete
  17. 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
  18. 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
  19. 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
  20. This comment has been removed by the author.

    ReplyDelete
  21. 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
  22. 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.
    Love it, Thanks For Sharing.

    ReplyDelete
  23. 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
  24. I like viewing web sites which comprehend the price of delivering the excellent useful resource Python training in pune free of charge. I truly adored reading your posting. Thank you!

    ReplyDelete
  25. 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
  26. Nice Post...I have learn some new information.thanks for sharing.
    Click here for ExcelR Business Analytics Course

    ReplyDelete
  27. Attend The Analytics Courses in Bangalore From ExcelR. Practical Analytics Courses in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Analytics Courses in Bangalore.
    ExcelR Analytics Courses in Bangalore

    ReplyDelete
  28. Really thanks for sharing...This blog is awesome very informative..
    ExcelR Machine Learning

    ReplyDelete
  29. Nice blog,I understood the topic very clearly,And want to study more like this.
    Data Scientist Course

    ReplyDelete
  30. Nice blog,I understood the topic very clearly,And want to study more like this.
    Data Scientist Course

    ReplyDelete
  31. 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
  32. 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
  33. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data science Interview Questions

    ReplyDelete
  34. I have to search sites with relevant information on given topic and provide them to teacher our opinion and the article.

    ExcelR data analytics courses

    ReplyDelete
  35. Attend The Course in Data Analytics From ExcelR. Practical Course in Data Analytics Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Course in Data Analytics.
    Course in Data Analytics
    Data Science Interview Questions

    ReplyDelete

  36. I think this is one of the most significant information for me. And I’m glad reading your article. Thanks for sharing!
    DevOps Training In Hyderabad

    ReplyDelete
  37. 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
  38. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data Science Course

    ReplyDelete
  39. cool stuff you have and you keep overhaul every one of us

    data science course

    ReplyDelete
  40. This is an awesome post.Really very informative and creative contents about Java. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  41. Thank you for sharing nice blog. I hope people are getting really good information from this blog
    Python Training In Hyderabad

    ReplyDelete
  42. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. keep it up.
    data analytics course in Bangalore

    ReplyDelete
  43. Thanks Good Post. This is really great informative blog.
    Python Training In Hyderabad

    ReplyDelete
  44. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance

    ReplyDelete
  45. Cool stuff you have and you keep overhaul every one of us

    Correlation vs Covariance

    ReplyDelete
  46. I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!

    Simple Linear Regression

    ReplyDelete
  47. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression

    ReplyDelete
  48. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery

    ReplyDelete
  49. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  50. Attend The Business Analytics Course From ExcelR. Practical Business Analytics Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Course.
    Business Analytics Course

    ReplyDelete
  51. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  52. Thank you so much for this incredible guide. This has given me so much information
    AI Training in Hyderabad

    ReplyDelete
  53. This Was An Amazing ! I Haven't Seen This Type of Blog Ever ! Thankyou For Sharing, data science course in hyderabad with placements

    ReplyDelete
  54. This post is really helpful for us. I certainly love this website, keep on it.
    Data Science Course in Hyderabad

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

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  56. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  57. 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
  58. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  59. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  60. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.

    Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.


    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.


    The Nodejs Training Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

    ReplyDelete
  61. I have read your blog and I gathered some new information through your blog. Thanks for sharing the information
    Data Science Training in Hyderabad

    ReplyDelete
  62. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm

    ReplyDelete
  63. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.

    Simple Linear Regression

    Correlation vs covariance

    KNN Algorithm

    Logistic Regression explained

    ReplyDelete
  64. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.

    data science interview questions

    ReplyDelete
  65. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Simple Linear Regression
    Correlation vs covariance
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  66. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete

  67. 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
  68. very well explained. I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  69. very well explained. I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Logistic Regression explained
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm

    ReplyDelete
  70. very well explained. I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Logistic Regression explained
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm

    ReplyDelete
  71. Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
    Data Analyst Course

    ReplyDelete
  72. Attend The Data Science Courses Bangalore From ExcelR. Practical Data Science Courses Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Courses Bangalore.
    Data Science Courses Bangalore

    ReplyDelete
  73. This is a good post. This post gives truly quality information. I’m definitely going to look into it. Really very useful tips are provided here. Thank you so much. Keep up the good works ExcelR Business Analytics Courses

    ReplyDelete
  74. Highly appreciable regarding the uniqueness of the content. This perhaps makes the readers feels excited to get stick to the subject. Certainly, the learners would thank the blogger to come up with the innovative content which keeps the readers to be up to date to stand by the competition. Once again nice blog keep it up and keep sharing the content as always.
    Data Science Training

    ReplyDelete
  75. If it's not too much trouble share more like that. ExcelR Data Analytics Course

    ReplyDelete
  76. Thanks for posting the best information and the blog is very helpful.python course in Bangalore

    ReplyDelete
  77. Thanks for posting the best information and the blog is very helpful.data science interview questions and answers

    ReplyDelete
  78. Wonderful blog found to be very impressive to come across such an awesome blog. I should really appreciate the blogger for the efforts they have put in to develop such an amazing content for all the curious readers who are very keen of being updated across every corner. Ultimately, this is an awesome experience for the readers. Anyways, thanks a lot and keep sharing the content in future too.

    data science institute in bangalore

    ReplyDelete
  79. I really thank you for the valuable info on this great subject and look forward to more great posts
    data scientist training in hyderabad

    ReplyDelete