ASP.NET OleDb Excel Connection to Worksheet

Question

I have an Excel file that the customer is pulling from their financial software and uploading to my web app. I need to connect to the file through ADO and read its contents into a SQL database.

The problem is that the file that comes from the financial software is a standalone Excel Worksheet, not Workbook, so no piece of software (besides Excel) that I've found can connect to/open it. No matter what connection string I use in the OleDB connector, I can't get it to work.

If I open the file in Excel, then simply save it, I can connect and read it fine. I wrote some code to automate Excel, using the Office interop, that opens/saves the file, and it works, but I've concluded this is bad practice to do on a server, based on testing and reading.

Does anyone know of a way I can get around this problem? I've seen some third party libraries, but they are very expensive.

Thanks in advance.

    HttpPostedFile jvFile = FileUpload1.PostedFile;
string jvPath = Path.Combine(Server.MapPath("~"), Path.GetFileName(jvFile.FileName));
jvFile.SaveAs(jvPath);

string[] begins = {
                      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=",
                      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=",
                  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=",
                  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=",
                  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=",
                  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=",
                  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=",
                  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                  };
string[] ends = {
                    ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"",
                    ";Extended Properties=\"Excel 8.0;HDR=Yes;\"",
                ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"",
                ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"",
                ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"",
                ";Extended Properties=\"Excel 12.0;HDR=YES\"",
                ";Extended Properties=\"Excel 12.0 Macro;HDR=YES\"",
                ";Extended Properties=\"text;HDR=Yes;FMT=Delimited\"",
                ";Extended Properties=\"text;HDR=Yes;FMT=Fixed\";"
                };

for(int i = 0; i < begins.Length; i++)
{
    StringBuilder sbExcelFileConnStr = new StringBuilder();
    sbExcelFileConnStr.Append(begins[i]);
    sbExcelFileConnStr.Append(jvPath);
    sbExcelFileConnStr.Append(ends[i]);

    OleDbConnection dbConn = new OleDbConnection(sbExcelFileConnStr.ToString());
    string[] excelSheets = { };
    try
    {
        dbConn.Open();
    }
    catch (Exception ex)
    {
         // fails here with "System.Data.OleDb.OleDbException: 
         // External table is not in the expected format."
         //
         //
    }
}

I can't put the problem file anywhere b/c it contains sensitive data.

Answer 1

I've used the Excel Data Reader for reading Excel files. It's free.