Exporting Excel Data To SQL 2000 through asp.net

[amitshinde02]

Hi Experts,

I have been struggling on this problem since last one month. I have a
huge Excel sheet (Not well formatted) with around 10000 records or
more. with around 60 fields.
I want to transfer those rows in sql server as it is. The table in SQL
has got diffrenter field names than this excel field names.

It has got Title of the Sheet in A1
Sub title in A2.
Then 3-4 Blank rows below it.
Then the header row starts from column 3. i.e. From C-5.

Currently I am looping through all the records of the datatable Dataset
in which excel is read using odbc. and in a loop i am calling a stored
procedure for inserting one record at a time.
So process is very slow.
Is there any other way to export data from excel to sql; I can not use
DTS as sheet is selected by the user.

One more way I have tried i.e. I have converted dataset into xml using
DataSet.GetXML() method. and passed the xml as string to sql . open xml
method in sql i tried to insert the records which also taking same
time.
Kindly help
Amit

 

[nshanmugaraj]

hi,
convert the excel to xml using dataset.writexml proper and try....

hope u get the solution

 

[amitshinde02]

I have tried the same way but its taking almost the same time.