Split text fiel searching for specific string of text and saving in mutltiple directories

Question

I have a text file that I need to split according to values in the 4th column of information. The script would need to split the lines of text according the the value of the first character of the text on that column and then split the file and save it to specified folders(different) under the original file name. for instance anything starting with the numbers 1 or 2 would be saved as file, anything starting with the number 4, 5 or 6 as another file and so on. Sample of file below:

0118844 10722     HAWKESBURY VALLEY MOTOR     624G05B    55567191             ROLLER TENSION
0118844 10722     HAWKESBURY VALLEY MOTOR     624G03A    92190654             LOCKING NUT   
0118844 10722     HAWKESBURY VALLEY MOTOR     517A03A    92056367             RADIATOR CAP V6
0118844 10722     HAWKESBURY VALLEY MOTOR     416H04B    92044669             BONNET LOCK
0118844           HAWKESBURY VALLEY MOTOR     213F04D    8972138700           BOLT
0118844           HAWKESBURY VALLEY MOTOR     101B15A    8973628940           THERMOSTAT

score 0 · Answer 1 · answered Sep 04 '12 at 08:31

Use a dictionary to store the discriminating digits (keys) and the corresponding (open) files (values). Loop over the lines of the input file; for each line: cut the digit, write the line to 'the file for the digit'. Don't forget to close all files.

In code:

  Const csDir = "..\data\splits"
  Const csInN = "splits.txt"
  Const csInF = "..\data\splits.txt" ' should be: Const csInF = goFS.BuildPath(csDir, csInN)
  Const cnPos = 47
  Dim aSplits   : aSplits       = Array("12", "456")
  Dim dicSplits : Set dicSplits = CreateObject("Scripting.Dictionary")

  If goFS.FolderExists(csDir) Then goFS.DeleteFolder csDir
  goFS.CreateFolder csDir
  Dim nSplit
  For nSplit = 0 To UBound(aSplits)
      Dim sDir : sDir = aSplits(nSplit)
      Dim nPos
      For nPos = 1 To Len(sDir)
          dicSplits(Mid(sDir, nPos, 1)) = nSplit
      Next
      sDir = goFS.BuildPath(csDir, sDir)
      goFS.CreateFolder sDir
      Set aSplits(nSplit) = goFS.CreateTextFile(goFS.BuildPath(sDir, csInN))
  Next

  Dim tsIn : Set tsIn = goFS.OpenTextFile(csInF)
  Do Until tsIn.AtEndOfStream
      Dim sLine : sLine = tsIn.ReadLine()
      Dim sKey  : sKey  = Mid(sLine, cnPos, 1)
      If dicSplits.Exists(sKey) Then
         aSplits(dicSplits(sKey)).WriteLine sLine
      End If
  Loop
  tsIn.Close

  For nSplit = 0 To UBound(aSplits)
      aSplits(nSplit).Close
  Next

Ansgar Wiechers · Answer 2 · 2012-09-04T15:05:47.160

0

I'd suggest to do the discrimination with a Select statement, because I consider that a lot easier to understand. A dictionary I'd use to manage the output files.

Const ForReading     = 1
Const ForWriting     = 2
Const keyPos         = 47
Const inputFileName  = "input.txt"
Const outputFileName = "input.txt"

outputFolders = Array("foo", "bar")

Sub WriteOutput(data, fldr)
  If Not fso.FolderExists(fldr) Then fso.CreateFolder(fldr)
  If Not outputFiles.Exists(fldr) Then outputFiles.Add fldr, fso.OpenTextFile(fso.BuildPath(fldr, outputFileName), ForWriting, True)
  outputFiles(fldr).WriteLine data
End Sub

Set fso = CreateObject("Scripting.FileSystemObject")
Set outputFiles = CreateObject("Scripting.Dictionary")

Set inputFile = fso.OpenTextFile(inputFileName, ForReading, True)
Do Until inputFile.AtEndOfStream
  line = inputFile.ReadLine
  Select Case Mid(line, keyPos, 1)
  Case 1, 2:
    WriteOutput line, outputFolders(0)
  Case 4, 5, 6:
    WriteOutput line, outputFolders(1)
  End Select
Loop
inputFile.Close

For Each f In outputFiles.Items
  f.Close
Next

edited Sep 04 '12 at 15:05

answered Sep 04 '12 at 10:38

Ansgar Wiechers

193,178
25
254
328

1

please consider: outputFolders(n), OpenTextFile(f,ForWriting,True), and moving Sub out of main/top level code. – Ekkehard.Horner Sep 04 '12 at 15:01
Bugs fixed. Thanks for the heads-up. I prefer to keep Sub/Function definitions before the main code, though. – Ansgar Wiechers Sep 04 '12 at 15:08
apologies for being pedantic, but the Sub *follows* the assignment to outputFolders – Ekkehard.Horner Sep 04 '12 at 15:15
Being pedantic is what helps avoiding/fixing mistakes. ;) In this case `outputFolders` is above the `Sub`, because that variable may be subject to change, whereas the rest of the script most likely won't be. With the exception of the `Select` statement perhaps, but I can't easily move that elsewhere without affecting readability. – Ansgar Wiechers Sep 04 '12 at 15:20

Split text fiel searching for specific string of text and saving in mutltiple directories

2 Answers2

Linked