Visual Studio Community Garden

HalfBee

Member
If you don't have it... get it...
Visual Studio 2013 Community Edition is free from MS but requires Windows 7, 8, or higher (10 still in beta at this writing).

Visual Studio 2010 express is still available for Vista users (and if you are still using XP -- SHAME ON YOU!)

Not sure how much information can actually be shared through the forum, but we now have a place to discuss this programming environment and how it can be used with lottery theories presented.

HalfBee
 

doug_w

Member
Certainly great to find other people with an interest in using visual basic to help with lotto analysis.
 

HalfBee

Member
Jumping into the deep end...

Step 1: The data
Normally this is a text file, often CSV format, and usually has the most current draw at the top of the list. Most often it's downloaded from the lottery site directly and needs massaging, removing or combining columns, since we don't control the formats.

Do we ask our program users to preformat the data? This cuts down on our programming.
Or do we make it trainable (think excel imports) that once we set up the preset, we only need to have the file selection options?

More modern methods might suck it directly from the lottery site, not sure how the lottery folks will feel if we start doing nasty things with poor programming on our side :smash:

Since I use the data in other programs, it's already been massaged. The file sizes (Fantasy5 at over 7500 drawings) aren't really a consideration anymore since most modern systems have lots of memory and VS is efficient in holding data for manipulation.

(BTW) My import routines assume massaged data, and the trainable option is just on the TODO list for sometime when the demand for it is there...


HalfBee
 

doug_w

Member
Hi Halfbee

Downloading the files from the Lotto Organisers web site would not be any problem to them.

For each different lotto game you know the number of main balls, bonus balls and prize divisions (if you want to keep the prize info) /

Generally the downloaded file is in a CSV format and in the majority of times you will find it will be Draw Number, Date, Main Balls, Bonus Balls, Prize Divisions and then other stuff like Ball Set, Machine etc. And generally the first line in the CSV File has the Column Headings which you can use when making it trainable.

Parsing CSV Files in Visual Basic is easy. So making it trainable is not such a major problem.

Think long and hard about using Excel for the import remember there are many versions of Excel eg 200, 2007, 2010 you have to make your program be able to use XLS and XLSX files. Late Binding is an option.

Better way might be to create a Data Table with a key and suck the Data into that and then process the data from the Data Table.

Food for thought.
 

HalfBee

Member
I wish all the lotteries played nice, having consistant CSV files. Take a look at California's downloads, they are space justified columns of text and numbers that are deliberately designed to cause problems.

