How to convert a string string of key-value pairs into an array

Question

I have below sample data. I want to convert this string into an array

device_name="Text Data" d_id=7454579598 status="Active" Key=947-4378-43248274

I tried below:

Const ForReading = 1 

Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objTextFile = objFSO.OpenTextFile _ 
    ("d:\vbfile.txt", ForReading) 

Do Until objTextFile.AtEndOfStream 
    strNextLine = objTextFile.Readline 
    arrServiceList = Split(strNextLine , " ") 

    For i = 0 to Ubound(arrServiceList) 
        Wscript.Echo  arrServiceList(i) 
    Next 
Loop

it generates below

device_name="Text
Data"
d_id=7454579598
status="Active"
Key=947-4378-43248274

Expected output

device_name="Text Data"    
d_id=7454579598
status="Active"
Key=947-4378-43248274

are the lines always that exact form?

John Coleman
– John Coleman

2015-11-07 14:42:49 +00:00
Commented Nov 7, 2015 at 14:42 — John Coleman
– John Coleman, Commented Nov 7, 2015 at 14:42
Field name may be different...

kreya
– kreya

2015-11-07 15:00:21 +00:00
Commented Nov 7, 2015 at 15:00 — kreya
– kreya, Commented Nov 7, 2015 at 15:00

Tomalak · Accepted Answer · 2015-11-07 15:40:13Z

3

How about this approach:

Option Explicit

Const ForReading = 1
Dim FSO, keyValueExpr
Set FSO = CreateObject("Scripting.FileSystemObject") 

Set keyValueExpr = New RegExp
keyValueExpr.Pattern = "\b(\w+)=(""[^""]*""|\S*)"
keyValueExpr.Global = True

Dim result, record, match
Set result = CreateObject("Scripting.Dictionary")

With FSO.OpenTextFile("D:\vbfile.txt", ForReading)
    While Not .AtEndOfStream
        Set record = CreateObject("Scripting.Dictionary")
        result.Add result.Count + 1, record
        For Each match In keyValueExpr.Execute(.ReadLine)
            record.Add match.SubMatches(0), match.SubMatches(1)
        Next
    Wend
    .Close
End With

Dim msg, lineNo, key
For Each lineNo In result
    msg = "Line " & lineNo & vbNewLine
    For Each key In result(lineNo)
        msg = msg & vbNewLine & key & ": " & result(lineNo)(key)
    Next
    MsgBox msg
Next

It uses a regular expression that can identify key-value pairs that fulfill these conditions:

The key is a string of characters (a-z), digits (0-9) or underscores (_)
The value is anything that is either enclosed in double quotes or anything except a space.
Compare https://regex101.com/r/zL2mX5/1

The program creates nested dictionaries, the outer dictionary holding all lines of the file with the corresponding line numbers (1..n) for keys, each inner dictionary holds the key-value pairs found on each line.

This layout gives you the opportunity to address every value very conveniently:

value = result(3)("status")

edited Nov 7, 2015 at 15:40

answered Nov 7, 2015 at 15:33

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kreya Over a year ago

Thanks for reply.It works.Now I have one more field such as src=00:0:00:0:00:0. How to read this one also ?Could you specify reg exp for this also ?

John Coleman · Accepted Answer · 2015-11-07 16:20:39Z

Here is a function that might help. It takes a string and a delimiter and returns an array obtained by splitting on the delimiter -- whenever the delimiter isn't inside a quote:

Function SmartSplit(s, d)
    Dim c, i, j, k, n, A, quoted
    n = Len(s)
    ReDim A(n - 1)
    quoted = False
    i = 1
    k = 0
    For j = 1 To n
        c = Mid(s, j, 1)
        If c = """" Then quoted = Not quoted
        If c = d And Not quoted Then
            A(k) = Mid(s, i, j - i)
            k = k + 1
            i = j + 1
        End If
    Next
    If i < n Then
        A(k) = Mid(s, i)
    Else
        k = k - 1
    End If
    ReDim Preserve A(k)
    SmartSplit = A
End Function

In your example -- just replace Split by SmartSplit and it should work.

Collectives™ on Stack Overflow

How to convert a string string of key-value pairs into an array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related