While reading Excel sheet if the value of column is numberic then it returns null in datatable

Question

I am reading values from Excel sheet. Columns generally contain String but sometimes it may contain numeric values. While reading Excel sheet into datatable numeric value is read as blank.

It reads 90004 as null, But if I sort this column by numeric the it reads numeric value and gives string value as null and if I sort this column by string then it reads string value and gives numeric as null.

AC62614 abc     EA  MISC
AC62615 pqr     EA  MISC
AC62616 xyz     EA  MISC
AC62617 test    EA  90004
AC62618 test3   TO  MISC
AC62619 test3   TO  STEEL

my code:

    public static DataTable ReadExcelFile(FileUpload File1, string strSheetName)
    {
        string strExtensionName = "";
        string strFileName = System.IO.Path.GetFileName(File1.PostedFile.FileName);
        DataTable dtt = new DataTable();
        if (!string.IsNullOrEmpty(strFileName))
        {
            //get the extension name, check if it's a spreadsheet
            strExtensionName = strFileName.Substring(strFileName.IndexOf(".") + 1);
            if (strExtensionName.Equals("xls") || strExtensionName.Equals("xlsx"))
            {
                /*Import data*/
                int FileLength = File1.PostedFile.ContentLength;
                if (File1.PostedFile != null && File1.HasFile)
                {

                    //upload the file to server
                    //string strServerPath = "~/FolderName"; 
                    FileInfo file = new FileInfo(File1.PostedFile.FileName);
                    string strServerFileName = file.Name;
                    string strFullPath =     HttpContext.Current.Server.MapPath("UploadedExcel/" + strServerFileName);
                    File1.PostedFile.SaveAs(strFullPath);

                    //open connection out to read excel
                    string strConnectionString = string.Empty;
                    if (strExtensionName == "xls")
                        strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                                                + strFullPath
                                                + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    else if (strExtensionName == "xlsx")
                        strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                                                + strFullPath
                                                + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

                    if (!string.IsNullOrEmpty(strConnectionString))
                    {
                        OleDbConnection objConnection = new OleDbConnection(strConnectionString);
                        objConnection.Open();
                        DataTable oleDbSchemaTable = objConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        bool blExists = false;
                        foreach (DataRow dtr in oleDbSchemaTable.Rows)
                        {
                            //reads from the spreadsheet called 'Sheet1'
                            if (dtr["TABLE_NAME"].ToString() == "" + strSheetName + "$")
                            {
                                blExists = true;
                                break;
                            }
                        }
                        if (blExists)
                        {
                            OleDbCommand objCmd = new OleDbCommand(string.Format("Select * from [{0}$]", strSheetName), objConnection);
                            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
                            objAdapter1.SelectCommand = objCmd;
                            DataSet objDataSet = new DataSet();
                            objAdapter1.Fill(objDataSet);
                            objConnection.Close();
                            dtt = objDataSet.Tables[0];
                        }

                    }
                }
            }
        }
        return dtt;
    }

As writen here http://stackoverflow.com/questions/3206915/dbnull-in-non-empty-cell-when-reading-excel-file-through-oledb the problem is that the provider sets the value to null if there are more string values then numeric values. Maybe this will help you. Sadly at the moment i have no solution for your problem. — Boot750, Jul 20 '15 at 07:35
It reads 90004 as null,But if i sort this column by numeric the it reads numeric value and gives string value as null and if i sort this column by string then it reads string value and gives numeric as null. — poonam, Jul 20 '15 at 07:53
can you try change IMEX=2 in your connection string to IMEX=1. IMEX = 1 should then import everything as text. You could then check in you c# code by useing int32.TryParse if it is a numeric value or not. — Boot750, Jul 20 '15 at 08:04
I will post it as an answer so other people see that it worked later. — Boot750, Jul 20 '15 at 09:41

score 3 · Accepted Answer · answered Jul 20 '15 at 09:43

3

If you change IMEX=2 in you connectionstring to IMEX = 1 the columns will be interpreted as text. Then you can get all data of the Sheet and check with Int32.TryParse() if the value is numeric or not.

answered Jul 20 '15 at 09:43

Boot750

891
1
7
17

s.ch · Answer 2 · 2021-10-11T03:17:17.680

0

Connecting string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"

IMEX=1 For Read All value as Text from Excel file
HDR =Yes For Excel First row as column read
IMEX=1 not use Than Numeric value will be NULL read

edited Oct 11 '21 at 03:17

answered Oct 06 '21 at 09:38

s.ch

134
5

While reading Excel sheet if the value of column is numberic then it returns null in datatable

2 Answers2