Import Excel spreadsheet columns into SQL Server database

L

15

64

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?

Lovelorn answered 17/12, 2008 at 20:54 Comment(0)

L

51

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.

Lorna answered 9/6, 2010 at 1:25 Comment(4)

Use SQL Server Management Studio to establish a connection to your database. Then this answer makes sense. This task starts the SQL Server Import and Export Wizard that includes Microsoft Excel as a data source. This is in contrast to running the native Import and Export SSIS application that does not have this option }:-(. – Scotism 19/1, 2012 at 19:43

Tasks -> Import Data does not exist in that menu on 2008. – Conservatory 19/12, 2012 at 18:44

Using SQL Server 2008 (not R2) I do see Tasks --> Import Data when right clicking on a DB. – Boser 14/3, 2014 at 18:44

This option is not available if SSIS is not installed. If you are using SSMS on a SQL Server with this installed, you should have the option. If you are on a computer with only client tools (just SSMS) installed, you will not see the option. – Maternal 29/7, 2014 at 6:17

I

25

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;

Irremediable answered 17/12, 2008 at 21:2 Comment(3)

Hmm. this indicates that I have to have excel on a server instead of my pc. is there a way to direct it to my pc? I haven't found that answer yet. – Lovelorn 18/12, 2008 at 16:4

When I try this, I get the error 'Syntax error in FROM clause. (Microsoft JET Database Engine)' – Ranzini 11/12, 2011 at 16:48

You can try this download file microsoft.com/en-us/download/details.aspx?id=13255 for 64x I haven't tested but look promise.. – Savoyard 19/2, 2013 at 17:54

E

12

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

Echoism answered 22/12, 2008 at 2:41 Comment(1)

This (link) is a similar method I use for inserting data into MySQL (same method can be used here) – Rightminded 6/4, 2012 at 20:41

G

7

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.

Goodwife answered 17/12, 2008 at 21:3 Comment(4)

nO WAY TO ACCESS MY LOCAL MACHINE? – Lovelorn 18/12, 2008 at 16:5

Well, you could probably create a share on your local machine and map a network drive from your SQL Server machine to that share, and access your file that way.... – Kodiak 18/2, 2009 at 16:58

I get the error, Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". – Tillett 29/9, 2014 at 20:34

What OS? If it's old, you may need to install MDAC – Goodwife 7/10, 2014 at 23:31

C

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

Counterproductive answered 14/6, 2011 at 10:35 Comment(0)

A

4

Another option is to use VBA in Excel, and write a macro to parse the spreadsheet data and write it into SQL.

One example is here: http://www.ozgrid.com/forum/showthread.php?t=26621&page=1

Sub InsertARecord() 
Dim cnt As ADODB.Connection 
Dim rst As ADODB.Recordset 
Dim stCon As String, stSQL As String 
Set cnt = New ADODB.Connection 
Set rst = New ADODB.Recordset 

stCon = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=JOEY" 
cnt.ConnectionString = stCon 
stSQL = "INSERT INTO MyTable (Price)" 
stSQL = stSQL & "VALUES (500)" 

cnt.Open 
rst.Open stSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText 

If CBool(rst.State And adStateOpen) = True Then rst.Close 
Set rst = Nothing 
If CBool(cnt.State And adStateOpen) = True Then cnt.Close 
Set cnt = Nothing 

End Sub

Analog answered 3/9, 2011 at 17:22 Comment(0)

P

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.

Propend answered 17/12, 2008 at 21:9 Comment(0)

O

3

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

Oldster answered 5/2, 2014 at 22:53 Comment(0)

B

2

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.

Brinson answered 12/2, 2010 at 19:40 Comment(0)

B

1

I think it will help you

Visit http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/78f8e980-0bb6-4a48-b1b1-5f65bca1d511

Brogdon answered 17/12, 2008 at 20:54 Comment(0)

C

1

The best tool i've ever used is http://tools.perceptus.ca/text-wiz.php?ops=7 Did you try it?

Cath answered 21/6, 2012 at 10:27 Comment(0)

B

0

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.

Brause answered 17/12, 2008 at 21:1 Comment(3)

It didn't give me an option to import into an existing table, just the database and it would create a new table. (SQLServer 2008) – Lovelorn 17/12, 2008 at 21:4

Found how that works. thanks. Now I found a bigger proplem. I'm using Management studio 2008 and the data base is 2000. I assumed wrong that it was 2005. – Lovelorn 18/12, 2008 at 16:9

I haven't used management studio 2008 yet (we're running 2005). Management studio 2005 is backward compatible with 2000. – Brause 18/12, 2008 at 21:37

S

0

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 ...

Simonsimona answered 23/4, 2011 at 19:55 Comment(0)

U

0

I have used DTS (now known as SQL server Import and Export Wizard). I used the this tutorial which worked great for me even in Sql 2008 and excel 2010 (14.0)

I hope this helps

-D

Uitlander answered 8/3, 2012 at 20:55 Comment(0)

S

0

First of all, try the 32 Bit Version of the Import Wizard. This shows a lot more supported import formats.

Background: All depends on your Office (Runtimes Engines) installation.

If you dont't have Office 2007 or greater installed, the Import Wizard (32 Bit) only allows you to import Excel 97-2003 (.xls) files.

If you have the Office 2010 and geater (comes also in 64 Bit, not recommended) installed, the Import Wizard also supports Excel 2007+ (.xlsx) files.

To get an overview on the runtimes see 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

Strongbox answered 14/1, 2016 at 8:10 Comment(0)

Recommended topics

Hot tags