You must Sign In to post a response.
Category: SQL Server
#345414
try it........
SELECT * INTO yourtablename
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\test.xls', 'SELECT * FROM [Sheet1$]')
d:\test.xls is your excel file path
Sheet1 is name of your sheet of excel workbook
/*hope it will help you*/
Many Thanks
Tejinder Singh Barnala
/*I have the simplest tastes. I am always satisfied with the best*/
SELECT * INTO yourtablename
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\test.xls', 'SELECT * FROM [Sheet1$]')
d:\test.xls is your excel file path
Sheet1 is name of your sheet of excel workbook
/*hope it will help you*/
Many Thanks
Tejinder Singh Barnala
/*I have the simplest tastes. I am always satisfied with the best*/
#345470
Thanks for your quick response.Here users could export excel files
from client machine to web server.So I can't run the above query from database(stored procedure).I will be thankful if you tell how to use
SSIS.
from client machine to web server.So I can't run the above query from database(stored procedure).I will be thankful if you tell how to use
SSIS.
#345489
yes sure,
SSIS is a ETL(Extract Transform Load) tool and a upgraded version of SQL server 2000 DTS(Data Transformation Services)
example of some other ETL tools are Informatica and DataStage
in SSIS we create package for data extraction, transformation and loading
we can deploy SSIS package in two way, in file system or on SQL server
lets start with a simple SSIS package which copy data from one table to another table
suppose we have a employeetable and a employeetablenew, we have to transfer all data of employeetable in employeetablenew
first create employeetable for this example on your database by executiong this script
CREATE TABLE [dbo].[EmployeeTable](
[EmpId] [bigint] NOT NULL,
[EmpName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [varchar](255) )
go
/*creating employeetablenew*/
CREATE TABLE [dbo].[EmployeeTableNew](
[EmpId] [bigint] NOT NULL,
[EmpName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [varchar](255) )
go
/*populating sample data in table*/
declare @counter as int
set @counter = 1
declare @insert as varchar(4000)
while @counter <> 100
begin
set @insert = 'insert into [EmployeeTable] ([EmpId], [EmpName], [Address], [Email])
values('+cast(@counter as varchar) +', ''Test' +cast(@counter as varchar) + ''',''TestAD' +cast(@counter as varchar) + ''',''TestEM' +cast(@counter as varchar) + ''')'
exec(@insert)
set @counter = @counter + 1
end
go
/*check inserted data, there is 99 record for you*/
select * from [EmployeeTable]
go
/*now let move to SSIS*/
1. go to programs->sql server 2005->business inteligence devlopment studio
2. go file->newproject->integration service project and click ok
3. go to view->select tool box
4.from tool box drag and drop data flow task in Control Flow area
5.Now double click on data flow task (now you are in data flow area)
6. now drag and drop oledb source from toolbox to data flow area
7. right click on Oledb Source Task and select Edit
8.click New (for new connection manager there) then again click new
9.enter you servername for servername property (for eg. if your server is without instance and its name is testing then testing if you have instanced server and instance name is test then testing\test)
10.select sql authentication mode and enter username and password and check save password
11. now select yopur database from database dropdownlist
12.click testconnection, if succeed then click ok else send me the error :)
13.again click ok
14.select your table from list of table or view
15.click ok
16. now drag and drop oledb destination from tool box to dataflow area
17 select ole db source you see there two arrow line one is of green colour and one of red colour strech green line from oldeb source to oledb destination
18. now both are connect with green line if not then try again
19.now right click on oledb destination , select edit
20. click New (for new connection manager there) then again click new
21.enter you servername for servername property (for eg. if your server is without instance and its name is testing then testing if you have instanced server and instance name is test then testing\test)
22.select sql authentication mode and enter username and password and check save password
23. now select yopur database from database dropdownlist and then click ok, ok
24.select mappings and click ok (they are automatcilay create by sql server if not map them manually)
25.now your first package for tranfering all data of employeetable to employeetablenew is created
26. for testing package , go to solution explorer-> right click on your package and select execute package, if all control are green then your package succeed else fail(hope,no chance to fail), or you can go to progress tab and watch all process
/*creation of package is complete*/
/*i will describe deployment of package after receiving my thanks :)*/
for information about control please visit my blog http://sqlwithtejinderbarnala.blogspot.com/
/*hope it will help you*/
Many Thanks
Tejinder Singh Barnala
/*I have the simplest tastes. I am always satisfied with the best*/
SSIS is a ETL(Extract Transform Load) tool and a upgraded version of SQL server 2000 DTS(Data Transformation Services)
example of some other ETL tools are Informatica and DataStage
in SSIS we create package for data extraction, transformation and loading
we can deploy SSIS package in two way, in file system or on SQL server
lets start with a simple SSIS package which copy data from one table to another table
suppose we have a employeetable and a employeetablenew, we have to transfer all data of employeetable in employeetablenew
first create employeetable for this example on your database by executiong this script
CREATE TABLE [dbo].[EmployeeTable](
[EmpId] [bigint] NOT NULL,
[EmpName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [varchar](255) )
go
/*creating employeetablenew*/
CREATE TABLE [dbo].[EmployeeTableNew](
[EmpId] [bigint] NOT NULL,
[EmpName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [varchar](255) )
go
/*populating sample data in table*/
declare @counter as int
set @counter = 1
declare @insert as varchar(4000)
while @counter <> 100
begin
set @insert = 'insert into [EmployeeTable] ([EmpId], [EmpName], [Address], [Email])
values('+cast(@counter as varchar) +', ''Test' +cast(@counter as varchar) + ''',''TestAD' +cast(@counter as varchar) + ''',''TestEM' +cast(@counter as varchar) + ''')'
exec(@insert)
set @counter = @counter + 1
end
go
/*check inserted data, there is 99 record for you*/
select * from [EmployeeTable]
go
/*now let move to SSIS*/
1. go to programs->sql server 2005->business inteligence devlopment studio
2. go file->newproject->integration service project and click ok
3. go to view->select tool box
4.from tool box drag and drop data flow task in Control Flow area
5.Now double click on data flow task (now you are in data flow area)
6. now drag and drop oledb source from toolbox to data flow area
7. right click on Oledb Source Task and select Edit
8.click New (for new connection manager there) then again click new
9.enter you servername for servername property (for eg. if your server is without instance and its name is testing then testing if you have instanced server and instance name is test then testing\test)
10.select sql authentication mode and enter username and password and check save password
11. now select yopur database from database dropdownlist
12.click testconnection, if succeed then click ok else send me the error :)
13.again click ok
14.select your table from list of table or view
15.click ok
16. now drag and drop oledb destination from tool box to dataflow area
17 select ole db source you see there two arrow line one is of green colour and one of red colour strech green line from oldeb source to oledb destination
18. now both are connect with green line if not then try again
19.now right click on oledb destination , select edit
20. click New (for new connection manager there) then again click new
21.enter you servername for servername property (for eg. if your server is without instance and its name is testing then testing if you have instanced server and instance name is test then testing\test)
22.select sql authentication mode and enter username and password and check save password
23. now select yopur database from database dropdownlist and then click ok, ok
24.select mappings and click ok (they are automatcilay create by sql server if not map them manually)
25.now your first package for tranfering all data of employeetable to employeetablenew is created
26. for testing package , go to solution explorer-> right click on your package and select execute package, if all control are green then your package succeed else fail(hope,no chance to fail), or you can go to progress tab and watch all process
/*creation of package is complete*/
/*i will describe deployment of package after receiving my thanks :)*/
for information about control please visit my blog http://sqlwithtejinderbarnala.blogspot.com/
/*hope it will help you*/
Many Thanks
Tejinder Singh Barnala
/*I have the simplest tastes. I am always satisfied with the best*/
#345542
Many thanks Tejinder.Here i have followed your steps except source part since i am exporting excel sheet. so i have use as excel data source.Now i want to know how to call this package in stored procedure and made some validation through script before import table.
#345624
Hi
The OPENROWSET feature in SQL Server and MSDE provides a fast and easy way to open an OLE DB compatible data source, such as an Excel sheet, directly from your SQL script. Coupled with the "SELECT * INTO" command, the OPENROWSET feature can import data from an Excel sheet into a table in SQL Server or MSDE.
Run the following command from the SQL window in Teratrax Database Manager. This example uses the OLE DB Provider for Microsoft Excel to access sheet1 in the Excel file c:\book1.xls.
SELECT *
INTO db1.dbo.table1
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls',
'SELECT * FROM [sheet1$]')
table1 will be created in the db1 database. The content of this table will be imported from the sheet1 worksheet in your c:\book1.xls Excel file.
This is very simple you dont have follow all sorts of complex logics.
Thanks -- Vijaya Kadiyala
http://www.DotNetVJ.com
Microsoft MVP
The OPENROWSET feature in SQL Server and MSDE provides a fast and easy way to open an OLE DB compatible data source, such as an Excel sheet, directly from your SQL script. Coupled with the "SELECT * INTO" command, the OPENROWSET feature can import data from an Excel sheet into a table in SQL Server or MSDE.
Run the following command from the SQL window in Teratrax Database Manager. This example uses the OLE DB Provider for Microsoft Excel to access sheet1 in the Excel file c:\book1.xls.
SELECT *
INTO db1.dbo.table1
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls',
'SELECT * FROM [sheet1$]')
table1 will be created in the db1 database. The content of this table will be imported from the sheet1 worksheet in your c:\book1.xls Excel file.
This is very simple you dont have follow all sorts of complex logics.
Thanks -- Vijaya Kadiyala
http://www.DotNetVJ.com
Microsoft MVP
#345823
Thanks Vijaya.
Now i am getting follwoing error message when run the script which you are mentioned above.
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.
Thanks in advance.
Now i am getting follwoing error message when run the script which you are mentioned above.
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.
Thanks in advance.
Return to Return to Discussion Forum