Kevin Pope Kevin Pope - 17 days ago 6
C# Question

ODBC Connection to Excel file - override determined data type

There are a couple other questions on this subject, but the answers I've seen reference the Text file driver, whereas I'm using an xlsx file. Anyway, here's the issue:

Is there a way to override the ODBC-determined data type in a C# program pulling data from Excel? The data type is coming through as a number seemingly because the row scan is showing most of the data as a number, whereas it's meant to be processed as alphanumeric. All text data is coming through as NULL with the

ODBCDataReader.GetValue(int)
method.

Here's the basic structure of the code:

public static void test()
{
OdbcDataReader DataReader;
try
{
using (OdbcConnection ODBC_Connection = new OdbcConnection("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + filename))
{

ODBC_Connection.Open();
OdbcCommand Command = new OdbcCommand("SELECT * FROM [" + sheetName + "]", ODBC_Connection);

Command.CommandType = System.Data.CommandType.Text;
DataReader = Command.ExecuteReader();

while (DataReader.Read())
{
field0 = DataReader.GetValue(0).ToString();
field1 = DataReader.GetValue(1).ToString();
field2 = DataReader.GetValue(2).ToString();
field3 = DataReader.GetValue(3).ToString();
field4 = DataReader.GetValue(4).ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
}


field4
is the field in question, and all the data for the other fields are passing properly through the
.GetValue(int)
method.

How can I override the data type for this field prior to the SELECT statement? Thanks!

Answer

It's been a while, but I believe I resolved this by using an OLEDB connection instead of ODBC.

Comments