Using OleDB to get Excel Sheet Data, but it's "randomly" not reading cell data of a row

Question

I'm currently attempting to extract certain data from thousands of excel files. But this problem is driving me absolutely crazy and I'm in a time-crunch to get this all of these done before a short deadline.

The problem is that it doesn't seem to be retrieving ALL of the data, in particular, these "option" columns as I call them. It's supposed to have -01 all the way to -10 for this particular sheet. These are the only values on the entire sheet that are sometimes not read in. They are sometimes slanted at a 45 degree angel, but it doesn't seem to make a difference if I change their orientation manually before reading the file in.

This is what it's doing:

This is the excel file in question (not sure if that link will work):
https://drive.google.com/file/d/0B7-iwpcLS0GoaVpHYkxPTWdIYUE/view?usp=sharing

Direct download link if that Google spreadsheet fails:
http://www.filedropper.com/123456_1

This is the basic code I'm using ripped out of my application:

Dim xlAppIn As New Excel.Application
Dim xlWorkbookIn As Excel.Workbook = xlAppIn.Workbooks.Open(sFilename)
xlAppIn.Visible = False

Dim oleDbConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & sFilename & ";Extended Properties='Excel 12.0; HDR=No; IMEX=1'"
Dim oleDbConn As OleDbConnection = New OleDbConnection(oleDbConnStr)
oleDbConn.Open()

Dim sSheetName As String = xlWorkbookIn.Sheets(1).Name.ToString()
txtStatus.Text = sSheetName

'Dim oleDbCmd As New OleDbCommand("SELECT * FROM [" & sSheetName & "$B5:AI46]", oleDbConn)
Dim oleDbCmd As New OleDbCommand("SELECT * FROM [" & sSheetName & "$]", oleDbConn)
Dim oleDbDa As New OleDbDataAdapter(oleDbCmd)

Dim dt As New DataTable
oleDbDa.Fill(dt)
dgvRaw.DataSource = dt

What am I doing wrong here?

Answer 1

I've finally figured it out, but I'm not sure why it matters.

I had to format the cells to text inside of Excel and save it before attempting to load the file with my program. Some of the cells I guess were not formatted as text, so it would just not bother reading it in.

I even tested without IMEX=1 and the same thing happened.

---

Now it'd be nice if there was a way to automate that in a quick fashion. e.g.: open the file, format all sheets text, and then read from it.

Figured that out as well...

'nSheetPos being iterator for looping through the sheets in the workbook
xlWorkbookIn.Sheets(nSheetPos).Columns.NumberFormat = "@"
xlWorkbookIn.Save()