Read an excel uploaded through FileUpload

Question

I have an ASP.NET web application that has a fileupload control to allow users to upload an excel file for it to read.

I'm using an OleDBConnection and I feel like my connection string or querystring is wrong since the app keeps throwing OleDBExceptions at me (ouch!). What would be a good way to save or access the uploaded files?

Here's the connection string:

olcon.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(fileupFile.FileName) + ";Extended Properties=Excel 12.0";

Query string:

OleDbCommand olcmd = new OleDbCommand("SELECT * FROM [Sheet1$]", olcon);

Answer 1

I suspect the issue is rooted in the call to Server.MapPath(fileupFile.FileName). The FileName property is a convenience property to indicate the original file name of the content being uploaded to the server. It doesn't necessarily indicate the location or the name of the file when (and if) you save it on the server.

It would seem that you still need to receive the file on the server side and save it to disk. There are several ways to do so. The SaveAs() method is perhaps the easiest, but you also have the FileBytes and FileContent properties which give you access to the uploaded file as a byte array or a stream, respectively.

If you first save the document to your hard drive and then construct your connection string, I believe that will resolve the issue.

Here is a simple example of saving an uploaded file. Assume that the "~/Documents" folder has granted the IIS worker process write permissions (or that you use impersonation and your authenticated user has write permissions):

var targetfilepath = Path.Combine(Server.MapPath(~/Documents), fileupFile.FileName);
fileupFile.SaveAs(targetfilepath);

var connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + targetfilepath + ";Extended Properties=Excel 12.0";
// ...

Good luck.

By the way, and you may already know, there is an excellent reference on Excel 2007 connection string properties at connectionstrings.com: http://connectionstrings.com/excel-2007