importing delimited in VB, ACCESS or EXCEL - Page 4 - Horse Racing Forum - PaceAdvantage.Com

Jeff P · 12-07-2004, 02:00 PM

Thomason,

In my original post I offered the following code sample for openeing a past performance file using VB or VBA in Access:

Quote:

Using VB to read a past performance file is not all that hard to do. Here's a code snippet that I've used with VB6 with great success for years. Something very similar should work as VBA code inside of Access 97 or Access 2000.

Create a standard EXE VB project and add a command button to your form.

In the declarations portion of your VB Project, paste in the following code:

Dim FieldVal (1435)
Public FILE as String

And then place this bit of code in the click event procedure behind the command button:

FILE = InputBox("Enter File Name:")
If Not FILE = "" Then
call ReadAFile(FILE)
end if

And then create a subroutine named ReadAFile. Here's the code for that; you can just copy and paste it in:

Private Sub ReadAFile(FILE)

Err.Clear
On Error Goto handler:

Open FILE for input as #1

Do While Not EOF(1)

'Read A Single Horse:

For j = 1 To 1435
Input #1, fileField
FieldVal(j) = fileField
Next j

'Done With Single Horse, now do something interesting.

Loop

handler:
If Not Err.Number = 0 then

MsgBox Err.Number & " " & Err.Description, vbCritical, "Error"
Err.Clear

Yesterday, you asked me how I would go about sending output
from the opened file to an html report.

HTML offers a great deal of power and flexibility for formatting
output. I greatly prefer it over using the Access Report Engine for
many many reasons. One of those reasons is that HTML is
portable. It's very easy to post a link to an html report on a
webpage. An html report sitting on a client machine folder will
open and display in a user's web browser whenever the user
gives the file a double click, etc. On top of that, once you
understand html and/or the DOM, you gain complete control over
the appearance of every element found in the document, etc.

I'm getting sidetracked here. My goal here isn't to tout HTML over
the Access Report Engine. Both are what they are.

Using VB or VBA, you can write every part of an html document to
an HTML file. This includes all tags, including meta tags,
style tags, script tags, head tag, body tag, as well as the more
common html tags for tables, paragraphs, break tags, etc.

One way to embed an HTML report into the above code snippet
would be to do something like this:

1. Create a Sub like the one below:

Code:

Private Sub WriteHTMLReport(FieldVal)

Open "HTMLReport.html" for Output as #2

'``````````````````````````````````````````
'Write title, style, script, html, and body tags
'``````````````````````````````````````````
strHorseHTML = "<html><head><title>JCapper HTML Report1</title>"

strHorseHTML = strHorseHTML & "<STYLE>" & vbCrLf & "<!--" & vbCrLf
strHorseHTML = strHorseHTML & vbCrLf
strHorseHTML = strHorseHTML & ".Content" & vbCrLf & "{" & vbCrLf & "FONT-WEIGHT: normal;" & vbCrLf & "FONT-SIZE: 11px;" & vbCrLf & "Font-FAMILY: Sans-Serif , Verdana , Arial" & vbCrLf & "}" & vbCrLf

strHorseHTML = strHorseHTML & ".ContentGray" & vbCrLf & "{" & vbCrLf & "FONT-WEIGHT: normal;" & vbCrLf & "FONT-SIZE: 11px;" & vbCrLf & "Color: gray;" & "Font-FAMILY: Verdana , Arial" & vbCrLf & "}" & vbCrLf
strHorseHTML = strHorseHTML & " -->" & vbCrLf & "</style>" & vbCrLf

'``````````````````````````````````````````
'script tag
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & "<script language=" & """" & "javascript" & """" & ">" & vbCrLf
strHorseHTML = strHorseHTML & "<!--" & vbCrLf

'``````````````````````````````````````````
'JavaScript functions
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & vbCrLf & vbCrLf

'displayInfoPassedIn
strHorseHTML = strHorseHTML & "function displayInfoPassedIn(sInfo)" & vbCrLf
strHorseHTML = strHorseHTML & "     // display the info passed in" & vbCrLf
strHorseHTML = strHorseHTML & "     {" & vbCrLf
strHorseHTML = strHorseHTML & "     alert(sInfo)" & vbCrLf
strHorseHTML = strHorseHTML & "     }" & vbCrLf
strHorseHTML = strHorseHTML & vbCrLf & vbCrLf

'``````````````````````````````````````````
'close script tag
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & "-->" & vbCrLf
strHorseHTML = strHorseHTML & "</script>" & vbCrLf


'``````````````````````````````````````````
'close out the head tag/write the body tag
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & "</head><body>"
Print #2, strHorseHTML


'``````````````````````````````````````````
'Print Button
'``````````````````````````````````````````
strClickToPrint = " onClick=" & """" & "javacript:self.print(); " & """"
strStyleCursorHand = " style=" & """" & "cursor:hand" & """" & "; "
strHorseHTML = "<table style='border:1px ridge #9966ff; position:relative; left:10px; "
strHorseHTML = strHorseHTML & "top:0px;' bgcolor='#ffffee'  >"
strHorseHTML = strHorseHTML & "<tr>"
strHorseHTML = strHorseHTML & "<td width='100%' class='ContentSmall' align='left' valign='top' " & strStyleCursorHand & strClickToPrint & " > "
strHorseHTML = strHorseHTML & "PRINT"
strHorseHTML = strHorseHTML & "</td></tr></table><br>"
Print #2, strHorseHTML


'``````````````````````````````````````````
'Display the horse name in a table
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & "<table><tr><td>"
strHorseHTML = strHorseHTML & FieldVal(45)
strHorseHTML = strHorseHTML & "</td></tr></table><br>
Print #2, strHorseHTML



