OleDbDataAdapter and Excel: Why are some of the column values missing?

Ran into this recently, writing it down in case I forget.

I had an excel spreadsheet that I had been importing for quite a while (meaning, a job that had been working for months on daily submitted spreadsheets) and I wanted to add new columns that were present, so I did all the necessary c# work, but then when I ran the updated project, the values were blank, though I verified they were present in the spreadsheet.

It turns out to be related to the fact that the columns were sparsely populated and how the connection string was set up.  The code will try to ‘guess’ data types and whatnot depending on what it sees in the first few rows, so since the columns I was interested weren’t populated in the first few rows, it was missing them later.

You need to add extended properties, like this:

Extended Properties='Excel 8.0;HDR=YES;IMEX=1'

And it is important to put the single-quotes around them, otherwise you will get a “could not find installable ISAM” error message.

posted on Thursday, June 05, 2014 11:46 AM Print
No comments posted yet.

Post Comment

Title *
Name *
Comment *  
Please add 4 and 4 and type the answer here: