-
Mar 8th, 2018, 08:45 PM
#1
Thread Starter
New Member
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
-
Mar 8th, 2018, 09:04 PM
#2
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:
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.
Last edited by jmcilhinney; Mar 9th, 2018 at 07:55 AM.
-
Mar 9th, 2018, 05:01 AM
#3
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.
-
Mar 9th, 2018, 10:24 AM
#4
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
-
Mar 9th, 2018, 12:36 PM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|