Solved

Parse a Text File Based on Record Number in File

Posted on 2016-09-28
8
55 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 35

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 35

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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 33

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 33

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 33

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Suggested Solutions

This article was inspired by a question here at Experts Exchange (http://www.experts-exchange.com/Software/Photos_Graphics/Images_and_Photos/Q_28629170.html). The requirements stated in that question are (1) reduce the file size of a large number of…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 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