Microsoft.Jet.OLEDB Extended Properties

[mcljava]

I recently started a C# prototype using the OLE DB access method to read from a hefty Excel spreadsheet that we use at the office. The nice thing about OLE DB is you can read Excel worksheets pretty easily, and in block read style. H O W E V E R, when was the last time you saw a clean error free, perfectly formatted spreadsheet? That's where I am stuck. It seems the OLE DB driver/JET engine will happily retrieve the specified query range but it also supplies a null for any cell it doesn't like. Is there a way to override this behavior and tell Jet.OLEDB through the ExtendedProperties to retrive all data as is? Currently I have specfied Imex=2 but that does not seem to be getting it done. FYI, here is one way to read a worksheet in one read. In my case I know there are roughly 330-370 rows per worksheet: // open the JET connection OleDbConnection con = new OleDbConnection( connectionOpts ); con.Open(); // build the query string query = string.Format( "SELECT * FROM [{0}$[1]]", tableName, queryRange ); // fill the dataset OleDbDataAdapter oleAdapter = new OleDbDataAdapter(); oleAdapter.SelectCommand = new OleDbCommand( query, con ); DataSet ds = new DataSet(); int rowsProcessed = oleAdapter.Fill( ds, "data" ); // free up resources oleAdapter.Dispose(); oleAdapter.Close(); etc. Since this is such a simple way to read the data, now all I need is a way to ensure it gets ALL of the data. Mike Luster CTI/IVR/Telephony SME

[mcljava]

OK I know the PROBLEM, see the link: http://support.microsoft.com/default.aspx?scid=kb;en-us;194124 Mike Luster CTI/IVR/Telephony SME