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:

Step By Step Guide to resolve SQL server connection issues when connecting from R Studio

Yesterday I tried using a couple of  RevolveScaleR functions -- RxSqlServerData and Rstudio.  These are my learnings as part of ...