Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I import an excel spreadsheet into SQL Server?

I need to import an excel spreadsheet into SQL Server 2005. What steps would I need to take to accomplish that?

like image 512
Fee Avatar asked Jan 23 '09 11:01

Fee


People also ask

Can you use Excel with SQL Server?

Microsoft Excel workbooks are one type of OLE DB data source that you can query through SQL Server in this manner.


3 Answers

An alternative quick and (very) dirty solution is to add a formula to the excel sheet, like this:

="INSERT INTO table1(col1, col2, col3) SELECT " & A1 & ", '" & B1 & "', '" & C1 & "'"

Copy this down (CTRL+D) and you're good to go.

like image 173
edosoft Avatar answered Sep 26 '22 19:09

edosoft


The "Data Transformation Services" wizards are your friend.

The instructions here assume SQL Server 2000

  • Open SQL Server Enterprise Manager
  • Right-click on the appropriate server
  • Select Import Data
  • Run through the wizard.
    • Click Next on the first screen
    • Choose "Microsoft Excel " as your datasource
    • Enter the path to the XLS file and click next.
    • Enter the connection details for your database and click next.

Depending on what you want to do with the data, you have a few options new. For one-off jobs I generally find it easiest to import all the excel data, then edit it in SQL Server, but if you're going to repeat this action on multiple files, you might want to craft a really sexy import script.

If you are going to run it again, you can save the DTS package using the wizard, then edit it in the "Data Transformation Services" section of Enterprise Manager. It's a good way to learn how DTS works.

like image 45
RB. Avatar answered Sep 25 '22 19:09

RB.


you can also do it with OPENROWSET

INSERT INTO SOMETABLE SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')
like image 23
SQLMenace Avatar answered Sep 27 '22 19:09

SQLMenace