Split function on an array

Question

I have a large text file (1 long line) with multiple delimiters (eg: ~, *, :). The ~ delimiter marks a new section, and the * and : delimiters mark sub sections or segments.

I tried the following but I'm getting a type mismatch error, likely because the Split function is meant to be used on a string, not an array.

Dim  strFileLine, arrSection, arrSegment, arrSegField
strFileLine = "C:\sometextfile.txt"
arrSection  = Split(strFileLine, "~")
arrSegment  = Split(arrSection, "*")
arrSegField = Split(arrSegment, ":")

I'm trying to use this logic to keep my segments and segment fields with the correct section, and insert those value into a database.

Any idea on how I can accomplish this with VBScript?

@Tondrey - it's easy to say what you shouldn't expect/do: assigning a file spec to a variable won't load he file's content; Split() will parse a string into an array of substrings separated by a 'delimiter' (i.e. separator), but not apply this operation to all elements of an array. But you should give a condensed sample of your input data and some hints about how you plan to use the (hierarchical?) parsed data. Has the file format a well known name/standard? — Ekkehard.Horner
– Ekkehard.Horner, Commented Dec 31, 2012 at 16:38

Ansgar Wiechers · Accepted Answer · 2013-01-01 13:43:09Z

5

The solution depends on how the fields are to be imported into the database. If you simply want to process all fields in the order they appear in the input file, you could replace the separator characters with newlines and then split the string at the newlines:

Set fso = CreateObject("Scripting.FileSystemObject")

Set text = fso.OpenTextFile("C:\sometextfile.txt").ReadAll

text = Replace(text, "~", vbNewLine)
text = Replace(text, "*", vbNewLine)
text = Replace(text, ":", vbNewLine)

arr = Split(text, vbNewLine)

For Each field In arr
  WScript.Echo field
Next

If you need to put more emphasis on the structure of the input file, you could process the input string with nested loops:

Set fso = CreateObject("Scripting.FileSystemObject")

Set text = fso.OpenTextFile("C:\sometextfile.txt").ReadAll

For Each segment In Split(text, "~")
  For Each section In Split(segment, "*")
    For Each field In Split(section, ":")
      WScript.Echo field
    Next
  Next
Next

For further assistance you'd need to supply more information about how the hierarchical structure should be imported into the database, as Ekkehard.Horner already pointed out.

answered Jan 1, 2013 at 13:43

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

1 Comment

maafk Over a year ago

Thank you so much! This is totally helps.

Zeddy · Accepted Answer · 2012-12-31 17:21:09Z

        Dim strFileLine
    Dim arrSection()
    Dim arrSegment()
    Dim arrSegField()
    Dim strBuf 
    Dim counta 
    Dim character 
    dim sectioncount 
    dim segmentcount 
    dim segfieldcount 

    strFileLine = "aaaaa~00000?AAAAA:bbbbb~11111?BBBBB:ccccc~22222?CCCCC:ddddd~33333?DDDDD:eeeee~44444?EEEEE:fffff~55555?EEEEE:"
    strBuf = ""
    For counta = 1 To Len(Trim(strFileLine))
        character = Mid(strFileLine, counta, 1)
        Select Case character
            Case "~"
                sectioncount = sectioncount + 1
                redim preserve arrSection(sectioncount - 1)
                arrSection(sectioncount-1)=strBuf
                strBuf = ""
            Case "?"
                segmentcount = segmentcount + 1
                redim preserve arrSegment(segmentcount - 1)
                arrSegment(segmentcount-1)=strBuf
                strBuf = ""
            Case ":"
                segfieldcount = segfieldcount + 1
                redim preserve arrSegField(segfieldcount - 1)
                arrSegField(segfieldcount-1)=strBuf
                strBuf = ""
            Case Else
                strBuf = strBuf & character
        End Select
    Next
    For counta = 0 To ubound(arrSection)-1
        document.Write("SECTION:=" & arrSection(counta) & "<br/>")
    Next
    For counta = 0 To ubound(arrSegment)-1
        document.Write("SEGMENT:=" & arrSegment(counta) & "<br/>")
    Next
    For counta = 0 To ubound(arrSegField)-1
        document.Write("SEGFIELD:=" & arrSegField(counta) & "<br/>")
    Next

gepi · Accepted Answer · 2014-06-01 07:52:44Z

0

To get the fifth parts (5th Section / 5th Segment / 5th Segfield), which are missing in Zaf's solution, the -1 must be removed from the FOR loops. For example:

redim preserve arrSection(sectioncount)
arrSection(sectioncount) = strBuf
sectioncount = sectioncount + 1
strBuf = ""

For counta = 0 To ubound(arrSection)   'REMOVE -1 from here
    document.Write("SECTION " & counta & " : " & arrSection(counta) & "<br>")
Next

(Same thing for Segment & Segfield). (Also : the last 6 chars in strFileLine (EEEEE:) should read FFFFF: ) Anyway, Zaf showed nicely that, with small data, Redim Preserve is pretty fast ! (His solution can be saved as a HTM file for testing (in IE10 mode if using IE))

answered Jun 1, 2014 at 7:52

gepi

1

Collectives™ on Stack Overflow

Split function on an array

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related