We help IT Professionals succeed at work.
Troubleshooting Question

Trying to Get a VB Script to split a text file into multiple

88 Views
Last Modified: 2020-09-30
I have a text file I receive from a client where they send us a single file with multiple record types. I am trying to split the file into 3 files.

The record type is in positions 1-2 of each file and unique identifier is in positions 3-13. I need the program to start in the first row. If record type is "02" it looks to position 253-254 if it equals "OH" all record types with matching data in positions 3-13 are copied as-is to ResultsOH.txt in order by record number in positions 1-2. If it is equal to "MI" all records with matching data in positions 3-13 are copied as-s to ResultsMI.txt in order by record number in positions 1-2. If there is any record type with a unique identifier that is not contained in a record "02", the line is copied as-is to ResultMisc.txt.

SampleFile.txt    Original File
ResultOH.txt       Output #1
ResultMI.txt         Output #2
ResultMisc.txt      Output #3
Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
and this will be a batch or script file that will run con command prompt right?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
not sure about your logic but this is start

process.vbs
dim nfi, nfo1, nfo2, nfo3

nfi = "SampleFile.txt"
nfo1 = "ResulMisc.txt"
nfo2 = "ResultOH.txt"
nfo3 = "ResultMI.txt"

Set fi = CreateObject("Scripting.FileSystemObject").OpenTextFile(nfi,1)
Set fo1 = CreateObject("Scripting.FileSystemObject").OpenTextFile(nfo1,2,true)
Set fo2 = CreateObject("Scripting.FileSystemObject").OpenTextFile(nfo2,2,true)
Set fo3 = CreateObject("Scripting.FileSystemObject").OpenTextFile(nfo3,2,true)

Dim strLine
Dim rt, uid

do while not fi.AtEndOfStream
   strLine = fi.ReadLine()
   rt = Mid(strLine,1,2)

   'Do something with the line
   if rt="01" then 
      fo1.WriteLine(strLine)
   elseif rt="02" then 
      fo2.WriteLine(strLine)
   else 
      fo3.WriteLine(strLine)
   end if
loop

fi.Close
Set fi = Nothing

fo1.Close
Set fo1 = Nothing

fo2.Close
Set fo2 = Nothing

fo3.Close
Set fo3 = Nothing
based on first 2 character on each line, it splits input into 3 files
modify the logic to add more logic

Author

Commented:
Yes, command line batch script
Joe WinogradDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2018

Commented:
Hi Hain,
I don't code in VBS (so can't fix it), but I can see that your code doesn't work for two reasons: (1) When the record type is "02" (columns 1-2), there needs to be a check for the state (columns 253-254), which can be OH, MI, or something else. The code you posted does not do this. (2) When it finds a record type of "02" AND a state of OH (or MI or something else), it then must find ALL record types (not just the "02"s) with a matching unique ID (columns 3-13). The code you posted does not do this. This last requirement is somewhat tricky and likely will require multiples passes across the records. Regards, Joe
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
Hi Joe,

I did not say it is what user needs...
it is just a startup, and misses the logic, which I could not get and left empty...
the code just looks at first 2 char of each line to split the file...

think this is a container/startup, and adding the rest of logic should be straight forward...
Joe WinogradDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2018

Commented:
> it is just a startup, and misses the logic, which I could not get and left empty

Ah, sorry, Hain, I missed that comment in your first post.

> adding the rest of logic should be straight forward

I'm not so sure how straightforward it is...may be a bit tricky...do you have any ideas on how to do it in a straightforward way with VBS? In any case, I'll be keeping my eye on this. :) Regards, Joe
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
if only I can understand the requirement :)

here is another piece to the result:

Dim strLine
Dim rt, uid, t
do while not fi.AtEndOfStream
   strLine = fi.ReadLine()
   rt = Mid(strLine,1,2) ' 2 digit record type
   uid = Mid(strLine,3,13) ' 13 digit unique number
   t = Mid(strLine,253,2) ' OH MI

   'MsgBox "rt=" & rt &" uid=" &uid & " t="&t

   'Do something with the line
   if rt="01" then 
      fo1.WriteLine(strLine)
   elseif rt="02" then 
      fo2.WriteLine(strLine)
   else 
      fo3.WriteLine(strLine)
   end if
