none
While Reading Excel Data in asp.net Datatype Problem is comming. RRS feed

  • Question

  • HI  Friends,

    I have a task i have to read excel data and i have to keep it in Grid.It is working Fine.

    But one condition its failing

    1. I have a column Batch Number.This has to allow alpha/Numeric/Alphanumerical values.

    If i give three rows as Number and fourth row value if i give (ex: Q20) Its not taking.Its taking Blank.

    Then i have checked in run time its treating Double as the data type of Batch Number Column.

    what should i do for this.I have formatted that column in Excel as Text.Even its not working.

    Thursday, July 25, 2013 11:13 AM

Answers

  • Hello,I did a test that:

    If the column "BatchNumber" is "General"(Type of column in excel) then when you give rows more than 7,llike following:

    > 7(rows):

    BatchNumber C2 C3
    1 AAAAA AAAAA
    2 AAAAA AAAAA
    3 AAAAA AAAAA
    4 AAAAA AAAAA
    5 AAAAA AAAAA
    6 AAAAA AAAAA
    7 AAAAA AAAAA
    8 AAAAA AAAAA
    A1 AAAAA AAAAA
    A2 AAAAA AAAAA
    A3 AAAAA AAAAA

    BatchNumber Column2 Column3
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
      AAAAA AAAAA
      AAAAA AAAAA
      AAAAA AAAAA

    <= 7(rosw)

    BatchNumber C2 C3
    1 AAAAA AAAAA
    2 AAAAA AAAAA
    3 AAAAA AAAAA
    4 AAAAA AAAAA
    5 AAAAA AAAAA
    6 AAAAA AAAAA
    7 AAAAA AAAAA
    A1 AAAAA AAAAA
    BatchNumber Column2 Column3
    1 AAAAA AAAAA
    2 AAAAA AAAAA
    3 AAAAA AAAAA
    4 AAAAA AAAAA
    5 AAAAA AAAAA
    6 AAAAA AAAAA
    7 AAAAA AAAAA
    A1 AAAAA AAAAA

    While if the column type is "Text",it is ok:

    BatchNumber Column2 Column3
    1 AAAAA AAAAA
    2 AAAAA AAAAA
    3 AAAAA AAAAA
    4 AAAAA AAAAA
    5 AAAAA AAAAA
    6 AAAAA AAAAA
    7 AAAAA AAAAA
    8 AAAAA AAAAA
    A1 AAAAA AAAAA

    So I think if your colunm type is "General",if it is,change to "Text" and there is one important point:

    Do not forget to rebliud your solution.

    Best Regards.

    Friday, July 26, 2013 10:04 AM

