Upload Excel file and write cell values to table

[sajuks]

This is similar to the one which i have posted in the PHP area.

Hi All,
 We are looking for an upload script which will upload an excel file and then insert the contents of the cell to a table in the database.
We are using ASP with SQL Server.
The excel file contains the data in Sheet1.
The excel sheet contains headers.
Sample Output would be

Description From Date         To Date
Test A         12/2/2006       14/2/2006
Test B         2/2/2006       14/2/2006
Test C         1/12/2006       14/2/2006
Test AB         25/9/2005       4/12/2005
Test AC         6/9/2005       1/2/2006

The SQL Server table structure is
id identity
desc varchar
fdate datetime
tdate datetime
uploaddate datetime

The script should allow the user to browse to the file and then clicking on upload/save should insert the
values to the said table.
How do i code for such a scenario.

Tnx

[amit_g]

Do you still need help with this?

[sajuks]

We had to look for other alternatives as we wanted this on an urgent basis. Now we would be happy to know how to proceed with it for our understanding and reference and more possibly future use.

[VGR]

well, as far as you problem stands, neither PHP, ASP, JSP oe the like will express any difference. The problem lies in the platform (open or not), protocol, proprietary stuff or not (COM not to name it), and your ability of making the process easier by "formatting" the input data (XLS vs CSV)

[amit_g]

Just looked at your same question in PHP and the first comment is the solution in ASP also except that in ASP, we can directly open the Excel as a database. So it is 2 step process. First upload the file to the server which is quite simple and then opening that Excel as database. The only requirement is that the Excel file MUST be in exact same format and the sheet MUST have a fixed name (it could be default but it has to be same). The connection string to be used for connection is

 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\UploadFolder\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""