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.
Select * into TestTable from
OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\MyTable.xls;HDR=Yes;IMEX=1', 'SELECT * FROM [Sheet1$]')
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/
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
ReplyDelete