Using batch, take specific numbers in a text file and sum them before writing the results back to the same txt file.

Michael Giardina
Michael Giardina used Ask the Experts™
on
I'd like to use a batch file to sum some specific numbers in a text file, unfortunately I do not have the skills required. In the following text file "test.txt", for all rows beginning with "622..." I need the second block of numbers summed together and put in a line after the last "622..." line. The total must retain any leading zeros so that the result will always be 15 digits long. It should also line up with the spacing of the values above it. The first number in the total line is a constant, as is the “COMPANY NAME”. At the end of this total line, the “00724…” number should maintain the chronological order of the one above it.

Original txt file:

101 123456789123456789123456789123456789BANK NAME              COMPANY NAME                   
5200COMPANY NAME                        1234567891CCDPAYMENT   123456789123   1072414250000001
62202123456789123456789      000056998811365          CHEMICAL MASTER LTD     0072414250000001
622021321321456987568        000149897016984          CHEMCO USA INC          0072414250000002
622011233214569874563211     000017285016585          GUME INC                0072414250000003
6220225411123658954212       000270139112936          DIOVIK GROUP            0072414250000004
6220432211655548541232       000000632017155          SANVED ENTERPRISES      0072414250000005
820000000500112340500000000000000000497895191123456789                         072414250000001
900000100000100000001234732205000000000000000004949519                                       
9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999

Open in new window


Example:

101 123456789123456789123456789123456789BANK NAME              COMPANY NAME                   
5200COMPANY NAME                        1234567891CCDPAYMENT   123456789123   1072414250000001
62202123456789123456789      000056998811365          CHEMICAL MASTER LTD     0072414250000001
622021321321456987568        000149897016984          CHEMCO USA INC          0072414250000002
622011233214569874563211     000017285016585          GUME INC                0072414250000003
6220225411123658954212       000270139112936          DIOVIK GROUP            0072414250000004
6220432211655548541232       000000632017155          SANVED ENTERPRISES      0072414250000005
6270154235698742245482       000494951975025          COMPANY NAME            0072414250000006
820000000500112340500000000000000000497895191123456789                         072414250000001
900000100000100000001234732205000000000000000004949519                                       
9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999

Open in new window


Any help at all with this would be greatly appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
In these situations, I usually write an app, as a batch file doesn't have the logic to do what you need (well, maybe it has, but it requires a REALLY REALLY long time to figure it out, and it definitely won't be easily readable, so bug hunting will be quite a chore)

So here's my solution:
The app itself: https://drive.google.com/open?id=14F526aTq5stkDqTWkP58-JLa-WD7EFmv
The whole solution itself if you don't trust the app (which you can compile yourself in the FREE MS Visual Studio Community): https://drive.google.com/open?id=1ZmpSk02K7bi-1NJAS-ZAzMouZh5F6L4N

And here just plain readable code:

Imports System
Imports System.Text
Imports System.IO
Imports System.Text.RegularExpressions


Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Form1_DragDrop(sender As System.Object, e As System.Windows.Forms.DragEventArgs) Handles Me.DragDrop
        Dim files() As String = e.Data.GetData(DataFormats.FileDrop)
        process_file(files(0))
    End Sub

    Private Sub Form1_DragEnter(sender As System.Object, e As System.Windows.Forms.DragEventArgs) Handles Me.DragEnter
        If e.Data.GetDataPresent(DataFormats.FileDrop) Then
            e.Effect = DragDropEffects.Copy
        End If
    End Sub

    Private Sub process_file(file_input As String)
        Dim lines = File.ReadAllLines(file_input)
        Dim sb As New StringBuilder
        Dim pattern As String = "([^|]*\|){3}"
        Dim found_622 As Boolean = False
        Dim finished_total As Boolean = False
        Dim total_count As Long = 0
        Dim temp_last_string As String

        For Each line In lines

            If finished_total Then
                sb.AppendLine(line)
            Else
                If Strings.Left(line, 3) <> "622" Then
                    If found_622 Then
                        'write total line
                        sb.AppendLine(process_total(total_count, temp_last_string))
                        sb.AppendLine(line)
                        finished_total = True
                    Else
                        sb.AppendLine(line)
                    End If
                Else
                    found_622 = True
                    sb.AppendLine(line)
                    'calculate total
                    total_count = total_count + process_line(line)
                    'save temp string 
                    temp_last_string = Split(line.Replace("   ", " ").Replace("  ", " ").Replace("  ", " "), " ").Last
                End If


            End If
        Next
        Dim new_file_name = Replace(file_input, ".", "_converted.") 'please don't use dots in the path name, or extra dots in the filename???

        Try
            File.WriteAllText(new_file_name, sb.ToString)
            MsgBox("successfully written file: " & new_file_name)
        Catch ex As Exception

        End Try

    End Sub

    Private Function process_line(input As String) As Long
        Dim temp = Split(input.Replace("   ", " ").Replace("  ", " ").Replace("  ", " "), " ")
        process_line = Long.Parse(temp(1))
    End Function

    Private Function process_total(input As Long, temp_last_string As String) As String
        process_total = "6270154235698742245482       " + Strings.Right("000000000000000" + input.ToString, 15) + "          COMPANY NAME            " + Strings.Right("0000000000000000" + (Long.Parse(temp_last_string) + 1).ToString, 16)

    End Function


End Class

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Batch calculations are limited to 32bit integer, you'd have to do the sum manually.
In PowerShell (it's 2018, after all), it's rather easy:
$inFile = '.\test.txt'
$outFile = $inFile -replace '(\.txt)\Z', '_out$1'
$totalCol1 = '6270154235698742245482'
$totalCol3 = 'COMPANY NAME'
$sum = $null
Get-Content -Path $inFile | ForEach-Object {
	If ($_ -match '\A(?<Col1>622[0-9 ]{26})(?<Col2>[0-9 ]{25})(?<Col3>.{24})(?<Col4>[0-9]{16})\Z') {
		$sum += [Int64]$Matches['Col2']
		$id = [Int64]$Matches['Col4']
	} ElseIf ($sum -ne $null) {
		$totalCol1.PadRight(29) + "$($sum)".PadLeft(15, '0').PadRight(25) + $totalCol3.PadRight(24) + "$($id + 1)".PadLeft(16, '0')
		$sum = $null
	}
	$_
} | Set-Content -Path $outFile

Open in new window

Author

Commented:
Wow! Both of these solutions met my needs exactly. They got the job done as-is.

Thank you so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial