[2005] Very strange Excel problem...

Ok,

I have a web application that reads in data from an Excel spreadsheet. Seems to work fine, but I'm getting some strange behavior from one of the Excel columns.

Here is the scenario:
A user uploads a spreadsheet, the app reads in the data from all the columns. On this particular column, called Relo, if there is at least one value for this column within the first few rows, the rest of the rows with a value for this column will be read as well. However, if there is no value in the column after the first few rows, I always get an empty string for the value in later rows, even if there is a legitimate value in the column.

Has anyone ever encountered this? I cannot for the life of me figure out what could be potentially causing this behavior.

Edit: Don't know if it matters any, but we're using Office 2003.

what connectionstring are you using?

I'm not sure what you mean.

how are you connecting to excel to read data from it? What code do you have to do this?

Is this what you are referring to?

Dim objExcel As New OleDbCommand("SELECT * FROM [User_Upload$]", objConn)
Dim Excelreader As OleDbDataReader = objExcel.ExecuteReader()

not quite... what is objConn. what value is that?

Well, we have discovered a solution.

At the end of the connection string, I put IMEX=1. I'm not completely satisfied with this though, since it declares everything on the spreadsheet as text (from what I've been told). Luckily all the app cares about is the values, not format, so we're in the clear this time.

Thanks for your help techno. Once I found the solution, it was then I finally figured out why you wanted me to show the connection string.

:) This is what i was trying to aim at... hehe :)

From what I know the problem could be caused because excell determines the dataType of each column from the first few entries in each column. This can be problematic as you discoverd.