I'm using the OleDB DataReader to get data from my excel files (but this problem occurs in DataTable.Fill as well). The thing is that I have a column that should return strings. All's fine and working but recently, an issue came up, because the column's cells had different formats. Some were in numbers and others in text. When I checked using dataReader.GetSchema() it showed that the column in question was inferred as type System.String. The problem with this was that all non-text cells were immediately set to null.
Is there a way to suggest to the reader that that column should just parse the columns as System.Object instead of inferring it as System.String and dumping all non-string cells?
The connection string I'm using is:
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";
and the code is:
using ( OleDbConnection connection = new OleDbConnection( connString ) )
{
connection.Open();
foreach ( string worksheetName in worksheetNames )
{
using ( OleDbCommand command =
new OleDbCommand( "SELECT * FROM [" + worksheetName + "]", connection ) )
{
TEntity entity;
using ( OleDbDataReader dataReader = command.ExecuteReader() )
{
while ( dataReader.Read() )
{
entity = GetDataFromDataTable( dataReader );
if ( entity != null )
{
entityList.Add( entity );
}
}
}
}
}
connection.Close();
}