All replies

  • Hello vijayalaxmi_atyam,

    From your description, I notice the issue you are experiencing is that it will take blank reading data from one excel column when you give diffrent type.

    If I have misunderstood anything, please feel free to let me know.

    As far as I know that if we want to read data from excel to DataSet,we usually use OLEDB and the connectionString is following:

    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel8.0;HDR=YES;IMEX=1\"";

    Notice that "Excel8.0;HDR=YES;IMEX=1":

    1)"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

    2)"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text and

    "IMEX=0" and "IMEX=2" will result in ImportMixedTypes being ignored .Note that

    this option might affect excel sheet write access negative.

    So you can try set "IMEX=1".

    I would like to provide some articles below regarding this event for your reference:

    http://social.msdn.microsoft.com/Forums/en-US/9ec840d0-d132-4837-8cee-d4b89334401c/excel-update-using-oledb-and-imex1

    http://byteyourhead.blogspot.in/2007/07/reading-excel-data-from-cnet-problem.html

    I hope this will help resolve your problem.

    Best Regards

    • Edited by GalleryIsUsed Friday, July 26, 2013 3:41 AM Font Format
    Friday, July 26, 2013 3:38 AM
  • Hi Dear,

    What you have understood is correct.

    Even i have used Same connection String 

    HDR=YES;IMEX=1

    .I have mentioned  below my code.

    OleDbConnection objOleDbCon = null;
            DataTable dtSheetName = new DataTable();

            string fileExt = System.IO.Path.GetExtension(fup1.FileName);
            string fileName = System.IO.Path.GetFileName(fup1.FileName);
            OleDbCommand objOleDbCmd = new OleDbCommand();
            OleDbDataAdapter objOleDbDataAdapter = new OleDbDataAdapter();
            DataTable dtExcelData = new DataTable();

            string xConnStr = "";

            if (fileExt.ToLower() == ".xls")
            {
               
                xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            }
            else if (fileExt.ToLower() == ".xlsx")
            {
                xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
            }

            objOleDbCon = new OleDbConnection(xConnStr);

            try
            {
                if (objOleDbCon.State == ConnectionState.Closed)
                {
                    objOleDbCon.Open();
                }


                dtSheetName = objOleDbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dtSheetName == null)
                {
                    lblMessage.Text = "Please add the Valid Sheet Name in the Excel File. For more info see the Sample Excel Uploaded file";
                    lblMessage.Style.Add("display", "block");
                    lblMessage.Font.Bold = true;
                    lblMessage.ForeColor = Color.Red;
                    return;
                }
                else
                {
                    foreach (DataRow row in dtSheetName.Rows)
                    {
                        if (Convert.ToString(row["TABLE_NAME"]) == "")
                        {
                            if (objOleDbCon.State == ConnectionState.Open)
                            {
                                objOleDbCon.Close();
                            }

                            lblMessage.Text = "Please add the Valid Sheet Name in the Excel File. For more info see the Sample Excel Uploaded file";
                            lblMessage.Style.Add("display", "block");
                            lblMessage.Font.Bold = true;
                            lblMessage.ForeColor = Color.Red;
                            return;
                        }
                        //checks if file selected in fileupload matches with selected Prcocess and document
                        else if (Convert.ToString(row["TABLE_NAME"]) != (Convert.ToString(Session["strSheetName"]) + "$"))
                        {
                          

                            lblMessage.Text = "Please add the Valid Sheet Name in the Excel File. For more info see the Sample Excel Uploaded file";
                            lblMessage.Style.Add("display", "block");
                            lblMessage.Font.Bold = true;
                            lblMessage.ForeColor = Color.Red;
                            //return;

                        }
                        else
                        {
                            //Getting Columns from Excelsheeet
                            string sheetName = Convert.ToString(row["TABLE_NAME"]);
                            DataTable sheetColumns = objOleDbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, sheetName, null });
                            DataTable DTest = new DataTable();
                            List<string> listColumn = new List<string>();

                          
                            foreach (DataRow row1 in sheetColumns.Rows)
                            {
                                if (row1[3].ToString() == "BatchNumber")
                                    row1["Data_Type"] = 130;

                                listColumn.Add(row1["Column_name"].ToString());

                            }
                            sheetColumns.AcceptChanges();

                            DataSet dsCust = new DataSet();
                            int CustomerTableId = 0;
                            string tableName = "Customer" + SelectedCustomerPracticeID + this.ProcessType + this.DocumentType;
                            TableMasterManager objTableMasterManager = new TableMasterManager(Convert.ToInt32(this.SelectedCustomerID), strConnString);
                            dsCust = objTableMasterManager.GetCustomerTableDetails(tableName);
                            if (dsCust.Tables[0].Rows.Count > 0)
                                CustomerTableId = Convert.ToInt32(dsCust.Tables[0].Rows[0][0].ToString());

                            //Getting Table columns from database
                            DataSet dsTableColumns = new DataSet();
                            TableMasterManager objTableMasterManager2 = new TableMasterManager(Convert.ToInt32(this.SelectedCustomerID), strConnString);
                            dsTableColumns = objTableMasterManager2.DynamicTable_ExcelFormat(CustomerTableId);

                            //Excel sheet column validation
                            if (listColumn.Count != dsTableColumns.Tables[0].Rows.Count)
                            {
                                if (objOleDbCon.State == ConnectionState.Open)
                                {
                                    objOleDbCon.Close();
                                }

                                lblMessage.Text = "Columns in excel sheet are not valid. Please select valid excel sheet file.";
                                lblMessage.Style.Add("display", "block");
                                lblMessage.Font.Bold = true;
                                lblMessage.ForeColor = Color.Red;
                                return;
                            }
                            else
                            {
                                for (int i = 0; i < listColumn.Count; i++)
                                {
                                    int trueCount = 0;
                                    for (int j = 0; j < dsTableColumns.Tables[0].Rows.Count; j++)
                                    {
                                        if (listColumn[i].ToString() == dsTableColumns.Tables[0].Rows[j][0].ToString())
                                        {
                                            trueCount++;
                                            break;
                                        }
                                    }
                                    if (trueCount == 0)// no matching column found
                                    {
                                        if (objOleDbCon.State == ConnectionState.Open)
                                        {
                                            objOleDbCon.Close();
                                        }

                                        lblMessage.Text = "Columns in excel sheet are not valid. Please select valid excel sheet file.";
                                        lblMessage.Style.Add("display", "block");
                                        lblMessage.Font.Bold = true;
                                        lblMessage.ForeColor = Color.Red;
                                        return;
                                    }

                                }
                            }


                            DataTable dt = new DataTable();

                            //If excel sheet is valid this code will excecute
                            lblMessage.Text = "";
                            objOleDbCmd = new OleDbCommand("SELECT * FROM [" + Session["strSheetName"].ToString() + "$]", objOleDbCon);
                            objOleDbDataAdapter = new OleDbDataAdapter(objOleDbCmd);
                            objOleDbDataAdapter.Fill(dt);

    if check this dt few of the places blank is coming.As i have explained u above.

    Friday, July 26, 2013 4:38 AM
  • Hi,

    I cteate a excel file and write some data into it:

    BatchNumber   C2 C3
    1000.01  AAAAA AAAAA
    100.001  BBBBB BBBBB
    -10000002  CCCCC CCCCC
    C00001  C00001 C00001
    C00002  C00002 C00002
    300002  C00003 C00003
    1452ssds  DDDDD DDDDD
    EEEEE EEEEE EEEEE
    FFFFF FFFFF FFFFF

    And I read it with your code but change a little:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.Drawing;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    namespace BatchNumber.MyFile
    {
        public partial class BatchNumber : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                this.GridView1.DataSource = GetDataSource();
                this.GridView1.DataBind();
            }
            private DataSet GetDataSource()
            {
                DataSet ds = new DataSet();
                OleDbConnection objOleDbCon = null;
                DataTable dtSheetName = new DataTable();
                string fileExt = System.IO.Path.GetExtension("E:\\BMX\\Forms Test File\\BatchNumber.xlsx");
                string fileName = System.IO.Path.GetFileName("E:\\BMX\\Forms Test File\\BatchNumber.xlsx");
                OleDbCommand objOleDbCmd = new OleDbCommand();
                OleDbDataAdapter objOleDbDataAdapter = new OleDbDataAdapter();
                DataTable dtExcelData = new DataTable();
                string xConnStr = "";
                if (fileExt.ToLower() == ".xls")
                {
                    xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\BMX\\Forms Test File\\BatchNumber.xlsx" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
                }
                else if (fileExt.ToLower() == ".xlsx")
                {
                    xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + "E:\\BMX\\Forms Test File\\BatchNumber.xlsx" + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
                }
                objOleDbCon = new OleDbConnection(xConnStr);
                try
                {
                    if (objOleDbCon.State == ConnectionState.Closed)
                    {
                        objOleDbCon.Open();
                    }
                    dtSheetName = objOleDbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dtSheetName == null)
                    {
                        lblMessage.Text = "Please add the Valid Sheet Name in the Excel File. For more info see the Sample Excel Uploaded file";
                        lblMessage.Style.Add("display", "block");
                        lblMessage.Font.Bold = true;
                        lblMessage.ForeColor = Color.Red;
                    }
                    else
                    {
                        foreach (DataRow row in dtSheetName.Rows)
                        {
                            if (Convert.ToString(row["TABLE_NAME"]) == "")
                            {
                                if (objOleDbCon.State == ConnectionState.Open)
                                {
                                    objOleDbCon.Close();
                                }
                                lblMessage.Text = "Please add the Valid Sheet Name in the Excel File. For more info see the Sample Excel Uploaded file";
                                lblMessage.Style.Add("display", "block");
                                lblMessage.Font.Bold = true;
                                lblMessage.ForeColor = Color.Red;
                            }
                            //checks if file selected in fileupload matches with selected Prcocess and document 
                            else if (Convert.ToString(row["TABLE_NAME"]) != "Customer$")
                            {
                                lblMessage.Text = "Please add the Valid Sheet Name in the Excel File. For more info see the Sample Excel Uploaded file";
                                lblMessage.Style.Add("display", "block");
                                lblMessage.Font.Bold = true;
                                lblMessage.ForeColor = Color.Red;
                                //return;
                            }
                        }
                    }
                    objOleDbCmd = new OleDbCommand("SELECT * FROM [" + "Customer" + "$]", objOleDbCon);
                    objOleDbDataAdapter = new OleDbDataAdapter(objOleDbCmd);
                    objOleDbDataAdapter.Fill(ds);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return ds;
            }
        }
    }
    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="BatchNumber.aspx.cs" Inherits="BatchNumber.MyFile.BatchNumber" %>
    <!DOCTYPE html>
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <span></span>
                <hr />
                <asp:Label ID="lblMessage" runat="server"></asp:Label>
            </div>
            <div>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
                    <Columns>
                        <asp:BoundField DataField="BatchNumber" HeaderText="BatchNumber" />
                        <asp:BoundField DataField="C2" HeaderText="Column2" />
                        <asp:BoundField DataField="C3" HeaderText="Column3" />
                    </Columns>
                </asp:GridView>
            </div>
        </form>
    </body>
    </html>

    And the result is following:

    BatchNumber Column2 Column3
    1000.01 AAAAA AAAAA
    100.001 BBBBB BBBBB
    -10000002 CCCCC CCCCC
    C00001 C00001 C00001
    C00002 C00002 C00002
    300002 C00003 C00003
    1452ssds DDDDD DDDDD
    EEEEE EEEEE EEEEE
    FFFFF FFFFF FFFFF


    It shows ok.

    Friday, July 26, 2013 8:00 AM
  • BatchNumber

    Column2 Column3
    1 AAAAA AAAAA
    2 BBBBB BBBBB
    3 CCCCC CCCCC
    4 C00001 C00001
    5
    C00002 C00002
    6 C00003 C00003
    7 DDDDD DDDDD
    8 EEEEE EEEEE
    9 FFFFF FFFFF
    Like dis way enter 100 numbers and after that enter A1 ,A2,A3   and check.Seriously it won't work.
    Friday, July 26, 2013 8:21 AM
  • Hello,I did a test that:

    If the column "BatchNumber" is "General"(Type of column in excel) then when you give rows more than 7,llike following:

    > 7(rows):

    BatchNumber C2 C3
    1 AAAAA AAAAA
    2 AAAAA AAAAA
    3 AAAAA AAAAA
    4 AAAAA AAAAA
    5 AAAAA AAAAA
    6 AAAAA AAAAA
    7 AAAAA AAAAA
    8 AAAAA AAAAA
    A1 AAAAA AAAAA
    A2 AAAAA AAAAA
    A3 AAAAA AAAAA

    BatchNumber Column2 Column3
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
    1 AAAAA AAAAA
      AAAAA AAAAA
      AAAAA AAAAA
      AAAAA AAAAA

    <= 7(rosw)

    BatchNumber C2 C3
    1 AAAAA AAAAA
    2 AAAAA AAAAA
    3 AAAAA AAAAA
    4 AAAAA AAAAA
    5 AAAAA AAAAA
    6 AAAAA AAAAA
    7 AAAAA AAAAA
    A1 AAAAA AAAAA
    BatchNumber Column2 Column3
    1 AAAAA AAAAA
    2 AAAAA AAAAA
    3 AAAAA AAAAA
    4 AAAAA AAAAA
    5 AAAAA AAAAA
    6 AAAAA AAAAA
    7 AAAAA AAAAA
    A1 AAAAA AAAAA

    While if the column type is "Text",it is ok:

    BatchNumber Column2 Column3
    1 AAAAA AAAAA
    2 AAAAA AAAAA
    3 AAAAA AAAAA
    4 AAAAA AAAAA
    5 AAAAA AAAAA
    6 AAAAA AAAAA
    7 AAAAA AAAAA
    8 AAAAA AAAAA
    A1 AAAAA AAAAA

    So I think if your colunm type is "General",if it is,change to "Text" and there is one important point:

    Do not forget to rebliud your solution.

    Best Regards.

    Friday, July 26, 2013 10:04 AM
  • thanks its working fine.
    Friday, July 26, 2013 11:06 AM
  • Hello,

    I am very glad that you have solved your problem and if my answer help you a litttle,can you mark it an answer.

    Thanks a lot.

    Monday, July 29, 2013 1:23 AM