Excel column data making alphabet values blank using asp.net

Question

While uploading Excel there are values in columns in alphabets, so taking into datatable the values are getting blank.

below is the code which is making it blank.

string filename = Path.GetFileName(fluploadData.FileName);
        // FileUpload.SaveAs(Server.MapPath("~/") + filename);
        string filenamewithoutrext = string.Empty;
        FileExt = Path.GetExtension(fluploadData.FileName).ToLower();
        if (Path.GetExtension(fluploadData.FileName).ToLower() != ".xls" &&
            Path.GetExtension(fluploadData.FileName).ToLower() != ".xlsx"
        )
        {

            Response.Write("Only .xls,.xlsx are allowed.!");
            return;
        }

        filenamewithoutrext = Path.GetFileNameWithoutExtension(fluploadData.FileName).ToLower();

        string path = Server.MapPath("UploadData\\");
        string filename_ = filenamewithoutrext;

        //   DeleteDirectory(path);
        if (!Directory.Exists(path))   // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
        {
            Directory.CreateDirectory(path);
        }
        else
        {
            foreach (string file in Directory.GetFiles(path))
            {
                File.Delete(file);
            }
        }

        string fname;
        fname = path + filename_ + ".xls";

        fluploadData.SaveAs(fname);
        HttpContext.Current.Session["ExcelFilePath"] = fname;

        string conStr = "";
        System.Data.DataTable dtExcelRows = new System.Data.DataTable();

        switch (FileExt)
        {
            case ".xls": //Excel 97-03
                conStr = System.Configuration.ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = System.Configuration.ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }

        conStr = String.Format(conStr, fname, "YES");

        System.Data.OleDb.OleDbConnection connExcel = new System.Data.OleDb.OleDbConnection(conStr);
        System.Data.OleDb.OleDbCommand cmdExcel = new System.Data.OleDb.OleDbCommand();
        System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();
        cmdExcel.Connection = connExcel;
        connExcel.Open();
        System.Data.DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
        System.Data.DataTable dtExcelColumnsTable = connExcel.GetSchema("Columns");
        //string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString().Replace('\'', ' ').Trim();
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString().Trim();  
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dtExcelRows);
        connExcel.Close();

`there are values in columns in alphabets` What does `in alphabets` mean? — mjwills, Jul 17 '18 at 11:58
The short answer is that the Excel ODBC driver is guessing your data type incorrectly. I don't know how to fix that, other than don't use Excel. — mjwills, Jul 17 '18 at 12:04
Would it be possible to see your connection string? It may be possible to solve this issue by changing it (obviously blank any sensitive data). — SBFrancies, Jul 17 '18 at 12:43
@SBFrancies: here is the connection string `Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}'` what should I change to ? one more line after that. `conStr = String.Format(conStr, fname, "YES");` — Nad, Jul 17 '18 at 12:51

score 0 · Answer 1 · answered Jul 17 '18 at 13:00

The problem is likely caused by mixed datatypes in a column. For example the first few rows have a number value so it is then presumed to be a column with a numeric type. When a non numeric value is encountered it can't be parsed and is ignored.

See this answer for some ways around this. They may include setting a value of IMEX=1 in the connection string or treating the header row as data (HDR=0) - assuming your column names are non numeric.

You might also want to look into specific libraries which are designed for reading/writing Excel Documents, I have had good experiences with EPPlus but it only works for .xlsx files not .xls, NPOI which I haven't used can do both.

As others have mentioned if you are just using the Excel document as a datasource for the applications it may be better to look into an alternative solution such as SQL. If you have a requirement to process spreadsheets from third parties obviously that would not be an option.

Excel column data making alphabet values blank using asp.net

1 Answers1