[Solved] Reading Excel using OleDB ignoring alphanumeric values using C#

Last Reply one month ago By dharmendr

Posted one month ago

I am importing excel. please see sample data

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

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

[HttpPost]
public ActionResult ImportRelationship(string sheet, string filename)
{
    string Message = string.Empty;

    try
    {
        #region----Excel Connection and Reading Section-------

        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 [" + sheet + "]";
        oleDA.SelectCommand = cmdExcel;
        oleDA.Fill(dt);
        excelConnection.Close();
        #endregion

        #region----Data Importing Section-----

        #region Validate import Data

        int columnCount = dt.Columns.Count;

        if (columnCount != 8)
        {
            Message = MSG11;
            goto Exit;
        }

        if (dt.Rows.Count == 0)
        {
            Message = MSGNoData;
            goto Exit;
        }
        else
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow row = dt.Rows[i];
            }
        }
    }
}

 

You are viewing reply posted by: dharmendr one month ago.
Posted one month 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