loop
Joe WinogradDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2018

Commented:
> here is another piece to the result

Yeah, that's the straightforward piece. :) Now that you found the state (OH, MI, or something else), you have to find ALL record types (not just the "02"s) in ALL the records with a matching uid...that's the not so straightforward piece, although certainly doable. Regards, Joe
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
Let's be honest: VB Script is dead - and rightfully so.
You didn't define the meaning of the two characters in position 253-254, so I called it "foo" ...
This here is PowerShell and creates files that match your samples.
It's actually pretty straightforward: collect all the rows with the same ID, get the 02 row, get the foo from that row, and write it to a file depending on foo.
$inFile = '.\SampleFile.txt'
$outDir = '.'
$outFileMap = @{
	'OH' = 'ResultOH.txt'
	'MI' = 'ResultMI.txt'
	'default' = 'ResultMisc.txt'
}

$outFileMap.Values | ForEach-Object {Remove-Item -Path "$($outDir)\$($_)" -ErrorAction SilentlyContinue}
Get-Content -Path $inFile |
	Select-Object -Property @{n='Type'; e={$_.SubString(0, 2)}}, @{n='ID'; e={$_.SubString(2, 13)}}, @{n='Foo'; e={$_.SubString(252, 2)}}, @{n='Row'; e={$_}} |
	Group-Object -Property ID |
	ForEach-Object {
		$foo = ($_.Group | Where-Object {$_.Type -eq '02'}).Foo
		If (-not $foo) {$foo = 'default'}
		$outFile = If ($outFileMap.ContainsKey($foo)) {$outFileMap[$foo]} Else {$outFileMap['default']}
		Write-Host "Exporting ID $($_.Group[0].ID) to '$($outFile)'"
		$_.Group | Select-Object -ExpandProperty Row | Add-Content -Path "$($outDir)\$($outFile)"
	}

Open in new window

Edit: Fixed missing output directory.
Joe WinogradDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2018

Commented:
> VB Script is dead - and rightfully so.

For me, it was never alive. :)

> so I called it "foo"

Based on the sample contents (OH and MI), I'm going with State.

> collect all rows with the same ID, get the 02 row, get the foo [State] from that row, and write it to a file depending on foo [State]

Very nice! Haven't tested your PowerShell code, but the logic sounds right! Regards, Joe
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
here the final piece, the diamond!

* I added my logic based on output provided... Hopefully it is correct :)

dim nfi, nfo1, nfo2, nfo3
nfi = "SampleFile.txt"
nfo1 = "ResultOH.txt"
nfo2 = "ResultMI.txt"
nfo3 = "ResulMisc.txt"

Set fi = CreateObject("Scripting.FileSystemObject").OpenTextFile(nfi,1)
Set fo1 = CreateObject("Scripting.FileSystemObject").OpenTextFile(nfo1,2,true)
Set fo2 = CreateObject("Scripting.FileSystemObject").OpenTextFile(nfo2,2,true)
Set fo3 = CreateObject("Scripting.FileSystemObject").OpenTextFile(nfo3,2,true)

Dim strLine
Dim prvLine
Dim hdrLine

Dim rt, uid, t
Dim n
n = 0

do while not fi.AtEndOfStream
   n=n+1
   strLine = fi.ReadLine()
   rt = Mid(strLine,1,2) ' 01 02 XX

   ' header
   if rt="01" then
      hdrLine = strLine
      uid = Mid(strLine,3,13) ' 13 digit unique number
   end if
   
   ' type
   if rt="02" then
      t = Mid(strLine,253,2) ' OH MI
      if t="OH" then
         fo1.WriteLine(hdrLine)
         fo1.WriteLine(strLine)
      elseif t="MI" then 
         fo2.WriteLine(hdrLine)
         fo2.WriteLine(strLine)
      end if
   end if

   if rt<>"01" and rt<>"02" then
      if uid <> Mid(strLine,3,13) then
         fo3.WriteLine(strLine)
      else
         if t="OH" then
            fo1.WriteLine(strLine)
         elseif t="MI" then 
            fo2.WriteLine(strLine)
         end if
      end if
   end if

   'MsgBox "Line=" & n & "    rt=" & rt & " uid=" & uid & " t=" & t
