Transform array of data to datatable rows

mcsdguyian
mcsdguyian used Ask the Experts™
on
I am try to take a 1 dimensional array of data and transform it to display in X number of columns each with X number of rows for each page into a datatable to easily display as the sample below shows. I am not sure the best way to do this.

Just to test the example I want 3 columns per page, and each column will contain 3 rows with the sample data below there should be 2 pages with 3 columns and a 4 page will have only 1 col and 1 row of data remaining.

samplearray= "{"100","110","120","130","140","150","160","170","180","190","200","210","220","230","240","250","260","270","280"}"

Example Output:

[Page #1]

[Column #1]     [Column #2]     [Column #3]
100                     130                    160
110                     140                    170
120                     150                    180

[Page #2]

[Column #1]     [Column #2]     [Column #3]
190                     220                     250                    
200                     230                     260                    
210                     240                     270                    

[Page #3]

[Column #1]     [Column #2]     [Column #3]
280
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
It almost feels like you are looking for a three dimensional array, where page number is another dimension?

If not then you would have to break pages based on row number in the presentation code?

If you just want a simple two dimensional table from a one dimensional one, then would the number of columns be the driver for the transformation?  And then based on that as many rows as needed would be added to the array (not assigned to pages)?


»bp
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
This seemed to work as a test here, should give you the general idea of an approach.  Code below, followed by output from debug.

    Sub EE29146093()
        Dim a() As String
        Dim dt As DataTable
        Dim s As String

        ' Initialize array
        a = {"100", "110", "120", "130", "140", "150", "160", "170", "180", "190", "200", "210", "220", "230", "240", "250", "260", "270", "280"}

        ' Convert to data table breaking into 3 columns
        dt = Array2DataTable(a, 3)

        ' Display data table to console for testing
        s = ""
        For Each col As DataColumn In dt.Columns
            If s <> "" Then
                s = s & ","
            End If
            s = s & col.ColumnName
        Next
        Console.WriteLine(s)
        For Each row As DataRow In dt.Rows
            s = ""
            For Each col As DataColumn In dt.Columns
                If s <> "" Then
                    s = s & ","
                End If
                s = s & row(col.ColumnName)
            Next
            Console.WriteLine(s)
        Next

    End Sub

    Function Array2DataTable(a() As String, w As Integer) As DataTable
        Dim dt As New DataTable
        Dim r As Integer
        Dim c As Integer

        ' Define column headers for table
        For c = 1 To w
            dt.Columns.Add(New DataColumn("Col" & c))
        Next

        ' Map input array to rows and columns in data table
        For r = 0 To UBound(a) Step 3
            ' Create new row
            Dim row As DataRow = dt.NewRow()
            ' Add desired number of columns to each row
            For c = 0 To w - 1
                ' Handle case where array doesn't have an even multiple of columns in it (fill with blank values)
                If r + c > UBound(a) Then
                    row(c) = ""
                Else
                    row(c) = a(r + c)
                End If
            Next
            ' Add this row to table
            dt.Rows.Add(row)
        Next

        ' return table
        Array2DataTable = dt

    End Function

Open in new window

Col1,Col2,Col3
100,110,120
130,140,150
160,170,180
190,200,210
220,230,240
250,260,270
280,,

Open in new window


»bp

Author

Commented:
Hi Bill,

The only problem is that I am actually trying to make the value increment per column row and not per row column. That is why I was having a hard time. So the output should be as follows.

Page #1
Col1,Col2,Col3
100,130,160
110,140,170
120,150,180

Page #2
Col1,Col2,Col3
190,220,250
200,230,260
210,240,270

Page #3
Col1,Col2,Col3
290,,
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Sorry, I noticed that when I originally read the Q, then just did the "obvious" mapping when I got to trying to code an example.  Okay, we can adjust for that...


»bp

Author

Commented:
No Problem.  I was spinning my gears for awhile on that one and thought I should get a fresh mind on the problem. Thanks!
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
So it appears we would have to take the "rows per page" value into consideration to do that.  Since you want the first 9 values on the first 3 rows of the data table, then the next 9, etc.

Let me ask you this, what do you want the data table itself to look like?  Do you just want:

[Col1]   [Col2]   [Col3]
 100      130      160
 110      140      170 
 120      150      180
 190      220      250                     
 200      230      260                     
 210      240      270                     
 280

Open in new window


»bp
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Or...

[Col1]   [Col2]   [Col3]
 100      170      230
 110      180      240
 120      190      250
 130      200      260
 140      210      270
 150      220      280
 160

Open in new window


»bp

Author

Commented:
Yes the first example is how I would want the data table populated and yes you would have to  use a  "rows per page" value. Thanks.
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Okay, another boundary condition to consider now.

If we started with this array:

{"100", "110", "120", "130", "140", "150", "160", "170", "180", "190", "200", "210", "220", "230", "240", "250", "260", "270", "280", "290", "300"}

Open in new window

And were doing 3 columns per row, 3 rows per page, would you want:

[Col1]   [Col2]   [Col3]
 100      130      160
 110      140      170 
 120      150      180
 190      220      250                     
 200      230      260                     
 210      240      270                     
 280
 290
 300

Open in new window

or:

[Col1]   [Col2]   [Col3]
 100      130      160
 110      140      170 
 120      150      180
 190      220      250                     
 200      230      260                     
 210      240      270                     
 280      290      300

Open in new window


»bp

Author

Commented:
The first example would be correct. Thanks
Test your restores, not your backups...
Top Expert 2016
Commented:
Okay, this seems to work here, see what you think.

    Sub EE29146093()
        Dim a() As String
        Dim dt As DataTable
        Dim s As String

        ' Initialize array
        a = {"100", "110", "120", "130", "140", "150", "160", "170", "180", "190", "200", "210", "220", "230", "240", "250", "260", "270", "280", "290", "300", "310"}

        ' Convert to data table breaking into 3 columns
        dt = Array2DataTable(a, 3, 3)

        ' Display data table to console for testing
        s = ""
        For Each col As DataColumn In dt.Columns
            If s <> "" Then
                s = s & ","
            End If
            s = s & col.ColumnName
        Next
        Console.WriteLine(s)
        For Each row As DataRow In dt.Rows
            s = ""
            For Each col As DataColumn In dt.Columns
                If s <> "" Then
                    s = s & ","
                End If
                s = s & row(col.ColumnName)
            Next
            Console.WriteLine(s)
        Next

    End Sub

    Function Array2DataTable(inputData() As String, colsPerRow As Integer, rowsPerPage As Integer) As DataTable
        Dim dt As New DataTable
        Dim r As Integer
        Dim c As Integer
        Dim p As Integer
        Dim i As Integer
        Dim maxPage As Integer

        ' Define column headers for table
        For c = 1 To colsPerRow
            dt.Columns.Add(New DataColumn("Col" & c))
        Next

        maxPage = Math.Ceiling(UBound(inputData) / (rowsPerPage * colsPerRow))

        ' Map input array to rows and columns in data table
        For p = 0 To maxPage
            For r = 0 To rowsPerPage - 1
                ' Make sure we still have data for at least one column of a new row
                If (p * colsPerRow * rowsPerPage) + r <= UBound(inputData) Then
                    ' Create new row
                    Dim row As DataRow = dt.NewRow()
                    ' Add desired number of columns to each row
                    For c = 0 To colsPerRow - 1
                        ' Calculate index of array to map to this table value
                        i = (p * colsPerRow * rowsPerPage) + (c * rowsPerPage) + r
                        ' Handle case where array doesn't have an even multiple of columns in it (fill with blank values)
                        If i > UBound(inputData) Then
                            row(c) = ""
                        Else
                            row(c) = inputData(i)
                        End If
                    Next
                    ' Add this row to table
                    dt.Rows.Add(row)
                End If
            Next
        Next

        ' return table
        Array2DataTable = dt

    End Function

Open in new window

Col1,Col2,Col3
100,130,160
110,140,170
120,150,180
190,220,250
200,230,260
210,240,270
280,310,
290,,
300,,

Open in new window


»bp

Author

Commented:
Thanks Bill.  This was exactly what I was looking for. Great Job!
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Great, glad that was useful (and I had a little fun puzzling out the mapping).


»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial