Results 1 to 5 of 5
  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2018

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

    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

    Any help is much appreciated!!!
    Thank you!
    Last edited by dday9; Mar 9th, 2018 at 08:53 AM. Reason: Added Code Tags and Removed Indents

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Sydney, Australia

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

    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:
    vb.net Code:
    1. For Each filePath In IO.Directory.GetFiles(folderPath)
    2.     'Use filePath here.
    3. Next
    Any variables you declare outside that loop can be used to store data across all files in the folder.
    Last edited by jmcilhinney; Mar 9th, 2018 at 06:55 AM.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Bristol, UK

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

    Welcome to VBForums

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

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    South Louisiana

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

    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())
    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()
                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)
                    End Using
                End Using
            Catch ex As Exception
                If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                End If
            End Try
        End Using
        Return dt
    End Function

  5. #5
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017

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


    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"))
            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)
                'in the CSV grid_Test.csv there is the Field Nummer I want to SumUp
            End Using
        End Sub
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.