regarding Excel Import - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

regarding Excel Import

Author

Topic

idthiru
Starting Member

6 Posts

Posted - 2006-09-15 : 08:54:02

Hi all,

hope i should have some luck.

I am struggling with importing excel sheet into remote sql database through asp.net web application.

i am now following this procedure:
1. ask the user to upload the file in the web server,
2. use opendatasource to populate excel data into a table(say tblTemp)
3. transfer data from tblTemp to tblMaster.
while doing step 3:
a. Loop through the records of tblTemp one by one.
b. check for the existence(of the record) in tblMaster
if esists then update else insert.

here is the code to populate tblTemp:
---------------------------------------
CREATE procedure usp_samp
@path varchar(1000)
as
declare @sqlQuery varchar(2000)
begin

set @sqlQuery='insert into tblTemp select * from '
set @sqlQuery=@sqlQuery+'OpenDataSource('''+'Microsoft.Jet.OLEDB.4.0'''+','''+'Data Source='+@path+';Extended properties=Excel 8.0'''+')...[Sheet1$]'

-- print @sqlQuery
if exists (select * from dbo.sysobjects where id = object_id(N'[tblTemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tblTemp]

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)

exec(@sqlQuery)
--------------------------------------------------------------
this is code that performs row by row operations:

create procedure usp_sampleInsUpdMaster
as
declare
@returnCode int,
@nextRowId int,
@currentRowId int,
@loopCounter int,
@rowCnt int,
--Fields
@AccNo varchar(50),
@MobNo varchar(50),
@Name varchar(1000)
Begin

select @rowCnt=rows from sysindexes where id=OBJECT_ID('tblTemp')and indid<2
if @rowCnt>0
begin
select @loopCounter=1
select @nextRowId=0
select @currentRowId=0
while @loopCounter=1
begin
select @nextRowId=min(rowId)
from tblTemp where rowId>@currentRowId
if isnull(@nextRowId,0)=0
begin
break
end
select @currentRowId=rowId,
@AccNo=[Account Number],
@MobNo=[Mobile Number],
@Name=[Name]
from tblTemp where rowId=@nextRowId
if exists(select * from tblDataImportExport where [Account Number]=@AccNo)
begin
Update tblDataImportExport
set [Mobile Number]=@MobNo,
[Import Date]=getdate()
where [Account Number]=@AccNo
end
else
begin
Insert into tblDataImportExport([Account Number],[Mobile Number],[Name],[Import Date])
Values(@AccNo,@MobNo,@Name,getdate())
end
end

end
else
begin
select 'No Rows'
end
End
----------------------------------------------------------------
I do these two operations using two separate stored procedures and in sequence.
1.usp_samp
2.usp_usp_sampleInsUpdMaster

the main thing is time it takes really long time and also the records are not properly inserted .

I thank all in advance

jccorner
Starting Member

20 Posts

Posted - 2006-09-15 : 16:46:53

Questions:

1. What version of SQL Server (2000)??
2. When you say records are not properly inserted are you getting dups or they're being updated when the actually don't match??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources