Format mysql data in excel

Question

I have a table in mysql like this:

+-------+--------+-------------+
| child | parent | data        |
+-------+--------+-------------+
|     1 |      0 | house       |
|     2 |      0 | car         |
|     3 |      1 | door        |
|     4 |      2 | door        |
|     5 |      2 | windscreen  |
|    11 |      5 | wiper       |
+-------+--------+-------------+

I connected to mysql from excel 2007 according to this tutorial except that i created the dns in system dns not in user dns, that worked for me.

I have a little knowledge about formulas and i couldn't figure out how to obtain this tabular data:

house | door
house | wall
car   | door
car   | windscreen | wiper  

Edit 1

The part with mysql is not a concern here. That mysql table could very well be an excel table.

Edit 2

Now I realize that it was not even necessary to say that there is a mysql table here just an excel table. But this may inspire/help somebody.

Edit 3

After some documentation i managed to solve the most important aspects of my problem

The range in sheet db:

child    parent     data
1        0          car
2        0          house
3        1          door
4        2          door
5        1          window
6        2          window
7        1          windscreen
8        7          wiper
9        4          color
10        2          color

I have a name db that refers to

=db!$A$2:OFFSET(db!$C$2,COUNTA(db!$C:$C)-2,0) 

a name child

=db!$A$2:OFFSET(db!$A$2,COUNTA(db!$A:$A)-2,0)

In another sheet with the name construct I started from B2 and used the following formula:

=IFERROR(
    IF(ISBLANK(B1),
        LARGE(child,COUNTA($A$2:A$2)+1),
        VLOOKUP(B1,db,2,0)
    ),".") 

In a third sheet named output I started from A1 and used the formula:

=IFERROR(VLOOKUP(construct!B2,db,3,0),".")

Now the last challenged is to make the formulas from construct and output to auto expend when new entry are added to the main table, but i don't think is possible.

Edit 4

When importing from sql in db sheet there will be a table instead of range so the formulas will look a little different. Click anywhere in the table, click design tab and rename the table base, then in construct sheet from b2 start with this formula:

=IFERROR(
    IF(ISBLANK(B1),
    LARGE(INDIRECT("base[child]"),COUNTA($A$2:A$2)+1),
    VLOOKUP(B1,base,2,0)
),".")

Answer 1

First of all, do you absolutely have to use that connection in order to get the data to Excel? Because it would be so much simpler if you just exported the data itself to a .csv file which can subsequently be opened directly with Excel.

Give it a shot: SELECT * FROM ... INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Regarding your query, it appears your trying to map a tree. Check out the following sites:

1. Trees in SQL databases
2. Tree hierarchy in SQL
3. SQL graph algorithms

Answer 2

Here's how I would do it using VBA: First create a class module and name it CDatum. Put this code in there.

Option Explicit

Private msID As String
Private msData As String
Private msParentID As String


Public Property Get ID() As String

    ID = msID

End Property

Public Property Let ID(ByVal sID As String)

    msID = sID

End Property

Public Property Get Data() As String

    Data = msData

End Property

Public Property Let Data(ByVal sData As String)

    msData = sData

End Property

Public Property Get ParentID() As String

    ParentID = msParentID

End Property

Public Property Let ParentID(ByVal sParentID As String)

    msParentID = sParentID

End Property

Public Property Get ChildCount() As Long

    Dim i As Long
    Dim lReturn As Long

    For i = 1 To gclsData.Count
        If gclsData.Data(i).ParentID = Me.ID Then
            lReturn = lReturn + 1
        End If
    Next i

    ChildCount = lReturn

End Property

Public Property Get Tree() As Variant

    Dim vaReturn As Variant
    Dim vaChild As Variant
    Dim i As Long, j As Long
    Dim lChildCount As Long
    Dim lRowCount As Long
    Dim lOldUbound As Long

    If Me.ChildCount = 0 Then
        lRowCount = 1
    Else
        lRowCount = Me.ChildCount
    End If

    ReDim vaReturn(1 To lRowCount, 1 To 1)

    For i = 1 To lRowCount
        vaReturn(i, 1) = Me.Data
    Next i

    For i = 1 To gclsData.Count
        If gclsData.Data(i).ParentID = Me.ID Then
            lChildCount = lChildCount + 1
            vaChild = gclsData.Data(i).Tree
            lOldUbound = UBound(vaReturn, 2)
            ReDim Preserve vaReturn(1 To lRowCount, 1 To UBound(vaReturn, 2) + UBound(vaChild, 2))
            For j = 1 To UBound(vaChild, 2)
                vaReturn(lChildCount, j + 1) = vaChild(1, j)
            Next j
        End If
    Next i

    Tree = vaReturn

End Property

Next make a class module and name it CData and put this code in it

Option Explicit

Private mcolCDatas As Collection

Private Sub Class_Initialize()

    Set mcolCDatas = New Collection

End Sub

Private Sub Class_Terminate()

    Set mcolCDatas = Nothing

End Sub

Public Sub Add(clsDatum As CDatum)

    mcolCDatas.Add clsDatum, clsDatum.ID

End Sub

Public Property Get Count() As Long

    Count = mcolCDatas.Count

End Property

Public Property Get Data(vItem As Variant) As CDatum

    Set Data = mcolCDatas.Item(vItem)

End Property

Public Property Get FilterByTopLevel() As CData

    Dim clsReturn As CData
    Dim i As Long
    Dim clsDatum As CDatum

    Set clsReturn = New CData

    For i = 1 To Me.Count
        Set clsDatum = Me.Data(i)
        If clsDatum.ParentID = 0 Then
            clsReturn.Add clsDatum
        End If
    Next i

    Set FilterByTopLevel = clsReturn

End Property

Next insert a standard module and put this code in it

Option Explicit

Public gclsData As CData

Sub FillClass()

    Dim clsDatum As CDatum
    Dim rCell As Range

    Set gclsData = New CData

    For Each rCell In Sheet1.Range("A2:A7").Cells
        Set clsDatum = New CDatum
        clsDatum.ID = rCell.Value
        clsDatum.Data = rCell.Offset(0, 2).Value
        clsDatum.ParentID = rCell.Offset(0, 1).Value
        gclsData.Add clsDatum
    Next rCell

End Sub

Sub PrintTree()

    Dim clsDatum As CDatum
    Dim clsTopLevel As CData
    Dim i As Long
    Dim ws As Worksheet
    Dim vaData As Variant
    Dim lRowCount As Long

    FillClass

    Set clsTopLevel = gclsData.FilterByTopLevel
    Set ws = ThisWorkbook.Worksheets.Add

    lRowCount = 1

    For i = 1 To clsTopLevel.Count
        Set clsDatum = clsTopLevel.Data(i)
        vaData = clsDatum.Tree
        ws.Cells(lRowCount, 1).Resize(UBound(vaData, 1), UBound(vaData, 2)).Value = vaData
        lRowCount = lRowCount + UBound(vaData, 1)
    Next i

End Sub

Then run the PrintTree sub. Or you can download the workbook I used to test it and follow along in there.

http://www.dailydoseofexcel.com/excel/TestDataClass.zip