I use this code to load an Excel file into a datatable:
public static DataTable ImportExcelFile(string connectionString)
{
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.OleDb");
var listCustomers = new DataTable();
using (DbConnection connection = factory.CreateConnection())
{
if (connection != null)
{
connection.ConnectionString = connectionString;
using (DbCommand command = connection.CreateCommand())
{
// Cities$ comes from the name of the worksheet
command.CommandText = "SELECT * FROM [Sheet1_2$]";
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
listCustomers.Load(dr);
}
}
}
}
return listCustomers;
}
The problem is, some columns in the Excel file, for example, AccountID, contains both string data ('quanmv') and number (123456). When I use this code, it just ignores cell with number value, and leave it with blank.
How can I fix that?
Thank you so much.
By default, the connection to excel tries to guess data types of columns. If it guesses wrong, it may leave nulls where types don't convert. You can add IMEX=1
to the connection string to turn off this automatic guessing, and treat all values as strings.