Solved

Parse a Text File Based on Record Number in File

Posted on 2016-09-28
8
31 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 32

Expert Comment

by:it_saige
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:sparker1970
Comment Utility
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 32

Accepted Solution

by:
it_saige earned 500 total points
Comment Utility
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 32

Expert Comment

by:it_saige
Comment Utility
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
Comment Utility
This worked perfectly and maintained the integrity of the record. Very easy solution to implement. Thank you very Much!!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The following is a collection of cases for strange behaviour when using advanced techniques in DOS batch files. You should have some basic experience in batch "programming", as I'm assuming some knowledge and not further explain the basics. For some…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now