Import Excel sheet using ASP.net web application

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

Answer this question

Import Excel sheet using ASP.net web application

Tryin2Bgood

Using the ASP.NET app to process the import may not be the best idea given the size of files being imported. The potential for timeouts is significant. Performing the upload using the HTMLFileControl should be fine.

I would recommend uploading the file to the server first and then performing the import via Jet OLEDB (not ODBC) for Excel and the native .NET SQL Server namespace library. Since performing the update/insert row by row will require a significant amount of time you will be much better off doing this in a background batch process or Windows service application.
le tan duc
I believe using SQL DTS will work much faster in this case. Iterating will be extrememly slow because it will require to execute individual SQL statements for each row. DTS will do it in a batch
ChSchmidt
One more option. If ou are using VS 2005, then you could query the Excel file first and then upload data to SQL Server usin SqlBulk class. It will do upload in a batch as well and will definitely improve performance
Import Excel sheet using ASP.net web application

Answer this question