Import Excel spreadsheet columns into SQL Server d

Question

I have an Excel spreadsheet that I want to import select columns into my SQL Server 2008 database table. The wizard didn't offer that option.

Do any easy code options exist?

Answer 1

Excel + SQLCMD + Perl = exceltomssqlinsert

and you can use your Excel as frond-end to MSSQL db ... Note the truncate table at the beginning of each generated sql insert file ...

Answer 2

If you would like a visual tool with Desktop interface including validation .. you may well like this Excel tool. You can also use the tool to create multi user data-edit tasks, or even paste data to SQL server from any source..

How to Validate and Import Excel spreadsheet to SQL Server database:

http://leansoftware.net/en-us/help/excel-database-tasks/worked-examples/how-to-import-excel-spreadsheet-to-sql-server-data.aspx

Answer 3

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 4

go
sp_configure 'show advanced options',1  
reconfigure with override  
go  
sp_configure 'Ad Hoc Distributed Queries',1  
reconfigure with override  
go
SELECT * into temptable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Documents and Settings\abhisharma\Desktop\exl\ImportExcel2SQLServer\ImportExcel2SQLServer\example.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

select * from temptable

Answer 5

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 6

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