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?
This question is related to
sql-server
excel
sql-server-2008
import
etl
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.
Microsoft suggest several methods:
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;
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
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
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.
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
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
The best tool i've ever used is http://tools.perceptus.ca/text-wiz.php?ops=7 Did you try it?
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 ...
Microsoft suggest several methods:
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;
I think it will help you
Microsoft suggest several methods:
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;
The best tool i've ever used is http://tools.perceptus.ca/text-wiz.php?ops=7 Did you try it?
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
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 ...
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.
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
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.
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
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.
I think it will help you
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
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.
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.
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
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.
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.
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.
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.
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.
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
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:
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.
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.
Microsoft suggest several methods:
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;
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
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.
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
Source: Stackoverflow.com