Best way to import data from Excel & save to DB?

[Jeevi]

hi,
i am downloading data from some other source to Excel workbook. after that, i just want to save all those data to DB after performing some validations.
at the moment, i am using the following code to import data from excel into my Dataset.

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] App [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Excel.Application[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] wkb [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Excel.Workbook[/SIZE]
[SIZE=2]App = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Excel.Application[/SIZE]
[SIZE=2]wkb = App.Workbooks.Open(PathToExcelFile)[/SIZE]
[SIZE=2]SheetCount = wkb.Worksheets.Count[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbConnection[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbDataAdapter[/SIZE]
[SIZE=2]DtSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.DataSet[/SIZE]
[SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] i [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 1 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] SheetCount[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] SheetName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = wkb.Worksheets(i).Name[/SIZE]
[SIZE=2]MyConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbConnection([/SIZE][SIZE=2][COLOR=#800000]"provider=Microsoft.Jet.OLEDB.4.0; "[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
 [SIZE=2][COLOR=#800000]"data source='"[/COLOR][/SIZE][SIZE=2] & PathExcelFile & [/SIZE][SIZE=2][COLOR=#800000]" '; "[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"Extended Properties=Excel 8.0;"[/COLOR][/SIZE][SIZE=2]) [/SIZE]
[SIZE=2]MyCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbDataAdapter([/SIZE][SIZE=2][COLOR=#800000]"select * from ["[/COLOR][/SIZE][SIZE=2] & SheetName & [/SIZE][SIZE=2][COLOR=#800000]"$]"[/COLOR][/SIZE][SIZE=2], MyConnection)[/SIZE]
[SIZE=2]MyCommand.TableMappings.Add([/SIZE][SIZE=2][COLOR=#800000]"Table"[/COLOR][/SIZE][SIZE=2], TableName)[/SIZE]
[SIZE=2]MyCommand.Fill(DtSet) [/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]

i can succesfully do this when the data in Excel sheets are limited. let say, if the workbook has 175 sheets and each sheet has about 3000 records (this operation takes about 20 mins to import into my dataset), but if it exceeds then the framework throws error that says "workbook has been lost.. -some thing like that". if i imported into my dataset successfully, then no problem in saving data from dataset into db (but it takes another 15 mins.)

i would like to know, is that anyother way to accomblish this task with less time and improved performance? or anyother easy way to do this?
any help would be greately appreciated.
thanks.

 

[cjard]

175 sheets of 3000 records is nearly half a million rows. The logic of using Excel in such a fashion is highly questionable; its a spreadsheet, not a database
I dont recommend you try to read it into memory all at once.. Do on-the-fly data processing via a datareader rather than a datatable.