Consulting

Results 1 to 9 of 9

Thread: Solved: Convert delimited string into an array

  1. #1
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    Solved: Convert delimited string into an array

    I want to split lines of text delimited by "|" to form a 4 dimensional array from the contents, for use in a UserForm. Is there a RegExp or similar to do this neatly? The delimiter can be changed if this would make life simpler. The lines will be read from a text file (attached)
    MD


    001|P:\1028 Crieff Golf Club DDA\14.04.04\|140404-001.jpg|Stair Landing
    002|P:\1028 Crieff Golf Club DDA\14.04.04\|140404-002.jpg|Toilet Entrance
    003|P:\1028 Crieff Golf Club DDA\14.04.04\|140404-003.jpg|No wheelchair access at bars

  2. #2
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Hi MD,

    Try
    [VBA]
    Dim mArr() As String
    mArr = Split("001|P:\1028 Crieff Golf Club DDA\14.04.04\|140404-001.jpg|Stair Landing", "|")
    [/VBA]
    This will return a 0 based array

    Later
    Tommy

  3. #3
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Thanks Tommy,
    I can manipulate that to suit my needs.
    MD

  4. #4
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    You aren't trying to copy this file into an array(0 to 15, 0 to 3) are you?

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Yes, That's the result I'm after.
    I'm using the following, but I'm sure there must be a more direct way
    [VBA]
    Do While ts.AtEndOfStream <> True
    i = i + 1
    myArr = Split(ts.readline, "|")
    For Each m In myArr
    j = j + 1
    MyList(i, j) = m
    Next
    j = 0
    Loop
    [/VBA]

  6. #6
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    The only way I know is CopyMemory (API) call. Massive speed increase, but will cause problems if not done properly. You can copy either the actual data or just the memory address. It works a lot easier with doubles, integers, long. The problem with strings is VB sets a string with a vbnullstring(0 bytes), a long is 0 (1 byte) 453 is 1 byte. SO to get it to work you have to dump spaces into the string to make it have something to copy to.

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Sounds complicated! I'll only ever have 50 - 100 lines, so I'll go with the Split for now.
    Thanks for the pointer.

  8. #8
    VBAX Regular
    Joined
    Sep 2004
    Posts
    65
    Location
    How about this;

    [VBA]
    Public Sub OpenTextFileTest()
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim fs, f, s
    Dim i, j, m, result
    Dim myArr(0 To 14, 0 To 3) As String
    i = 0: j = 0

    '' Open TextFile and read one line at a time
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile("C:\TEMP\DDA Report.txt", ForReading)
    Do While f.AtEndOfLine <> True
    s = f.readline
    '' Split a line into single dimensioned array
    result = Split(s, "|")
    '' Dump Single array elements into multi-dimensioned
    For j = 0 To 3
    myArr(i, j) = result(j)
    Next j
    i = i + 1
    Loop
    f.Close

    '' Testing section - dump the lot to debug
    i = 0: j = 0
    For i = LBound(myArr) To UBound(myArr)
    For j = 0 To 3
    Debug.Print i & "/" & j & vbTab & myArr(i, j)
    Next j
    Next i
    End Sub

    [/VBA]

  9. #9
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    Solved:Convert delimited string into an array

    Hi Bilby,
    A little bit neater than mine.
    Thanks
    MD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •