Question 86884 in ASP.NET Question posted in
The official documentation can be found here.

ASP.Net Importing Excel File To DataTable losses Values When It Consists Different Data Types

Using the below code for importing excel data to datatable.

Everything works fine but while importing the data to datatable losses data when the values in excel file is Different Data Type.

For example if Colum-A is having 1 in A1 cell and "Text" in A2 cell, then A2 is returned as blank in imported datatable.

Any suggestion to get rid of this issue will be highly helpful.

ASPX Code window

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:FileUpload ID="FileUpload1" runat="server" />  

        <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />  
        <br />  
        <asp:Label ID="Label1" runat="server" Text="Has Header ?" />  

        <asp:RadioButtonList ID="rbHDR" runat="server">  
            <asp:ListItem Text="Yes" Value="Yes" Selected="True">  
            </asp:ListItem>  
            <asp:ListItem Text="No" Value="No"></asp:ListItem>  
        </asp:RadioButtonList>  

        <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging="PageIndexChanging" AllowPaging="true">  
        </asp:GridView>  

    </form>
</body>
</html>

Back End Code

using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnUpload_Click(object sender, EventArgs e)

    {

        if (FileUpload1.HasFile)

        {

            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

            string FilePath = Server.MapPath(FolderPath + FileName);

            FileUpload1.SaveAs(FilePath);

            Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);

        }

    }

    private void Import_To_Grid(string FilePath, string Extension, string isHDR)

    {

        string conStr = "";

        switch (Extension)

        {

            case ".xls": //Excel 97-03  

                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]

                .ConnectionString;

                break;

            case ".xlsx": //Excel 07  

                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]

                .ConnectionString;

                break;

        }

        conStr = String.Format(conStr, FilePath, isHDR);

        OleDbConnection connExcel = new OleDbConnection(conStr);

        OleDbCommand cmdExcel = new OleDbCommand();

        OleDbDataAdapter oda = new OleDbDataAdapter();

        DataTable dt = new DataTable();

        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet  

        connExcel.Open();

        DataTable dtExcelSchema;

        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

        connExcel.Close();

        //Read Data from First Sheet  

        connExcel.Open();

        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

        oda.SelectCommand = cmdExcel;

        oda.Fill(dt);

        connExcel.Close();

        //Bind Data to GridView  

        GridView1.Caption = Path.GetFileName(FilePath);

        GridView1.DataSource = dt;

        GridView1.DataBind();

    }

    protected void PageIndexChanging(object sender, GridViewPageEventArgs e)

    {

        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

        string FileName = GridView1.Caption;

        string Extension = Path.GetExtension(FileName);

        string FilePath = Server.MapPath(FolderPath + FileName);

        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);

        GridView1.PageIndex = e.NewPageIndex;

        GridView1.DataBind();

    }
}

Web Config Code

<configuration>

  <appSettings>

    <add key="FolderPath" value="Files/" />

  </appSettings>

  <connectionStrings>
    <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" />
    <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" />
  </connectionStrings>

  <system.web>
      <compilation debug="true" targetFramework="4.5.2" />
      <httpRuntime targetFramework="4.5.2" />
    </system.web>
</configuration>
Tags:

2

Answers


  1. 0

    You have to set IMEX=1 in your connection string:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'

    As stated here IMEX=1 tells the driver to always read intermixed data columns as text.

    Login or Signup to reply.
  2. 0

    It’s an ODBC issue, you must have to tell your connection that the fields (cells) can be in mixed mode, otherwise the ODBC driver will try to classify the field as numeric.If it finds anything other than numeric in the cell it return a null. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type.
    If the two types are equally mixed in the column, the provider chooses numeric over text.

    For example:

    1. In your 8 scanned rows, if the column contains 5 numeric values and 3 text values, the provider returns 5 numbers and 3 null values.
    2. In your 8 scanned rows, if the column contains 3 numeric values and 5 text values, the provider returns 3 null values and 5 text values.

    To work around this problem for read-only data, enable Import Mode by using the setting IMEX=1 in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode.

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search