What possible use is day of the week? Already have the date, and even then they use Jan XX XXXX, which we need to covert to standard mm/dd/yyyy format for easier manipulations. One state (which I won't point fingers at) goes so far as to only put the listing in PDF format.

Wasn't suggesting actually using excel, although the coding for it is just a matter of file headers and available somewhere within the VS environment (remember reading that somewhere). But something like the import wizard it employs...

Read in first line, ask if it's column headers or not.
Read in 2nd line, choose either delimiter or spacing mode for data
Show column review and ask if any columns need deleted or combined, etc.

These things are only needed if you work with many lotteries. Most of the time you work with the same data over and over. Only when you use new data do you need to re-invent the wheel so to speak.

HalfBee
 

doug_w

Member
Apologies did not mean to use Excel but using the Microsoft.Office.Interop.Excel facility within Visual Basic.
 

doug_w

Member
Found This Interesting Piece of Code re CSV File

• I would suggest using the TextFieldParser to read in the CSV file. You can then load the values into a DataTable object and take advatange of its sorting capabilities. The sorted DataTable can then be written back out to a csv.

Here is an example of a simple implementation of the above idea. This code sorts 200,000 of your example records in about 4.5 seconds - not sure if that is fast enough for your application or not, but this code could probably be sped up and other tactics could also be faster. But this was meant to be easy to follow for a beginner:
Public Class Form1

'Sets up a large test file (200,000 records; ~3.5 MB)
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not System.IO.File.Exists("c:\test\test data.csv") Then
Dim sb As New System.Text.StringBuilder
For i As Integer = 1 To 50000
sb.AppendLine("160001, John, Doe")
sb.AppendLine("150227,Sue,Smith")
sb.AppendLine("160102,Ben,Cartwright")
sb.AppendLine("120222,Bill,Jones")
Next

System.IO.File.WriteAllText("c:\test\test data.csv", sb.ToString)
End If
End Sub

'Uses Button1, DataGridView1, and Label1 to display results of example
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
DataGridView1.AutoGenerateColumns = True
Dim sw As New Stopwatch
sw.Start()
SortCsv("c:\test\test data.csv", "c:\test\test data result.csv", New Integer() {2, 3})
sw.Stop()
Label1.Text = sw.ElapsedMilliseconds.ToString
DataGridView1.DataSource = CsvToTable("c:\test\test data result.csv")
'Should execute in about 4.5 seconds (~4500 ms)
End Sub

'Sorts the csv by turning it into a datatable, applying a view filter, and then writing the result back to a csv
Public Sub SortCsv(ByVal sourceFile As String, ByVal destinationFile As String, ByVal ParamArray sortColumns() As Integer)
Dim dt As DataTable = CsvToTable(sourceFile)
If sortColumns.Length > 0 Then
Dim sortStr As String = String.Empty
For i As Integer = 0 To sortColumns.Length - 1
If sortStr.Length > 0 Then sortStr &= ", "
sortStr &= "Column" & sortColumns(i).ToString
Next
dt.DefaultView.Sort = sortStr
End If
TableToCSV(dt.DefaultView.ToTable, destinationFile)
End Sub

'Parses a csv into a datatable
Private Function CsvToTable(ByVal filePathName As String) As DataTable
Dim result As New DataTable
If System.IO.File.Exists(filePathName) Then
Dim parser As New Microsoft.VisualBasic.FileIO.TextFieldParser(filePathName)
parser.Delimiters = New String() {","}
parser.HasFieldsEnclosedInQuotes = True 'use if data may contain delimiters
parser.TextFieldType = FileIO.FieldType.Delimited
parser.TrimWhiteSpace = True

While Not parser.EndOfData
AddValuesToTable(parser.ReadFields, result)
End While

parser.Close()
End If
Return result
End Function

'Writes a datatable back into a csv
Private Sub TableToCSV(ByVal sourceTable As DataTable, ByVal filePathName As String)
Dim sb As New System.Text.StringBuilder
For Each dr As DataRow In sourceTable.Rows
sb.AppendLine(String.Join(",", Array.ConvertAll(Of Object, String)(dr.ItemArray, _
Function(o As Object) If(o.ToString.Contains(","), _
ControlChars.Quote & o.ToString & ControlChars.Quote, o.ToString))))
Next
System.IO.File.WriteAllText(filePathName, sb.ToString)
End Sub

'Ensures a datatable can hold an array of values and then adds a new row
Private Sub AddValuesToTable(ByVal source() As String, ByVal destination As DataTable)
Dim existing As Integer = destination.Columns.Count
For i As Integer = 0 To source.Length - existing - 1
destination.Columns.Add("Column" & (existing + 1 + i).ToString, GetType(String))
Next
destination.Rows.Add(source)
End Sub
End Class
 

HalfBee

Member
Thanks for the example...

One of the things about the Visual Studio environment is the handy routines already provided, like the VisualBasic.FileIO.TextFieldParser you referenced. Many of the courses I've viewed online mention sifting through the runtime libraries for nuggets that provide solutions. The one I recently have been using reads it into an xml to dataset method and then spits out xml for direct dataset reading or other preformatted database needs.

Another nice touch in your example was the stopwatch to time a routine and give some feedback on processing throughput. That one I need to add to my current toolset...

HalfBee
 

HalfBee

Member
I do plan to present many more topics on this subject, but at the moment am a bit too busy to post much. The next phase details how to handle the draw data once we import it. We can put it in a dataset/database and that allows us to do some manipulation, or creating a class object which opens up many more options. This builds even more to the MAIN subject, designing a 'template' that gets us to a set starting point and has many of the routines we need later already available.

HalfBee

(btw was hoping you wouldn't just disappear)
 

doug_w

Member
HalfBee said:
I do plan to present many more topics on this subject, but at the moment am a bit too busy to post much. The next phase details how to handle the draw data once we import it. We can put it in a dataset/database and that allows us to do some manipulation, or creating a class object which opens up many more options. This builds even more to the MAIN subject, designing a 'template' that gets us to a set starting point and has many of the routines we need later already available.

HalfBee

(btw was hoping you wouldn't just disappear)

Re the BTW - thanks someone cares :)

I have just about completed my system in Visual Basic so if I can help you in anyway just ask,
 

doug_w

Member
Post removed. It is against the rules to encourage members to go to "a new lotto forum".
 
Last edited by a moderator:

HalfBee

Member
Funny how life issues put lottery dreams on the back burner as something to keep the mind distracted from reality.

Windows 10 conversion PITA done...
Now to update and locate all my project files and convert them to the latest VS yada yada yada and hope to revive this topic if anyone is still interested out there.

Will still be a month or more before activity resumes...
Watch this space...

HalfBee
 

Sidebar

Top