This something I got to do, Import data in Excel 2007 sheet to table in MS SQL database in SQL Express 2005. So I'm blogging it anyone who need can quickly get it done.
Here are the details you need to know:
Excel File: D:\MyTable.xls
Excel Sheet: Sheet1
SQL database: NewTestDB
Database Table: TestTable (This doesn't exist yet)
Beside these things there should be Access Database Engine installed, otherwise it will give a error when you run the query. Download it from here
First you have to enable AD HOC query by entering to the Surface Area Configuration. Go to Start>SQL Server Surface Area Configuration with a windows account with Administrative privileges.
Then go to Surface Area Configuration for Features in the bottom. In that window go to SQLEXPRESS>Database Engine>AD HOC Remote Queries in the left side pane. Put a tick in the check-box next to 'Enable OPENROWSET and OPENDATASOURCE support' option.
Next open a SQL Server Management Studio Express with insert privileges to the Database you want to insert data data into. Open a New Query pane from the top left hand in the toolbar.Then select the the database from the drop down list in the tool bar. Then put following query in the query area.
Select * into TestTable from
OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\MyTable.xls;HDR=Yes;IMEX=1', 'SELECT * FROM [Sheet1$]')
The TestTabele will be created and all the columns in the excel sheet will be imported to the that table.
Note: Click to See the images larger.
Here are the details you need to know:
Excel File: D:\MyTable.xls
Excel Sheet: Sheet1
SQL database: NewTestDB
Database Table: TestTable (This doesn't exist yet)
Beside these things there should be Access Database Engine installed, otherwise it will give a error when you run the query. Download it from here
First you have to enable AD HOC query by entering to the Surface Area Configuration. Go to Start>SQL Server Surface Area Configuration with a windows account with Administrative privileges.
Then go to Surface Area Configuration for Features in the bottom. In that window go to SQLEXPRESS>Database Engine>AD HOC Remote Queries in the left side pane. Put a tick in the check-box next to 'Enable OPENROWSET and OPENDATASOURCE support' option.
Next open a SQL Server Management Studio Express with insert privileges to the Database you want to insert data data into. Open a New Query pane from the top left hand in the toolbar.Then select the the database from the drop down list in the tool bar. Then put following query in the query area.
Select * into TestTable from
OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\MyTable.xls;HDR=Yes;IMEX=1', 'SELECT * FROM [Sheet1$]')
The TestTabele will be created and all the columns in the excel sheet will be imported to the that table.
Note: Click to See the images larger.
Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my visitors will find that very useful.
ReplyDeletehttp://www.sqlservermasters.com/
Great Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
nice computer tricks
ReplyDeleteGreat stuff. This was the thing I needed.
ReplyDeleteIf you use the Apple version of Excel you want to note that only a few versions of excel for the Mac have VBA in them. excel reporting dashboard
ReplyDeleteOther pro-business usage of Excel programming includes chart or graph creation, data conversion, spreadsheet display for the purpose of interpretation of information, data Import from internet, Mail Merge, creating the concept map and lot more.salesforce datawarehouse
ReplyDeleteThanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for. Microsoft Excel Training Courses Malaysia
ReplyDeleteI Regreat For Sharing The information The InFormation shared Is Very Valuable Please Keep Updating Us Time Just Went On Reading The Article Python Online Training AWS Online Training Hadoop Online Training Data Science Online Training
ReplyDelete
ReplyDeleteIt has been simply incredibly generous with you to provide openly what exactly many individuals would’ve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted.
Advanced Python Training Institute in Chennai| Best Python Training institute in Chennai
Advanced RPA Training in Chennai | Best RPA Training institute in Chennai
Advanced DevOps Training in Chennai | Best DevOps Training institute in Chennai
Advanced Azure Training in Chennai | Best Azure Training institute in Chennai
Advanced Java Training in Chennai | Best Java Training institute in Chennai
Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
ReplyDeleteSalesforce Training in Chennai | Certification | Online Course | Salesforce Training in Bangalore | Certification | Online Course | Salesforce Training in Hyderabad | Certification | Online Course | Salesforce Training in Pune | Certification | Online Course | Salesforce Online Training | Salesforce Training
it’s really nice and meanful. it’s really cool blog. Linking is very useful thing.you have really helped lots of people who visit blog and provide them usefull information.
ReplyDeleteData Science Training in Hyderabad
I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up
Devops Training in USA
Hadoop Training in Hyderabad
Python Training in Hyderabad
ReplyDeleteit’s really nice and meanful. it’s really cool blog. Linking is very useful thing.you have really helped lots of people who visit blog and provide them usefull information.
Data Science Training in Hyderabad
I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up
Devops Training in USA
Hadoop Training in Hyderabad
Python Training in Hyderabad
I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up
ReplyDeleteDevops Training in Hyderabad
Hadoop Training in Hyderabad
Python Training in Hyderabad
Tableau Training in Hyderabad
Selenium Training in Hyderabad
I admire this article for well-researched content and excellent wording. Thank you for providing such a unique information here. Vietnam Import Data
ReplyDelete
ReplyDeleteI am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up
Devops Training in Hyderabad
Hadoop Training in Hyderabad
Python Training in Hyderabad
Tableau Training in Hyderabad
Selenium Training in Hyderabad
Informatica Training in Hyderabad