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;
}

Multiple files definitely NOT causing your issue. What is your logic? I don't remember needing to edit registry to read excel workbooks. Use ACE OleDB and you should have no problems reading it. Really. you wrote so much but nothing that can help to understand your issue — T.S.
@T.S. There is an IMEX setting for when excel guesses the wrong data type. So what happens is if your first 6 rows appear to be integer types, and the 10th row is text it will have a type cast failure and bring in a value of "" for the 10th row. — loctrice
You need to write your logic to parse the type. What I've done in one application, is I parsed all rows as text and then each row went through data validation and those that failed were added to a log. Later, user could go to that log and download those failed rows as Excel. User would quickly fix it [because only failed rows present] and re-upload the file. You need to work in direction of building the logic that will take care of your incompatibility issues, or use only strings. — T.S.
I have it set to read all mixed types as text. That works when it's reading a file, it's when I read multiple files in a row this seems to be ignored. I'm not allowed, in this situation, to make the user fix the file. — loctrice

Wiebe Tijsma Wiebe Tijsma · Accepted Answer · 2014-06-16T19:42:22

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

Oledb reading multiple excel files on server causes type error

2 Answers