Why is OleDB ignoring Excel cells?

Question

Here's the setup:

I have an excel spreadsheet that has a very simple page. It looks like so: Image redacted, of course

I use the following connection string to access this file:

string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO\";", fn)

My function to access the file looks like:

try
{
    string select = string.Format("SELECT * FROM [{0}$]", tab.PageName);
    OleDbDataAdapter adapter = new OleDbDataAdapter(select, con);
    DataSet ds = new DataSet();

    adapter.Fill(ds, tab.PageName);


    // DEBUG: Let's just see what it is getting...
    for (int x = 0; x < 13; x++)
    {
        for (int y = 0; y < 3; y++)
        {
            Console.Write(ds.Tables[0].Rows[x][y].ToString() + "\t");
        }
        Console.WriteLine("");
    }
} 
catch
{ ... }

QUESTION

Why would the code NOT read some cells? Note that there is the text "Profit" at C5. I can read B5 just fine as "Revenue". I can read C6 just fine as an integer value. But Profit seems to vanish.

This isn't such a big problem with the header information, but entire blocks of real data refuse to be read. Instead it returns DBNull, even when the cell contains real, valid, usable data. The cells are all formatted exactly the same between cells that can read and cells that return DBNull.

I'm truly stumped!!!

Any thoughts?

What happens when you put the `MonthQuarter, Revenue, Profit` table into its own sheet? — rlb.usa, Sep 14 '11 at 22:30
if I copy just the yellow header to a new page and query just that page, it reads fine. If I copy the entire block from Month/Quarter to C13, it doesn't read profit again. If I copy just a portion of the grid, and use that, it suddenly loses "Revenue" as well. — Jerry, Sep 14 '11 at 22:40

score 2 · Accepted Answer · edited May 23 '17 at 12:07

2

 new OleDbConnection("...TypeGuessRows=0;ImportMixedTypes=Text");

I have a hunch you may be experiencing a problem that I had previously.

Try adding those parameters to your connection string.

edited May 23 '17 at 12:07

Community

1
1

answered Sep 14 '11 at 23:47

rlb.usa

14,942
16
80
128

1

You were VERY close. ImportMixedTypes didn't do it, but it did get me on the right track. I added IMEX=1 to the extended properties, and it worked. – Jerry Sep 15 '11 at 13:19
I get "Could not find installable ISAM." on connection.Open() anytime I try any of these settings :/ Same issue other's mention here: http://jingyangli.wordpress.com/2009/02/13/imex1-revisit-and-typeguessrows-setting-change-to-0-watch-for-performance/ – AaronLS Jun 12 '14 at 22:56
@AaronLS I'm pretty sure that your problem on `connection.Open()` has to do with the connection string being incorrect. At any rate, you can open a new question to receive the individual attention that you need to resolve your *different* issue. – rlb.usa Jun 13 '14 at 16:49
@rlb.usa Thanks rlb, but I figured it out and posted it as a complete example so others don't have the same issue with *this* ***same*** question :) – AaronLS Jun 13 '14 at 20:05

AaronLS · Answer 2 · 2014-06-13T20:06:18.947

0

I was having trouble getting the other answer+comment to work. The only setting needed was the IMEX=1 but it must be nested in single quotes in the Extended Properties, so here's an example of exactly how to format the additional IMEX setting:

connection.ConnectionString = 
  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somepath\somefile.xls;Extended Properties='Excel 8.0;IMEX=1';";

edited Jun 13 '14 at 20:06

answered Jun 13 '14 at 00:40

AaronLS

37,329
20
143
202

score -1 · Answer 3 · edited Aug 08 '19 at 12:39

-1

if (ObjFile.Extension == ".xls")
    conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + srcFilePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
if (ObjFile.Extension == ".xlsx")
    conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + srcFilePath + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";
if (ObjFile.Extension == ".xlsm")
    conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + srcFilePath + ";Extended Properties='Excel 12.0 Macro;HDR=No;IMEX=1';";

edited Aug 08 '19 at 12:39

Airn5475

2,452
29
51

answered Aug 08 '19 at 11:51

bagur

1

Welcome to Stack Overflow! Thank you for this code snippet, which might provide some limited short-term help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Aug 08 '19 at 12:12

Why is OleDB ignoring Excel cells?

3 Answers3

Linked