OleDB ignoring Excel cells having alphanumeric values

Last Reply 9 days ago By dharmendr

Posted 11 days ago

I am importing excel. please see sample data

http://prntscr.com/opj6tp

and when select into datatable some values for one column including number and text gives empty. 

http://prntscr.com/opj7fr

when put numbers in one sheet and characters in other it will import correctly.

code is below.

string filePath = Path.Combine(Server.MapPath("~/UploadedFiles/TemporaryFiles"), filename);
string excelConnectionString = Functions.GetExcelConnectionString(filePath);
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
OleDbDataAdapter oleDA;
DataTable dt = new DataTable();
string query = "select * from [" + sheet + "]";
excelConnection.Open();
oleDA = new OleDbDataAdapter(query, excelConnection);
oleDA.Fill(dt);
excelConnection.Close();

thank you for help in advance.

You are viewing reply posted by: dharmendr 9 days ago.
Posted 9 days ago

Hi userNK,

I have checked the provided code. Its working at my end.

string filename = "Test.xls";
string Message = string.Empty;
try
{
    string filePath = Path.Combine(Server.MapPath("~/UploadedFiles/TemporaryFiles"), filename);
    string extension = Path.GetExtension(filename);
    string excelConnectionString = "";
    switch (extension)
    {
        case ".xls": //Excel 97-03
            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'";
            break;
        case ".xlsx": //Excel 07
            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'";
            break;
    }
    excelConnectionString = String.Format(excelConnectionString, filePath);
    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter oleDA = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    cmdExcel.Connection = excelConnection;
    excelConnection.Open();
    DataTable dtExcelSchema;
    dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
    excelConnection.Close();
    excelConnection.Open();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
    oleDA.SelectCommand = cmdExcel;
    oleDA.Fill(dt);
    excelConnection.Close();
}

Screenshots

The Excel

The Dataset Visualizer