VBScript to split a text file into multiple files based on a variables that needs to be set from file contents

Question

I am hoping that someone will be able to help me out, basically I have a text file that I need to split based on a variable that will be contained within the file…..

I have added below an example of how the source file would look…

What I need to do is ignore the header and footer and then split the file in to separate files based on the second delimiter so for the first row this would be “1001” the files should group all of the matching references into the file and then apply a header and footer.

Header should be a variable (in this example it should be the first delimiter) and the footer should be the count of records, the file then needs to be saved with a variable name (see below)

Source file example:

Header is Here|1
Testcust1|1001|thisis 1
Testcust1|1001|thisis 2
Testcust1|1001|thisis 3
Testcust1|1001|thisis 4
Testcust2|1002|thisis 1
Testcust2|1002|thisis 2
Testcust3|1003|thisis 3
Testcust4|1004|thisis 1
Testcust4|1004|thisis 2
Testcust4|1004|thisis 3
Testcust1|1001|thisis 5
Testcust1|1001|thisis 6
Testcust1|1001|thisis 7
Testcust1|1001|thisis 8
Testcust1|1001|thisis 9
Footer|15

Below is what I would expect the first output file to look like based on the source:

Testcust1|1
Testcust1|1001|thisis 1
Testcust1|1001|thisis 2
Testcust1|1001|thisis 3
Testcust1|1001|thisis 4
Testcust1|1001|thisis 5
Testcust1|1001|thisis 6
Testcust1|1001|thisis 7
Testcust1|1001|thisis 8
Testcust1|1001|thisis 9
Footer|9

This needs to be saved as “C:\Testcust1(Datetime).txt”

Help would be greatly appreciated!

score 1 · Accepted Answer · edited May 23 '17 at 10:24

Theory:

"Grouping" means "Dictionary" in VBScript. So use a Dictionary to store the file to write to under the key the grouping is based of - as applied here and here.

Step by Step:

 Dim sLine : sLine = tsIn.ReadLine()
 Read a line like 'Footer|15' or 'Testcust4|1004|thisis 1'
 Dim aLine : aLine = Split(sLine, "|")
 Split it into an array
 If 2 = UBound(aLine) Then
    it's a data line like 'Testcust4|1004|thisis 1'
    If Not dicF.Exists(aLine(1)) Then
       We haven't seen the key, e.g. '1004'
       Dim sFSpec : sFSpec = oFS.BuildPath("..\data", aLine(1) & ".txt")
       build file spec for this key
       Set dicF(aLine(1)) = oFS.CreateTextFile(sFSpec)
       create the file and store it in dicF
       dicF(aLine(1)).WriteLine aLine(0) & "|1"
       write the header
    End If
    dicF(aLine(1)).WriteLine sLine
    write the data line to this key's file
 End If

Practice:

  Dim oFS  : Set oFS  = CreateObject("Scripting.FileSystemObject")
  Dim dicF : Set dicF = CreateObject("Scripting.Dictionary")
  Dim tsIn : Set tsIn = oFS.OpenTextFile("..\data\28691670.txt")
  Do Until tsIn.AtEndOfStream
     Dim sLine : sLine = tsIn.ReadLine()
     Dim aLine : aLine = Split(sLine, "|")
     If 2 = UBound(aLine) Then
        If Not dicF.Exists(aLine(1)) Then
           Dim sFSpec : sFSpec = oFS.BuildPath("..\data", aLine(1) & ".txt")
           Set dicF(aLine(1)) = oFS.CreateTextFile(sFSpec)
           dicF(aLine(1)).WriteLine aLine(0) & "|1"
        End If
        dicF(aLine(1)).WriteLine sLine
     End If
  Loop
  tsIn.Close
  Dim tsOut
  For Each tsOut in dicF.Items()
      tsOut.WriteLine "Footer|" & tsOut.Line - 2
      tsOut.Close
  Next

output of 1001.txt

Testcust1|1
Testcust1|1001|thisis 1
Testcust1|1001|thisis 2
Testcust1|1001|thisis 3
Testcust1|1001|thisis 4
Testcust1|1001|thisis 5
Testcust1|1001|thisis 6
Testcust1|1001|thisis 7
Testcust1|1001|thisis 8
Testcust1|1001|thisis 9
Footer|9

Ekkehard.Horner thank you for your help with this.... based on my example your script works perfect but i would like to understand what it is doing so i can addapt it in future... — Mike Harris, Feb 25 '15 at 12:41
i would like to understand each line between: Do Until tsIn.AtEndOfStream And Loop — Mike Harris, Feb 25 '15 at 12:43
@MikeHarris - if you have **specific** questions **after** looking at the examples given, I'm willing to answer them. — Ekkehard.Horner, Feb 25 '15 at 12:47
Good Morning @Ekkehard.Horner Thank you again for your guidance on this are you able to advise more on the below line of code what is the "2" reffering two and what is UBound(aLine) [ If 2 = UBound(aLine) Then ] — Mike Harris, Feb 26 '15 at 09:26
@MikeHarris - Read the docs for UBound(): https://msdn.microsoft.com/en-us/library/fhx59d0t%28v=vs.84%29.aspx — Ekkehard.Horner, Feb 26 '15 at 09:36

VBScript to split a text file into multiple files based on a variables that needs to be set from file contents

1 Answers1