none
How to delete empty rows from excel programmatically in asp.net RRS feed

  • Question

  • Hi,

     

    Can anyone please help me out with this:

     

    I am uploading an excel sheet and it has 3 rows of data with 65 columns.I am taking this data into a dataset with the help of a simple select query, dataset is taking 65531 number of empty rows and it is throwing timed out exception mid way. How do i restrict it from taking empty rows.

     

    This is my code:

     

    byte[] uploadedFile = new byte[htmUploadFile.PostedFile.ContentLength];

    string strFileName = Path.GetFileName(htmUploadFile.PostedFile.FileName);

    htmUploadFile.PostedFile.SaveAs(Server.MapPath(strFileName));

    string strOleDBConnectionString = Server.MapPath(strFileName);

    string strCon = @"Provider=Microsoft.Jet.OLEDB.4.0;" +

    "Data Source=" + strOleDBConnectionString + ";" +

    "Extended Properties='Excel 8.0;HDR=YES'";

     

    OleDbConnection objConn = new OleDbConnection(strCon);

    objConn.Open();

    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    Hashtable hsh = new Hashtable();

    foreach (DataRow row in dt.Rows)

    {

    hsh.Add(row["TABLE_NAME"].ToString(), row["TABLE_NAME"].ToString());

    }

    string[] excelSheets = new string[hsh.Count];

    int iCount = 0;

    foreach (object iCol in hsh.Keys)

    {

    excelSheets[iCount++] = Convert.ToString(iCol).Replace("$", "").Replace("'", "");

    }

    for (int k = 0; k < excelSheets.Length; k++)

    {

    OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + excelSheets[k] + "$] " , objConn);

    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

    objAdapter1.SelectCommand = objCmdSelect;

    objAdapter1.Fill(dsExcel, excelSheets[k]);

    }

     

    Kindly help ASAP!

     

    Thanks in advance!

    Thursday, January 10, 2008 9:33 AM

All replies

  • You could specify WHERE condition for your SELECT SQL statement to select everything except records where specific column has empty cell, for example (assuming that F1 is a first column)

     

    ("SELECT * FROM [" + excelSheets[k] + "$] WHERE F1<>''"

     

    Thursday, January 10, 2008 11:00 AM
    Moderator