MySQL data format 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 guide, except that I created the DSN in the system DSN, and not in the user DSN that worked for me.

I have a little knowledge of formulas and I could not figure out how to get this tabular data:

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

The MySQL part doesn't matter here. This MySQL table may very well be an Excel table. Now I understand that it was not even necessary to say that there is a MySQL table, but just an Excel table. But it can inspire / help someone.

After some documentation, I managed to solve the most important aspects of my problem. Range in db sheet:

 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 DB name that refers to:

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

baby name:

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

On another sheet with the name of the construction, I started with B2 and used the following formula:

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

On the third sheet, called output, I started with A1 and used the formula:

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

Now the last challenge is to create formulas from the design and output for automatic spending when adding new records to the main table, but I think this is not possible.

When importing from SQL, there will be a table instead of a range in the database sheet, so the formulas will look a little different. Click anywhere in the table, go to the Design tab and rename the base of the table, then in the construction 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

Here's how I will do it with VBA: first create a class module and name it CDatum. Put this code 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 

Then create 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 

Then insert the 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 routine. Or you can download the book that I used to check it and follow it.

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

Answer 2

First of all, do you need to use this connection to get data in Excel? Since it would be much easier if you just exported the data itself to a CSV file, which can later be opened directly using Excel.

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

As for your query, it seems you are trying to map a tree. Check out the following sites:

• Trees in SQL Databases
• Tree hierarchy in SQL
• SQL Graph Algorithms