How to add sums from multiple text files , save total, then move to next file.

[ottrwat]

I have a folder with multiple text files. I am trying to get it so it opens each file one by one, calculates the total, writes and saves at bottom, then moves to the next. For an example. write now in a folder I have 3 delimited text files with a value in the third column...

File1.text                       File2.text                     File3.text
word,word,3                   word,word,4                 words,words,6
words,words,3                words,words,2              words,words,4

I would like it to put a Total at the end of the file then move to the next like:

File1.text                       File2.text                     File3.text
word,word,2                   word,word,4                 words,words,6
words,words,3                words,words,2              words,words,4
Total: 5                         Total 6                       total 10

So far I can add the totals for a file but need help with doing all files in a folder. The files names and number of files are not set. This is what i have so far...

Dim filecontents As String() = IO.File.ReadAllLines("c:\File1.txt")
Dim Sum As Integer = 0 ' This holds the sum
For Each line In filecontents

    Dim tokens() As String = line.Split(New Char() {","c})
    Dim value As Integer = 0 ' This holds the current value

    If tokens.Length >= 2 Then
        If Integer.TryParse(tokens(2), value) Then
            Sum += value
        End If
    End If
Next

Any help is much appreciated!!!
Thank you!

[jmcilhinney]

This question really has nothing to do with reporting so I'm asking the mods to move it to the VB.NET forum.

As for the question, you can access all files in a folder like this:

For Each filePath In IO.Directory.GetFiles(folderPath)
    'Use filePath here.
Next

Any variables you declare outside that loop can be used to store data across all files in the folder.

[si_the_geek]

Welcome to VBForums

Thread moved from the 'Reporting' forum to the 'VB.Net' (VB2002 and later) forum.

[dday9]

What I would do is iterate through each file using the technique that JMcIlhinney provided you in post #2, then inside the iteration you would then convert the CSV file to a DataTable so that you could perform the Sum method on the last column. Here is a quick example:

Private Sub CalculateTotal(ByVal folderPath As String)
    Dim csv As DataTable = Nothing
    Dim total As Integer = 0

    For Each filePath In IO.Directory.GetFiles(folderPath)
        csv = ConvertCSVToDataTable(filePath)
        total = csv.AsEnumerable.Sum(Function(row) row.Field(Of Integer)(csv.Columns.Count - 1))
        IO.File.AppendAllText(filePath, Environment.NewLine & "File: " & total.ToString())
    Next
End Sub

Private Function ConvertCSVToDataTable(ByVal path As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        Try
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & IO.Path.GetDirectoryName(path) & ";Extended Properties=""Text;HDR=NO;FMT=Delimited"""
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM " & IO.Path.GetFileName(path), con)
                Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                    con.Open()
                    da.Fill(dt)
                    con.Close()
                End Using
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using

    Return dt
End Function

[ChrisE]

Hi,

this is a method I like to use to SumUp a Datatable..

 Public Function SumUp(ByVal TB As DataTable) As Double
        Dim SumVal As Single = 0
        For Each Rw As DataRow In TB.Rows
'SumUp Nummer
            SumVal += Convert.ToDouble(Rw("Nummer"))
        Next
        Return SumVal
    End Function

usage in a CSV file...

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim _tb As New DataTable

        Dim SrcDir As String = "C:\"
        Dim sConn As String = String.Join(";", New String() { _
           "Provider=Microsoft.Jet.OLEDB.4.0", _
           "Data Source=" & SrcDir, _
           "Extended Properties=""Text; HDR=YES; FMT=Delimited"""})

        Dim SQL As String = "Select * From [grid_Test.csv];"

        Using Cn As New OleDbConnection(sConn), ta As New OleDbDataAdapter(SQL, Cn)

            Cn.Open()

            ta.Fill(_tb)
            'in the CSV grid_Test.csv there is the Field Nummer I want to SumUp
            MsgBox(SumUp(_tb))

        End Using
    End Sub

regards
Chris