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?

Answer 1

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.