loop

fi.Close
Set fi = Nothing
fo1.Close
Set fo1 = Nothing
fo2.Close
Set fo2 = Nothing
fo3.Close
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
On a side note, in case this is one of your first encounters with PowerShell: here's a version you can save as a batch file (Whatever.cmd). Same as above, but the first two lines "disguise" the PowerShell as Batch.
And if you find new "foo" values to export separately, you can just adjust the hash table in $outFileMap; you can add and remove entries as you want (except for the 'default'), the rest will be handled automatically.
@PowerShell.exe -Command "Invoke-Expression -Command ((Get-Content -Path '%~f0' | Select-Object -Skip 2) -join [environment]::NewLine)"
@exit /b %Errorlevel%

$inFile = '.\SampleFile.txt'
$outDir = '.'
$outFileMap = @{
	'OH' = 'ResultOH.txt'
	'MI' = 'ResultMI.txt'
	'default' = 'ResultMisc.txt'
}

$outFileMap.Values | ForEach-Object {Remove-Item -Path "$($outDir)\$($_)" -ErrorAction SilentlyContinue}
Get-Content -Path $inFile |
	Select-Object -Property @{n='Type'; e={$_.SubString(0, 2)}}, @{n='ID'; e={$_.SubString(2, 13)}}, @{n='Foo'; e={$_.SubString(252, 2)}}, @{n='Row'; e={$_}} |
	Group-Object -Property ID |
	ForEach-Object {
		$foo = ($_.Group | Where-Object {$_.Type -eq '02'}).Foo
		If (-not $foo) {$foo = 'default'}
		$outFile = If ($outFileMap.ContainsKey($foo)) {$outFileMap[$foo]} Else {$outFileMap['default']}
		Write-Host "Exporting ID $($_.Group[0].ID) to '$($outFile)'"
		$_.Group | Select-Object -ExpandProperty Row | Add-Content -Path "$($outDir)\$($outFile)"
	}

Open in new window

Edit: Fixed missing output directory.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
If the input file is large, you will probably need to make two passes at the file.  On the first pass, collect the ID,State tuples in an dictionary/hashtable (or similar).  On the second pass, match the ID for the record and output/append the record to the appropriate output file.

Author

Commented:
I will try out some of these options on Monday. Files are not typically large so there should not be an issue there.

Just for clarification these are blocks of account information where a block of record types 01-09 (when appearing consecutively) are a placement file tied together by the uid in positions 3-13. I need to split based on the state in record 02. When other records 09-81 appear that do not have record types 01-02 they are updates to data we already have. Hope this helps in the understanding.

Powershell can be an option. I've never worked in Powershell so I'm not sure how close it is to VB and I'm old school (programmed in DOS basic when I started) so I stick to what I can understand and tweak down the road.

thank you everyone and I will update the post on Monday evening after I try what has been posted.
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
question was about writing it with vb script
which I gave the solution here

and PS version is accepted, and VB version is just ignored...

so, should we totally remove "VB Script" topic from EE? :)
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
so, should we totally remove "VB Script" topic from EE? :)

I know this was sort of a rhetorical question, but...

No way, just because it's long in the tooth doesn't mean people don't still use it, or have legacy stuff to support.  And in the case of VBS there are several use case for that, (1) Web page scripting, (2) Windows Scripting Host, (3) Some 3rd party applications use a VBS like syntax for user extensions, ...

It's just as valid and useful here as a topic as things like DOS, Cobol, etc...


»bp
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.