Import Excel spreadsheet columns into SQL Server database
Solution 1
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.
Solution 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;
Solution 3
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
Solution 4
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.
Solution 5
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
Admin
Updated on July 14, 2020Comments
-
Admin almost 4 years
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?
-
Admin over 15 yearsIt didn't give me an option to import into an existing table, just the database and it would create a new table. (SQLServer 2008)
-
Admin over 15 yearsHmm. 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.
-
Admin over 15 yearsnO WAY TO ACCESS MY LOCAL MACHINE?
-
Admin over 15 yearsFound 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.
-
DCNYAM over 15 yearsI haven't used management studio 2008 yet (we're running 2005). Management studio 2005 is backward compatible with 2000.
-
marc_s about 15 yearsWell, 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....
-
Techboy over 12 yearsWhen I try this, I get the error 'Syntax error in FROM clause. (Microsoft JET Database Engine)'
-
Suncat2000 over 12 yearsUse 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 }:-(.
-
VenerableAgents about 12 yearsThis (link) is a similar method I use for inserting data into MySQL (same method can be used here)
-
user1568901 over 11 yearsTasks -> Import Data does not exist in that menu on 2008.
-
ncubica about 11 yearsYou can try this download file microsoft.com/en-us/download/details.aspx?id=13255 for 64x I haven't tested but look promise..
-
Ash Machine about 10 yearsUsing SQL Server 2008 (not R2) I do see Tasks --> Import Data when right clicking on a DB.
-
Nick.McDermaid almost 10 yearsThis 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.
-
David Sopko over 9 yearsI get the error, Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
-
SqlACID over 9 yearsWhat OS? If it's old, you may need to install MDAC