PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
How to add sums from multiple text files , save total, then move to next file.-VBForums
Results 1 to 5 of 5

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    1

    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...

    Code:
    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:

    Code:
    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...


    Code:
    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!
    Last edited by dday9; Mar 9th, 2018 at 09:53 AM. Reason: Added Code Tags and Removed Indents

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,784

    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 07: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
    Location
    Bristol, UK
    Posts
    40,118

    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
    Location
    South Louisiana
    Posts
    9,420

    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:
    Code:
    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

  5. #5
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,229

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

    Hi,

    this is a method I like to use to SumUp a Datatable..
    Code:
     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...
    Code:
     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
    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
  •  



Featured


Click Here to Expand Forum to Full Width