'``````````````````````````````````````````
'Close out the body and html tags
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & "</body></html>"
Close #2


End Sub

2. Then, just under the comment in the original code snippet where it says:

"'Done With Single Horse, now do something interesting."

Simply place a Call to the above Sub, passing the array named
FieldVal that contains all of the horse data as an argument:

Call WriteHTMLReport(FieldVal)

You may have to play with it a bit to get the output you want. I
just did a cut and paste of pieces here and there from the HTML
Report I use in JCapper, but it does illustrate at least one
strategy for reading a past performance file to create an HTML Report as output.

Good Luck,

Jeff

Jeff P · 12-07-2004, 02:19 PM

Thomason,

I'd like to amend my previous post just a tad...

You don't wan't to place the call to WriteHTMLReport(FieldVal) just after the comment. Otherwise you end up with an HTML Report for each horse.

DOH!

You are going to need to insert logic to determine the start and end of each race and use a multidimensional array variable so that it contains an index for each track and race number as well as each horse number.

Something like this in the Declarations section of your project:

Code:

'FieldVal  (20 tracks, 250 races, 1500 horses, 1435 fields per horse)
Dim FieldVal(1 to 20, 1 to 250, 1 to 1500, 1 to 1435)

You want to place your call to WriteHTMLReport(FieldVal) after all races have been read from all loaded files.

Again Good Luck,

Jeff

Tom · 12-23-2005, 10:09 PM

Quote:

Originally Posted by Jeff P

Using VB to read a past performance file is not all that hard to do. Here's a code snippet that I've used with VB6 with great success for years. Something very similar should work as VBA code inside of Access 97 or Access 2000.

Create a standard EXE VB project and add a command button to your form.

In the declarations portion of your VB Project, paste in the following code:

Public FieldVal (1435)
Public FILE as String

And then place this bit of code in the click event procedure behind the command button:

FILE = InputBox("Enter File Name:")
If Not FILE = "" Then
call ReadAFile(FILE)
end if

And then create a subroutine named ReadAFile. Here's the code for that; you can just copy and paste it in:

Private Sub ReadAFile(FILE)

Err.Clear
On Error Goto handler:

Open FILE for input as #1

Do While Not EOF(1)

'Read A Single Horse:

For j = 1 To 1435
Input #1, fileField
FieldVal(j) = fileField
Next j

'Done With Single Horse, now do something interesting.

Loop

handler:
If Not Err.Number = 0 then

MsgBox Err.Number & " " & Err.Description, vbCritical, "Error"
Err.Clear

End If

Close #1

End Sub

Now, after enter clicking your command button and entering the name of a data file, here's what happens:

The file is opened. Each field is read from the file. The field value from the file is stored in the array. When each group of 1435 fields from the file have been read you are done with a single horse. It is at this point (at the comment that says 'Done With Single Horse, now do something interesting.) where you can begin picking the horse's past performance record apart.

I used 1435 fields for my example because I use Bris files which have 1435 fields per horse. Each array element corresponds to a field in the Bris Data File. For example, the horse's name can be found by looking at the value stored in FieldVal(45) which corresponds to field number 45 in the Bris File.

You can get a field map for Bris DRF Single Format Data files at:

http://www.brisnet.com/cgi-bin/static.cgi?page=drfsff

The same file reading strategy works with other file formats. You just need to know the number of fields in the file and what each one is supposed to contain.

Could this be used in VBScript to open data files and then select fields to use in calulations? Not really familiar with anyhting past DOS basic, but trying to learn VBScript.
Is VBS a form of Visual Basic?

Thanks,

Jeff P · 12-24-2005, 12:39 AM

First, a little background...

What is VBScript?

VBScript is a stripped down offshoot of VB. It's designed primarily to be run on a server for the purpose of presenting web pages. A lot of the functionality present in VB, particularly functionality related to handling files, isn't supported in VBScript. There is a good reason for this. If VBScript were able to perform file handling (renaming, editing, deleting, etc) then whenever you'd browse to web page with embedded VBScript you'd run the risk of having that web page do something malicious to your own machine. Microsoft wanted to avoid that risk so they stripped file handling capability out of VBScript when they released it as part of Visual Studio. Sure, there are other ways for programmers to have web pages do nasty things to a client machine, but the capability to do so isn't built in to VBScript.

Okay, so on to your question:

Quote:

Could this be used in VBScript to open data files and then select fields to use in calulations? Not really familiar with anyhting past DOS basic, but trying to learn VBScript. Is VBS a form of Visual Basic?

The OPEN and INPUT# statements aren't supported in VBScript. So no, the code block I presented won't work in VB Script as is.

Now, what you could do in VBScript is adopt a slighly different strategy for opening the file. You could register an object written in VB6 on the server. This object could have the ability to open and pick apart a data file. It could have public functions that return values back to the VBScript based web page where it is instantiated.

For example, suppose the VB6 object is named "FileReader" and registered on the server with a ProgID of "FileReader.ProgId." And suppose this object has a public function named getSpeedFigure() that returns a Speed Figure calculated from data pulled from a loaded data file.

A VBScript code snippet to present the speed figure on the web page might look something like this:

Code:

<%

Dim objFileReader
Dim intSpeedFigure

'Instantiate the FileReader object
Set objFileReader = "FileReader.ProgId"

'Get the Speed Figure
intSpeedFigure = objFileReader.getSpeedFigure() 

'Release Object References
Set objFileReader = Nothing

%>

At this point the variable named intSpeedFigure holds the value of the speed figure returned by the getSpeedFigure() function and could be presented on the web page along with whatever other data you are presenting to the user.

-jp

.

Tom · 12-24-2005, 10:49 AM

Thanks, Jeff, and Merry Christmas to you and yours.