Friday, May 14, 2010

I had a requirement to import an excel 2007 spreadsheet into a database as a table in sql server 2005. In Excel 2007 the drivers required for exporting have changed when compared to the previous versions. With Excel 2007 you use the driver named 'Microsoft Access 12.0 database engine OL DB provider'

Following are the steps to import data into sql server 2005 from Excel.
  • Right click on the database from sql server management studio and choose tasks -- import data
  • Choose the data source as the driver name specified above ('Microsoft Access 12.0 database engine OL DB provider')
  • Click on properties button and click on All tab
  • Double click on the data source line and give the file name with the exact path in the property value field. Click ok
  • Double click on the Extended properties line and enter Excel12.0 in the property value field. Click ok twice.
  • Click next through the import wizard and preview the data and click finish.

Your data is imported into the sql server database.

No comments:

Free Databases and Data Visualisations course for kids Weeks 9 to 11 update

It has been quite a while since I have posted about the free databases and data visualisation classes for 9 to 14 year old kids.  So here I...