dcsimg
Results 1 to 7 of 7

Thread: How to combine similar rows in a Datatable(the rows only differ by one column)?

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    49

    How to combine similar rows in a Datatable(the rows only differ by one column)?

    I have three sub tables that I want to process. For each I want to combine the rows, as they are only different by contents in the second column(I want to do the same to the fourth column, later):


    'Sub table 1
    xx|C201 |02300877 |Samsung |….
    xx|C201 |02300877 |Toshiba |….
    xx|C213 |02300877 |Samsung
    xx|C213 |02300877 |Toshiba
    xx|C606 |02300877 |Samsung
    xx|C606 |02300877 |Toshiba

    'Sub table 2
    xx| C303 |02301163
    xx| C305 |02301163
    xx|C712 |02301163

    ' Sub table 3
    xx|C207 |02301165 |....
    xx|C209 |02301165 |….
    xx|C708 |02301165


    After the combining's done I want to put these back together to a single Datatable that would look like this:


    xx| C201,C213,C606,C619 |02300877
    xx| C303, C305,C712 |02301163
    xx| C207, C209, C708 |02301165


    Now the second row would hold all different "Cxxx" information that appeared in the subtables. I'm fairly noob to vb and I'd appreciate some sample codes that could do the "combine rows in datatable" part and probably the final aggregation part. Any advice's appreciated. Thank you guys.






    p.s. For the fourth column, Manufacturer information , I want to do the same and I'd probably get something like this for the final table:



    xx| C201,C213,C606,C619 |02300877 | Samsung
    xx| C201,C213,C606,C619 |02300877 | Toshiba
    xx| C303, C305,C712 |02301163
    xx| C207, C209, C708 |02301165
    Last edited by SandiegoSSD; May 8th, 2012 at 05:00 PM.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,544

    Re: How to combine similar rows in a Datatable(the rows only differ by one column)?

    Any advice's appreciated
    Be sure to check out my signature titled "Do not open what ever you do!" as per this


    I understand pretty clearly what you eventually want to accomplish. However, what have you done so far? Did you run into any errors? If so what did they say? What type of database are you using(access, sql, ect.)? I won't speak on behalf of the forum users, however I know that I cannot help you with out seeing any effort on your behalf.

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    49

    Re: How to combine similar rows in a Datatable(the rows only differ by one column)?

    good point I'm collecting my thoughts

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,515

    Re: How to combine similar rows in a Datatable(the rows only differ by one column)?

    The following example may very well be more advance than you care for yet this will provide a method to do many (sub) tables. I do one table and place results into a results table which for your solution needs to be in this case form level rather than local to the button click event. Use a little imagination and you can do many tables. I have placed comments thru the code that will assist.

    Note 1 the database connection string is setup for MS-Access 2007, if your database is different than you will of course need to modify the connection string.

    Note 2 Refer to the xml file below which gives field names and data I tested the code with. The first column is Integer while the remain columns are strings. By studying the code note that the column names in the xml (which came from the MS-Access database) match up to the columns in the code i.e. SQL select statements and LINQ statements.

    If there is something you don't understand than you best choice is to find resources i.e. DataTable.Select, datatable select group by etc.

    Note 3 If something is not according to how things should turn out feel free to modify the code.

    Code:
    Public Class Form1
        Private Sub cmdExecute_Click(
            ByVal sender As System.Object,
            ByVal e As System.EventArgs) _
        Handles cmdExecute.Click
    
            DataGridView2.DataSource = Nothing
    
            Dim Table As New DataTable
            '
            ' Since we populated DataGridView1 with a DataTable we
            ' can of course cast the DataSource as so.
            '
            Table = CType(DataGridView1.DataSource, DataTable)
    
            '
            ' Group on the last two columns
            '
            Dim query =
                From row In Table.AsEnumerable() _
                Group row By GroupKey = New With
                    {
                        Key .C2 = row("Column2"),
                        Key .C3 = row("Column3")
                    } Into g = Group _
                Select New With
                    {
                        .Col2 = g(0).Field(Of String)("Column2"),
                        .Col3 = g(0).Field(Of String)("Column3")
                    }
    
            '
            ' In this demo we only use the first 3 columns
            '
            Dim NewTable As New DataTable
            NewTable.Columns.Add("C1", GetType(System.String))
            NewTable.Columns.Add("C2", GetType(System.String))
            NewTable.Columns.Add("C3", GetType(System.String))
            NewTable.Columns.Add("C4", GetType(System.String))
    
            Dim foundRows() As DataRow
    
            For Each item In query
    
                Dim Expression As String =
                    String.Format("Column2 = '{0}' AND Column3 = '{1}'",
                                  item.Col2, item.Col3
                )
    
                foundRows = Table.Select(Expression)
    
                '
                ' C1 column concatenates items in Column1
                '
                NewTable.Rows.Add(New Object() _
                    {
                        String.Join(",", (From T In foundRows Select T.Field(Of String)("Column1")).ToArray),
                        item.Col2,
                        item.Col3
                    }
                )
            Next
    
            DataGridView2.DataSource = NewTable
    
        End Sub
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim cb As New OleDb.OleDbConnectionStringBuilder With
            {
                .DataSource = IO.Path.Combine(Application.StartupPath, "YourDatabaseName.accdb"),
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
    
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = cb.ToString
                }
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT Identifier, Column1, Column2, Column3 FROM Table1"
                    }
    
                    Dim dt As New DataTable With {.TableName = "Data1"}
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    cn.Close()
    
                    DataGridView1.DataSource = dt
                End Using
            End Using
        End Sub
    End Class

  5. #5

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    49

    Re: How to combine similar rows in a Datatable(the rows only differ by one column)?

    Quote Originally Posted by kevininstructor View Post
    The following example may very well ..
    Note 1 the database connection string is setup for MS-Access 2007, if your database is different than you will of course need to modify the connection string.
    Thank you for the post. My case is a bit strange though. I'm actually working with a bunch of .csv files as source files, and I chose to read data into DataTable just for the sake of being able to sort the rows as needed. Thereafter I got on the endless path of exploring all those classes about DataTable and dataview and etc., which are totally unfamiliar to me.. I originally intended to read from CSV files(which is the step before I break the data into these subtables I have in my post),break into subtables, sort subtables , and write the sorted data back to a new .CSV file, without resorting to any database applications(sql, access).

    I guess what i could do to achieve the goal here is just go in the second column of each subtable , store any new(different) Ref Designator, and combine them into a single cell to put into the final .csv file.

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,515

    Re: How to combine similar rows in a Datatable(the rows only differ by one column)?

    Quote Originally Posted by SandiegoSSD View Post
    Thank you for the post. My case is a bit strange though. I'm actually working with a bunch of .csv files as source files, and I chose to read data into DataTable just for the sake of being able to sort the rows as needed. Thereafter I got on the endless path of exploring all those classes about DataTable and dataview and etc., which are totally unfamiliar to me.. I originally intended to read from CSV files(which is the step before I break the data into these subtables I have in my post),break into subtables, sort subtables , and write the sorted data back to a new .CSV file, without resorting to any database applications(sql, access).

    I guess what i could do to achieve the goal here is just go in the second column of each subtable , store any new(different) Ref Designator, and combine them into a single cell to put into the final .csv file.
    Doesn't matter if your source data is a database, csv, xml or other readable file they all can be placed into a DataTable.

    Example below reads in a text file line by line into an anonymous list which is used to create a DataTable via a language extension ToDataTable which in this case is assigned to a BindingSource. My point is the initial data source need only have a known structure to obtain the data.

    Code:
            '
            ' The extension method ToDataTable creates the column headings
            ' according to the members in Select New With which means you
            ' can change them as you desire. Make sure they match up to the
            ' names used in the prodedure Execute_Click 
            '
            bsData.DataSource =
                (
                    From line In IO.File.ReadAllLines(FileName) _
                    Where line.Length > 0 _
                    Let Items = line.Split(","c) _
                    Select New With
                           {
                               .Col1 = Items(0), _
                               .Col2 = Items(1), _
                               .Col3 = Items(2), _
                               .Col4 = Items(3), _
                               .Col5 = Items(4) _
                            } _
                        ).ToDataTable
    
            DataGridView1.DataSource = bsData

    Code:
    Module Module1
        <System.Diagnostics.DebuggerStepThrough()> _
        <System.Runtime.CompilerServices.Extension()> _
        Public Function ToDataTable(Of T)(ByVal value As IEnumerable(Of T)) As DataTable
    
            Dim returnTable As New DataTable
            Dim firstRecord = value.First
    
            For Each pi In firstRecord.GetType.GetProperties
                returnTable.Columns.Add(pi.Name, pi.GetValue(firstRecord, Nothing).GetType)
            Next
    
            For Each result In value
                Dim nr = returnTable.NewRow
                For Each pi In result.GetType.GetProperties
                    nr(pi.Name) = pi.GetValue(result, Nothing)
                Next
                returnTable.Rows.Add(nr)
            Next
            Return returnTable
        End Function
    
    End Module

  7. #7
    New Member
    Join Date
    Jul 2014
    Posts
    1

    Re: How to combine similar rows in a Datatable(the rows only differ by one column)?

    Gracias Kevininstructor... muy útil..

    Thank you!!! excellent!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width