Quirky SELECT from Excel file via OleDbDataAdapter method (C#) -

i have got excel file in form :

column 1    column 2    column 3    data1        data2      data1        data2    data1        data2    data1        data2    data1        data2       data3   

that is, whole column 3 empty except last row. accessing excel file via oledbdataadapter, returning datatable: here's code.

query = "select * [" + query + "]"; objdt = new datatable(); objcmdsql = this.getcommand(); objcmdsql.commandtext = query; objsqldad = new oledbdataadapter(objcmdsql); objsqldad.fill(objdt); return objdt; 

the point is, in scenario code returns datatable column 1 , column 2.
guess jet engine tries infer column type type of first cell in every column; being first value null, whole column ignored.
tried fill in zeros , code returning 3 columns; least preferable solution because have process large numbers of small files.
inverting selection range (from, i.e. "a1:c5" "c5:a1" ) doesn't work either. i'm looking more elegant.
have found couple of posts discussing type mismatch (varchar cells in int columns , vice versa) haven't found related one.
reading!

edit

weird behavior again. have work on excel 2003 .xls files, since question has been answered thought test code against excel 2007 .xslx files. connection string following:

string strconn = @"provider=microsoft.ace.oledb.12.0; data source=" + _filename.trim() + @";extended properties=""excel 12.0;hdr=no;imex=1;"""; 

i "external table not in expected format" exception reckon standard exception when there version mismatch between ace/jet , file being opened.

the string

provider=microsoft.ace.oledb.12.0  

means using recent version of oledb, took quick peek around , version used everywhere there need of connecting .xlsx files.
have tried vanilla provider ( excel 12.0, without imex nor hdr ) same exception.
on .net 2.0.50727 sp2, maybe time upgrade?

i recreated situation , following returned 3 columns correctly. is, first 2 columns populated data , third containing null until last row, had data.

string connstring = @"provider=microsoft.ace.oledb.12.0;data source=c:\myexcel.xls;extended properties=""excel 8.0;hdr=no;imex=1"";"; datatable dt = new datatable(); oledbconnection conn = new oledbconnection(connstring); oledbdataadapter adapter = new oledbdataadapter("select * [sheet1$]", conn);  adapter.fill(dt); 

note used access database engine(ace) provider, succeeded old joint engine technology(jet) provider, , results may represent behavior difference between two. of course, if aren't using suggest using ace provider believe microsoft too. also, note connection's extended properties:

"hdr=yes;" indicates first row contains columnnames, not data. "hdr=no;" indicates opposite.

"imex=1;" tells driver read "intermixed" (numbers, dates, strings etc) data columns text. note option might affect excel sheet write access negative.

let me know if helps.