Best practice to transfer data from Excel file to SQL Server 2000

[GD]

Hi,

I am trying write a web application using ASP.Net to load an Excel file from
client site and save all the data in the file to a table in SQL Server 2000.

My current assumption is: load Excel data to an ADO.Net dataset, or a
datareader, then pull the data out from them and insert the data to a SQL
Sever table.

I wonder if there is any better way to transfer the Excel data to SQL
Server.

Thanks for any hints.

GD

 

[WJ]

You would want to use the "Import/Export Utility" shipped with the SQL
Server CD. This is the best tool I see so far. I use this daily! On top of
this, the tool also aloows you to schedule the job to run anytime you wish.

John

 

[Guest]

Do you need the information from the Excel document in SQL or just the
document itself?

If you need to just save the doc use the file upload and instead of saving
the file to disk put into a binary stream and save to sql.

If you need to extract the info to insert into SQL Server, same as above
except you can transform and read anyway you like, Excel object lib, dataset,
SQL DTS

If you want an example let me know, for either.

 

[W.G. Ryan eMVP]

I agree with WJ - Datasets aren't the best data transfer mechanism - you can
use the ImportExport or write a DTS package - very easy to do and very fast

 

[GD]

What I am working on is a web-based client-side application. The clients
don't have the permission to use the Import/Export Utility in SQL Server
directely. However, they are granted permision to perform database
transaction through web application.

Thanks for reply anyway.

GD

 

[GD]

I need to simply load all data from an Excel file chosen by end users, and
then insert the data to a SQL Server table that has exact same data columns
defined as the Excel file. I don't need to use the document itself. This has
to be done in a web-based application. I would appreciate it very much if
you can provide me with sample codes.

Thanks in advance.

GD

 

[Paul Clement]

¤ Hi,
¤
¤ I am trying write a web application using ASP.Net to load an Excel file from
¤ client site and save all the data in the file to a table in SQL Server 2000.
¤
¤ My current assumption is: load Excel data to an ADO.Net dataset, or a
¤ datareader, then pull the data out from them and insert the data to a SQL
¤ Sever table.
¤
¤ I wonder if there is any better way to transfer the Excel data to SQL
¤ Server.
¤
¤ Thanks for any hints.

If the Excel file is on the client side then it will likely need to be uploaded to the server first,
or a network share where it is accessible.

http://www.stardeveloper.com/articles/display.html?article=2003031201&page=1
http://www.aspheute.com/english/20000802.asp

After the file is uploaded there is a good possibility that you can use SQL to insert the Excel data
into an existing SQL Server table.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)