Solved

Parse a Text File Based on Record Number in File

Posted on 2016-09-28
8
67 Views
Last Modified: 2016-10-04
I have a fixed length file I receive from a client that contains 12 potential record types. The records are identified by a 3 digit number in positions 33-35 called a Transaction-Code.

What I need is a batch program that can split the file by Transaction-Code and output the entire record as a text file:

Transaction-Code / File Output
110 / Record110.txt
111 / Record111.txt
210 / Record210.txt
211 / Record211.txt
213 / Record213.txt
214 / Record214.txt
300 / Record300.txt
400 / Record400.txt
700 / Record700.txt
800 / Record800.txt
900 / Record900.txt
910 / Record910.txt
ParseSample.txt
0
Comment
Question by:sparker1970
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41819762
What I need is a batch program...

Topics applied: Visual Basic Classic, .NET Programming, Windows Batch
Topics removed: Visual Basic Classic, .NET Programming
0
 
LVL 36

Expert Comment

by:Kimputer
ID: 41819771
Try this VB code (assumes you were open to VB besides a batch program)?

Imports System.IO

Module Module1

    Dim inputfile As String = "C:\Users\Kimputer\Documents\transactioncode.txt"
    Dim outputpath As String = "C:\Users\Kimputer\Documents"
    Sub Main()
        Dim sr As New StreamReader(inputfile)

        Do Until sr.EndOfStream
            Dim line = sr.ReadLine
            Dim number = (Left(Mid(line, 33), 3))
            Dim sw As New StreamWriter(Path.Combine(outputpath, "Record" & number.ToString & ".txt"))
            sw.WriteLine(line)
            sw.Close()
            sw = Nothing
        Loop
    End Sub

End Module

Open in new window

0
 
LVL 36

Expert Comment

by:Kimputer
ID: 41819800
Here's the DOS batch file variant (add fixed paths yourself, otherwise you need to run it where all the source AND target files should be):

@echo off
for /f "tokens=*" %%a in (transactioncode.txt) do call :processline %%a

goto :eof

:processLine
set str=%2
set str=%str:~0,3%
echo %1 %2 > Record%str%.txt
goto :eof

:eof

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 34

Expert Comment

by:it_saige
ID: 41820109
I would simply do something like:
@echo off
SETLOCAL DisableDelayedExpansion
FOR /F "usebackq delims=" %%a in (`"findstr /n ^^ test.txt"`) do (
	set "line=%%a"
	call :processLine line
)
goto :eof

:processLine
SETLOCAL EnableDelayedExpansion
set "line=!%1!"
set "line=!line:*:=!"
echo %line:~35% >> Record%line:~32,3%.txt
ENDLOCAL
goto :eof

Open in new window


Which from your example document:Capture.JPGProduces:Capture.JPGThe contents of each file are the sequence of characters after the 35th column; e.g. -Capture.JPG
-saige-
0
 

Author Comment

by:sparker1970
ID: 41822492
Kimputer - the batch script you provided is only pulling back single records...if I have 5 records with "300" in the position  am looking at, I need all 5 in the output. The batch syntax provided only outputs a single record to each output file.

I may have hundreds of records so I need all records with the identifier in each text file

it_saige - I'm not sure what your batch file is doing. I am not getting the whole record and it is nothing like the original record
0
 
LVL 34

Accepted Solution

by:
it_saige earned 500 total points
ID: 41822534
Is the whole record the line itself?

If it is then you need to change the batch to this:
@echo off
SETLOCAL DisableDelayedExpansion
FOR /F "usebackq delims=" %%a in (`"findstr /n ^^ test.txt"`) do (
	set "line=%%a"
	call :processLine line
)
goto :eof

:processLine
SETLOCAL EnableDelayedExpansion
set "line=!%1!"
set "line=!line:*:=!"
echo %line% >> Record%line:~32,3%.txt
ENDLOCAL
goto :eof

Open in new window


-saige-
0
 
LVL 34

Expert Comment

by:it_saige
ID: 41822605
I just did another test on the file you specified and added adding additional lines with the same markers specified.

Just for clarification, you have a file that is like what you stated, inside this file are individual lines that have a maker starting on the 33rd column and ending on the 35th column.  This marker specifies a 'Transaction-Code'.  You want to take this transaction code and write the line data (you need to specify if the line data is the full line or just the data after the 35th column) to a file that is called Record<Transaction-Code>.txt; e.g. Transaction-Code 210 = Record210.txt.  Am I right so far?

However, if Kimputer's works fine as far as parsing the correct data, then you simply need to change their script to this:
@echo off
for /f "tokens=*" %%a in (transactioncode.txt) do call :processline %%a

goto :eof

:processLine
set str=%2
set str=%str:~0,3%
echo %1 %2 >> Record%str%.txt
goto :eof

:eof

Open in new window

Use the '>>' in the echo line as opposed to '>'.
> is used to overwrite.
>> is used to append.

-saige-
0
 

Author Closing Comment

by:sparker1970
ID: 41828563
This worked perfectly and maintained the integrity of the record. Very easy solution to implement. Thank you very Much!!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you receive another warning that your shared drive is almost full and you have asked your users to clean out old files again and again, here is a single command that may help. This command will place all the files that have not been used rec…
You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question