Oledb reading multiple excel files on server causes type error

Question

Initially I had an issue with the data type "guesses" when dealing with the jet driver (through oledb). If a sheet had mixed types, it would bring in null/empty values.

-Edit-

There is an IMEX setting in the connection string as well as in the registry that will tell jet/ace to use text for columns with multiple data types. This way if the first 6 rows have an integer value and the 7th cell has a text value, there won't be a type cast failure. There is also a setting in the registry (and connection string) that will allow you to say how many rows jet should use for sampling.

-end edit-

I changed the connection string, and the registry settings on the server. So now the program is reading fine. It will read values as text, and not use {n} rows for sampling. I thought it was working fine.

Now I have a data source that lists files in order to be read. If I have multiple files in there, it will have the same type casting issues... or at least the same symptoms. If I upload the files one at a time without using the queue then it works fine. It's when I have multiple files in a row that it seems to have the type casting issue.

I'm not really sure what is causing this to happen when reading multiple files in a row, but not when reading one at a time. The connection opens, reads all the data, and then closes... so I don't think it has to do with that.

I am just looking for any ideas ? It was hard enough to find the original problem. Working with Jet seems to be asking for a butt ache.

---

Added relevant code as per request

public static readonly String CONNECTION_STRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data   Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES; ReadOnly=True;IMEX=1;\"";

private System.Data.DataTable Query(String worksheetName, String selectList = "*")
{
    DataTable table = new DataTable();

    _connection.Open();
    var query = String.Format(Constants.DATA_QUERY, selectList, worksheetName);
    new OleDbDataAdapter(query, _connection).Fill(table);
    _connection.Close();
    return table;
}

Answer 1

I'd recommend using a native library if possible, something like Excel Data Reader or EPPlus instead of OLEDB