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

Last Reply 4 months ago By dharmendr

Posted 4 months 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];
            }
        }
    }
}

 

Posted 4 months ago

Hi userNK,

Add IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text to the Extended Properties in the connection string would fix the problem.

Check this example. Now please take its reference and correct your code.

HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<br /><br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

Namespaces

C#

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

VB.Net

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Code

C#

protected void Upload(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        string filePath = Path.Combine(Server.MapPath("~/UploadedFiles/TemporaryFiles"), filename);
        FileUpload1.SaveAs(filePath);
        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();
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

VB.Net

Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
    If FileUpload1.HasFile Then
        Dim filename As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
        Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
        Dim filePath As String = Path.Combine(Server.MapPath("~/UploadedFiles/TemporaryFiles"), filename)
        FileUpload1.SaveAs(filePath)
        Dim excelConnectionString As String = ""
        Select Case extension
            Case ".xls"
                excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'"
            Case ".xlsx"
                excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'"
        End Select

        excelConnectionString = String.Format(excelConnectionString, filePath)
        Dim excelConnection As OleDbConnection = New OleDbConnection(excelConnectionString)
        Dim cmdExcel As OleDbCommand = New OleDbCommand()
        Dim oleDA As OleDbDataAdapter = New OleDbDataAdapter()
        Dim dt As DataTable = New DataTable()
        cmdExcel.Connection = excelConnection
        excelConnection.Open()
        Dim dtExcelSchema As DataTable
        dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
        excelConnection.Close()
        excelConnection.Open()
        cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
        oleDA.SelectCommand = cmdExcel
        oleDA.Fill(dt)
        excelConnection.Close()
        GridView1.DataSource = dt
        GridView1.DataBind()
    End If
End Sub

Screenshot


Posted 4 months 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