none
Removing empty rows from DataSet RRS feed

  • Question

  • Hi Guys,

          I am accessing Excel sheet from my C# Code. I am storing all rows in DataSet..

    But i got one problem. After getting enterite Excel contents in DataSet i am getting some empty rows also. Is there any short cut to remove that empty rows in DataSet.

    I can use loop or i can dump into other Dataset but other than this any method.

    Plz Help MeSmile-

     

    Tuesday, July 24, 2007 6:36 AM

Answers

  • Hi, Alim

    I guess you are using OleDbConnection to access the Excel sheet and put it into dataset with OleDbDataAdapter.

    If so, check your connection string like:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + File_FullName + ";Extended Properties='Excel 8.0;'"
    change it to

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + File_FullName + ";Extended Properties='Excel 8.0;IMEX=1'";


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

     

    Thanks

    Thursday, July 26, 2007 3:37 AM
  •  

    Are you getting empty rows always at the end of the dataset?

     

    What you can do,  and it's a bit 'lame' is open the excel file, select the bottom few rows and delete them via the right click and shift up.

     

    This would stop any empty rows being returned.

     

    If you don't want to have to do that because it's not a one off, maybe edit the query and stick in a where clause to eliminate the rows being selected.

     

    Else instead of looping through rows in a dataset (table), create a dataview and filter out he offending rows and call the dataviews .ToDataTable method to create a datatable without the offending rows.

    Thursday, July 26, 2007 8:51 AM

All replies

  • Hi, Alim

    I guess you are using OleDbConnection to access the Excel sheet and put it into dataset with OleDbDataAdapter.

    If so, check your connection string like:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + File_FullName + ";Extended Properties='Excel 8.0;'"
    change it to

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + File_FullName + ";Extended Properties='Excel 8.0;IMEX=1'";


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

     

    Thanks

    Thursday, July 26, 2007 3:37 AM
  •  

    Are you getting empty rows always at the end of the dataset?

     

    What you can do,  and it's a bit 'lame' is open the excel file, select the bottom few rows and delete them via the right click and shift up.

     

    This would stop any empty rows being returned.

     

    If you don't want to have to do that because it's not a one off, maybe edit the query and stick in a where clause to eliminate the rows being selected.

     

    Else instead of looping through rows in a dataset (table), create a dataview and filter out he offending rows and call the dataviews .ToDataTable method to create a datatable without the offending rows.

    Thursday, July 26, 2007 8:51 AM
  • Hey Guys,

    I have the same problem.  But the differences is that my empty rows are in the middle and I have over 50 columns.  The user wants to see the duplicated rows, so that means I cannot use SELECT DISTINCT * FROM [excel]

    The empty rows could be anywhere.  The largest execl I faced with so far have over 100,000 rows. 

    Is there a more effecient way to get the empty rows or MUST I loop through and check all columns in each row??

    • Edited by MS_Dev Wednesday, May 20, 2009 6:11 PM
    • Proposed as answer by Amir Rabbanian Tuesday, September 1, 2009 1:40 PM
    Wednesday, May 20, 2009 5:22 PM
  • Hey Guys,

    I have the same problem.  But the differences is that my empty rows are in the middle and I have over 50 columns.  The user wants to see the duplicated rows, so that means I cannot use SELECT DISTINCT * FROM [excel]

    The empty rows could be anywhere.  The largest execl I faced with so far have over 100,000 rows. 

    Is there a more effecient way to get the empty rows or MUST I loop through and check all columns in each row??

    You may use the following code, it works for sure, any Q, let me know.

     /// <summary>
            /// Removes duplicate rows from given DataTable
            /// </summary>
            /// <param name="dTable">Table to scan for duplicate rows</param>
            /// <param name="KeyColumns">An array of DataColumns
            /// Containing the columns to match for duplicates</param>

            public static void RemoveDuplicates(DataTable dTable, DataColumn[] keyColumns)
            {
                int rowNdx = 0;

                while (rowNdx < dTable.Rows.Count - 1)
                {

                    DataRow[] dups = FindDups(dTable, rowNdx, keyColumns);

                    if (dups.Length > 0)
                    {

                        foreach (DataRow dup in dups)
                        {
                            dTable.Rows.Remove(dup);
                        }

                    }

                    else
                    {
                        rowNdx++;
                    }
                }

            }

            private static DataRow[] FindDups(DataTable dTable, int sourceNdx, DataColumn[] keyColumns)
            {
                ArrayList retVal = new ArrayList();
                DataRow sourceRow = dTable.Rows[sourceNdx];

                for (int i = sourceNdx + 1; i < dTable.Rows.Count; i++)
                {

                    DataRow targetRow = dTable.Rows[i];

                    if (IsDup(sourceRow, targetRow, keyColumns))
                    {

                        retVal.Add(targetRow);

                    }

                }

                return (DataRow[])retVal.ToArray(typeof(DataRow));

            }

            private static bool IsDup(DataRow sourceRow, DataRow targetRow, DataColumn[] keyColumns)
            {
                bool retVal = true;

                foreach (DataColumn column in keyColumns)
                {

                    retVal = retVal && sourceRow[column].Equals(targetRow[column]);

                    if (!retVal) break;

                }

                return retVal;
            }
    Tuesday, September 1, 2009 1:41 PM
  • Hi Guru's,

    Here i want to explain one thing. you can use below two connections strings to read excle file

    1)

    <strong>string
    

    connString = "Provider=Microsoft.Jet.OleDb.4.0; data source="

    + inputFilename + ";Extended Properties=Excel 8.0;"

    ;</strong>




     

    above connString works fine when you want to read all the data from excel sheet, but the problem with the above conn string is if you have empty rows at the end of excel it will read empty rows as well

    but if you don't want to read empty rows at the end of the excel sheet use below one

    2)

    <strong>string
    

    connString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source="

    + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""

    ;</strong>




     

    this works fine, you can use directly

    full code...

     

    public
    

    void

    ReadXLs(string

    filePath, string

    SheetName)
    {
    string

    connString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source="

    + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""

    ;
    OleDbConnection oledbConn = new

    OleDbConnection(connString);
    try


    {
    oledbConn.Open();
    OleDbCommand cmd = new

    OleDbCommand("SELECT * FROM ["

    + SheetName + "$]"

    , oledbConn);
    OleDbDataAdapter oleda = new

    OleDbDataAdapter();
    oleda.SelectCommand = cmd;
    DataSet ds = new

    DataSet();
    oleda.Fill(ds, "test"

    );
    }
    catch

    (Exception ee)
    {
    Response.Write(ee.Message);
    }
    finally


    {
    oledbConn.Close();
    }
    }

     

     

    Thanx :-)

    Venkat

    Thursday, December 30, 2010 1:36 PM
  • Please use a Generalised Function in a Module of your Project...

    Public Function DeleteBlankRowsfromDataset(ByRef Dtset As DataSet) As Boolean
            Try
                Dtset.Tables(0).AsEnumerable().Where(Function(row) row.ItemArray.All(Function(field) field Is Nothing Or field Is DBNull.Value Or field.Equals(""))).ToList().ForEach(Sub(row) row.Delete())
                Dtset.Tables(0).AcceptChanges()
                DeleteBlankRowsfromDataset = True
            Catch ex As Exception
                MsgBox("Deleting Blank Records in Dataset Failed")
                DeleteBlankRowsfromDataset = False
            End Try
    
        End Function

    • Proposed as answer by RakLali Monday, October 12, 2015 6:00 PM
    Monday, October 12, 2015 6:00 PM