Import Excel spreadsheet columns into SQL Server database

Question

I have an excel spreadsheet that I want to import select columns into my database table. the wiz didn't offer that option.

any easy code options?

Thanks newbie

Answer 1

The import wizard does offer that option. You can either use the option to write your own query for the data to import, or you can use the copy data option and use the "Edit Mappings" button to ignore columns you do not want to import.

Answer 2

Microsoft suggest several methods:

• SQL Server Data Transformation Services (DTS)
• Microsoft SQL Server 2005 Integration Services (SSIS)
• SQL Server linked servers
• SQL Server distributed queries
• ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
• ADO and the Microsoft OLE DB Provider for Jet 4.0

If the wizard (DTS) isn't working (and I think it should) you could try something like this http://www.devasp.net/net/articles/display/771.html which basically suggests doing something like

INSERT INTO [tblTemp] ([Column1], [Column2], [Column3], [Column4])

SELECT A.[Column1], A.[Column2], A.[Column3], A.[Column4]
FROM OPENROWSET 
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Excel.xls;HDR=YES', 'select * from [Sheet1$]') AS A;

Answer 3

You could use OPENROWSET, something like:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
  'Excel 8.0;IMEX=1;HDR=NO;DATABASE=C:\FILE.xls', 'Select * from [Sheet1$]'

Just make sure the path is a path on the server, not your local machine.

Answer 4

By 'the wiz' I'm assuming you're talking about the 'SQL Server Import and Export Wizard'. (I'm also pretty new so I don't understand most questions, much less most answers, but I think I get this one). If so couldn't you take the spreadsheet, or a copy of it, delete the columns you don't want imported and then use the wizard?

I've always found the ability to do what I need with it and I'm only on SQL Server 2000 (not sure how other versions differ).

Edit: In fact I'm looking at it now and I seem to be able to choose which columns I want to map to which rows in an existing table. On the 'Select Source Tables and Views' screen I check the datasheet I'm using, select the 'Destination' then click the 'Edit...' button. From there you can choose the Excel column and the table column to map it to.

Answer 5

This may sound like the long way around, but you may want to look at using Excel to generate INSERT SQL code that you can past into Query Analyzer to create your table.

Works well if you cant use the wizards because the excel file isn't on the server

Answer 6

Microsoft Access is another option. You could have a Access database locally on your machine that you import the excel spreadsheets into (wizards available) and link to the the SQL Server database tables via ODBC.

You could then design a query in access that appends data from the Excel spreadsheet to the SQL Server Tables.

Answer 7

Once connected to Sql Server 2005 Database, From Object Explorer Window, right click on the database which you want to import table into. Select Tasks -> Import Data. This is a simple tool and allows you to 'map' the incoming data into appropriate table. You can save the scripts to run again when needed.

Answer 8

seems tejas is right, if you hit Import Data from within management studio, it brings up the DTS Wizard with much more data source options.

Answer 9

Read this post "http://msdotnetbuddy.blogspot.com/2010/08/data-exchange-between-sql-server-excel.html"