Attempting to add and populate a column with filtered data to an array
Hello,
I have a csv that contains 217 rows by 26 columns.
The csv is attached.
In my code I am trying the add the 27th and 28th columns "aplaynext", "hplaynext" where a is for the away team and h is for the home team. The rows relate to the matches and are in date order from the start of the season to the end of the season.
In my code I get the first team eg (row1) (Column3, ateam) and then filter for the next occurrence of that team which might be (row7) and if that next occurrence is in (column10, hteam) I get the teamname from the corresponding (column3, ateam) and put the name in (Column26, aplaynext) , or if they are in (Column3, ateam) then I get the team name from (column10, hteam) and put the name in (column27, hplaynext)
I go through the whole CSV until all the whoplayswhonext values are populated into columns 26 and 27.
then I am wanting to save the result to a csv called UpdatedFixture.csv
Unfortunately, no matter what I try, I can't seem to get the filtering right, and can't get the array to save.
I cannot figure out what I am doing wrong.
I am looking forward to your suggestions
Regards,
Antony
Code:
Private Sub WhoPlaysWhoNext()
' CSV Column descriptors (27 columns by 217 rows) comma seperated variables, all strings
' abehinds agoals ascore ateam ateamid complete date hbehinds hgoals hscore hteam hteamid id is_final is_grand_final localtime round roundname timestr tz unixtime updated venue winner winnerteamid year playnext
' <Integer> <Integer> <Integer> <String> <Integer> <Integer> 5/03/2026 19:30 <Integer> <Integer> <Integer> <String> <Integer> <Integer> <Integer> <Integer> <5/03/2026 7:30:00 PM> <5/03/2026 7:30:00 PM> <String> <String> +11:00 <Long> <5/03/2026 10:16:49 PM> <String> <String> <Integer> <Integer> <String>
Dim daysahead As Integer
For teamname = 0 To 17
For tvt = 1 To 217
If GV.NextGameTeams(teamname) = GV.The_FullFixture(tvt, 3) Then
If tvt > 198 Then daysahead = 217 - tvt
For nextgame = tvt To (tvt + daysahead)
If GV.The_FullFixture(nextgame, 10) = GV.NextGameTeams(teamname) Then GV.The_FullFixture(tvt, 26) = GV.The_FullFixture(nextgame, 3)
Debug.Print(GV.The_FullFixture(nextgame, 3) & " " & GV.The_FullFixture(nextgame, 26))
Next
End If
If GV.NextGameTeams(teamname) = GV.The_FullFixture(tvt, 10) Then
If tvt > 198 Then daysahead = 217 - tvt
For nextgame = tvt To (tvt + daysahead)
If GV.The_FullFixture(nextgame, 3) = GV.NextGameTeams(teamname) Then GV.The_FullFixture(tvt, 26) = GV.The_FullFixture(nextgame, 10)
Next
End If
Next
Next
Dim UpdatedFixture As String(Of String) = GV.The_FullFixture
My.Computer.FileSystem.WriteAllText(GV.FixturePath & "UpdateFixture.csv", UpdatedFixture, True)
End Sub
Re: Attempting to add and populate a column with filtered data to an array
Hi Guys,
Just a quick update,
I sorted out the save array issue. Apparently a good nights sleep AFK does wonders.
I still haven't been able to figure out how to reliably go through the csv and extract who plays who next and add that to the array called GV.The_FullFixture at column 26 and 27 respectively
Column 26 is aplaynext which is the Away Team Plays Next
Column 27 is hplaynext which is the Home Team Plays Next
Column 3 is the ateam which is the Away Team
Column 10 is the hteam which is the Home Team
The CSV is attached to the first post
I am hoping that your suggestions will lead me in the right direction to figuring out how to filter the CSV which is stored in the array GV.The_FullFixture for the desired values
Here is the updated code with the save working and adding the extra columns but only populating columns 26 and 27 with placeholder values
Code:
Private Sub WhoPlaysWhoNext()
' CSV Column descriptors (27 columns by 217 rows) comma seperated variables, all strings
'Column Numbers
' 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
' abehinds agoals ascore ateam ateamid complete date hbehinds hgoals hscore hteam hteamid id is_final is_grand_final localtime round roundname timestr tz unixtime updated venue winner winnerteamid year aplaynext hplaynext
' <Integer> <Integer> <Integer> <String> <Integer> <Integer> 5/03/2026 19:30 <Integer> <Integer> <Integer> <String> <Integer> <Integer> <Integer> <Integer> <5/03/2026 7:30:00 PM> <5/03/2026 7:30:00 PM> <String> <String> +11:00 <Long> <5/03/2026 10:16:49 PM> <String> <String> <Integer> <Integer> <String> <String>
Dim daysahead As Integer
For teamname = 0 To 17
For tvt = 1 To 217
If GV.NextGameTeams(teamname) = GV.The_FullFixture(tvt, 3) Then
If tvt > 198 Then daysahead = 217 - tvt
For nextgame = tvt To (tvt + daysahead)
If GV.The_FullFixture(nextgame, 10) = GV.NextGameTeams(teamname) Then GV.The_FullFixture(tvt, 26) = GV.The_FullFixture(nextgame, 3)
Debug.Print(GV.The_FullFixture(nextgame, 3) & " " & GV.The_FullFixture(nextgame, 26))
Next
End If
If GV.NextGameTeams(teamname) = GV.The_FullFixture(tvt, 10) Then
If tvt > 198 Then daysahead = 217 - tvt
For nextgame = tvt To (tvt + daysahead)
If GV.The_FullFixture(nextgame, 3) = GV.NextGameTeams(teamname) Then GV.The_FullFixture(tvt, 26) = GV.The_FullFixture(nextgame, 10)
Next
End If
Next
Next
Dim UpdatedFixture As String = Nothing
For row = 0 To 216
For column = 0 To 27
If column < 26 Then UpdatedFixture &= GV.The_FullFixture(row, column) & ","
If row = 0 And column = 26 Then UpdatedFixture &= "aplaynext" & ","
If row = 0 And column = 27 Then UpdatedFixture &= "hplaynext" & vbCrLf
If row > 0 And column = 26 Then UpdatedFixture &= "Away playing Next" & ","
If row > 0 And column = 27 Then UpdatedFixture &= "Home playing Next" & vbCrLf
Next
Next
If System.IO.File.Exists(GV.FixturePath & "UpdatedFixture.csv") = True Then
System.IO.File.Delete(GV.FixturePath & "UpdatedFixture.csv")
End If
My.Computer.FileSystem.WriteAllText(GV.FixturePath & "UpdatedFixture.csv", UpdatedFixture, True)
End Sub
Reply With Quote
