Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Nested Column (Excel, CSV) : How to extract just the metadata or column names within a column of data

Knicks

Technical User
Joined
Apr 1, 2002
Messages
384
Location
US
I am working with some complex data that originated in JSON format that I am downloading from Databricks into EXCEL format. What I am trying to do is parse out the Column names from an individual nested column. Basically something like this, The Excel file has multiple columns but a single column like Product can have multiple entries within (Nested). They don't always have the same amount of column headers within, some can have 10, some can have 1. I am trying to get the metadata of the column headers within Product to determine the schema of that field. I need someway to loop through the records and the field and parse all the would be column headers within Product - Product_Details, Product_Transfer, Product_Number, Product_Engine, Product_Lifecycle, Product_Service ect... . The data I am trying to parse is in the column Product between {} . Any advice would be appreciated

RowID, Product
100 { "Product_Details": "N/A", "Product_Transfer": "N/A", "Product_Number": "1", "Product_Engine": "Intel"}
101 { "Product_Lifecycle" : "2", "Product_Size" : "2x"}
102 { "Product_Details": "NA", "Product_Size": "1x", "Product_Service": "Amazon"}
 
May not be the most elegant code, but at the end you will get:
Product_Details
Product_Transfer
Product_Number
Product_Engine
Product_Lifecycle
Product_Size
Product_Service


Code:
Option Explicit

Sub Knicks()
Dim strTL As String
Dim fpath As String
Dim intLineNo As Integer
Dim strHeader As String
Dim strProd() As String
Dim A As Integer

ReDim strProd(0)

fpath = "C:\SomeFolder\YourFile.txt"

Open fpath For Input As #1
Do Until EOF(1)
    intLineNo = intLineNo + 1
    Line Input #1, strTL
    If intLineNo = 1 Then
        '''Get Product from first row
        strHeader = Trim(Split(strTL, ",")(1))
    Else
        ''' Get Product_XYZ from other rows
        Call AddToArray(strHeader, strProd, strTL)
    End If
Loop
Close #1

For A = 1 To UBound(strProd)
    Debug.Print strProd(A)
Next A

End Sub

Private Sub AddToArray(ByRef strH As String, ByRef strArr() As String, ByRef strText As String)
Dim aryTemp() As String
Dim X As Integer
Dim Y As Integer
Dim blnIsFound As Boolean

aryTemp = Split(strText, Chr(34))

For X = 0 To UBound(aryTemp)
    If InStr(aryTemp(X), strH) Then
        blnIsFound = False
        For Y = 0 To UBound(strArr)
            If strArr(Y) = aryTemp(X) Then
                blnIsFound = True
            End If
        Next Y
        If Not (blnIsFound) Then
            ReDim Preserve strArr(UBound(strArr) + 1)
            strArr(UBound(strArr)) = aryTemp(X)
        End If
        'Debug.Print aryTemp(X)
    End If
Next X

End Sub
 

Part and Inventory Search

Sponsor

Back
Top