importing delimited in VB, ACCESS or EXCEL

[masterpeg]

I have started learning VB and am not sure where I want to create my program yet (access or VB), I am leaning toward VB because of the flexibility. I haven't gotten too far into VB yet, but am feeling better and better about it as I go along.

I haven't begun sketching a design yet for my program because I'm waiting to learn more and get more ideas of the possiblilities, but I keep thinking of this one problem I encounter while importing my DRF delimited files into access and excel and wonder if there is away to avoid it in VB.

The problem is , I would want the program to be able to handicapp one race at a time, and to use selected running lines. Whenever I import I end up with all of the running lines for all the races without any visible option to discriminate. Can someone give me a little insight as to if this is possible in any of these programs.

[sjk]

It is easy to select lines based on your chosen criterion using Access. That is what queries are all about. I have never run up against anything I was unable to do with Access.

You could probably say the same for VB although I have never used it so I cannot help you with that.

Excel is good for many things but it does not have the versatility of a database program and I would consider it a poor choice.

Anything you use is going to require some time to learn and experiment with.

[masterpeg]

are you saying you can choose which lines to import, or are you saying you import all lines first then select?

[sjk]

Import first then select.

[masterpeg]

I don't want to do it that way though. I wan't to be able to select which ones to import. Can that be done with Access or VB?

I think it will pose many problems in coding the formulas if I can't select first.

If anyone here uses VB and knows please let me know so I can plan my design accordingly.

[osophy_junkie]

Your importing, selecting, and testing should all be different components. Having one big glob of code that does it all will decrease the amount of flexibilty available to you in the future.

Ed

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

[masterpeg]

That was a lot of information Jeff. I'm not sure if Im ready for it yet, but I will copy it and store if for when I am. It is very much appreciated. What you have managed to do is answer my question and keep my interest high so I now have the fortitude to continue with this interest.

Since you are handy with VB, I will ask you a couple questions. I have just started learning and been watching a tutiorial video I got from learnkey. I also have an older V5 book to look at from the library and one on the way for V6 from Amazon.com. So far I am becoming familiar with controls and some bits of code "variables, and conditions." I haven't look ahead too much but found myself thinking, "I wish I had a VB dictionary."

Am I thinking the wrong way here? Should I expect to just follow the step by step guides to the end and somehow attain enough knowledge to create what I need or is there an all in one resource guide to find quick answers to code questions. The reason I ask is because while searching the net for all things VB I found a lot of available source code for the taking (all of which means nothing to me yet) with various titles (which I don't understand yet) designed to do things I can't comprehend yet, but may need at sometime.

How will I ever know what they do and if I could use them? Should I just forget about them and learn what I can expecting to write all my own code for my project? do programmers often use others' code (isn't there some copywrite infringements)?

BTW, back to the original question and your solution. Remember, I'm a newbie here.....but, when you import your files, do you use "excel" worksheet in VB to present the data, or did you create text boxes? or some other way. No details needed, just a general answer so I know where Im heading here. Thanks for all your input.

[Jeff P]

Should I just forget about them and learn what I can expecting to write all my own code for my project?

For me it was a case of learning by doing. It's probably best to gain some understanding of VB programming techniques before attempting to tackle a handicapping app in VB.

I'd recommend getting your hands on some of the books by Microsoft Press. There's one called Visual Basic Step by Step that provides some very clear and thorough examples of what you can do with VB. Working through the examples can provide you a pretty solid foundation. If that seems a little over your head, get the Learning Edition of VB (check E-Bay.) It's written for beginners and comes with a book by Michael Halverson that's pretty good. That's how I got my start back in the day.


...but, when you import your files, do you use "excel" worksheet in VB to present the data, or did you create text boxes? or some other way.

I used VB to generate an HTML Report to present the data. I use this report for my handicapping on a daily basis. I also used VB to write a routine that merges the Bris data files with results files which then stores selected fields in a SQL table for later analysis. I then used VB to write a comprehensive analysis tool that lets me run queries against my database to see what factors are working (or not working) at specific tracks, distances, and surfaces. Then... I used VB to write a marker program that compares the past performance record of every horse in a Bris data file against my own wagering models. When this program finds a play that fits my own pre-defined criteria it "marks" the horse in such a way that the horse becomes highlighted on my HTML Report.

This last one is probably the most important part of my overall process. I no longer really have to handicap. All I have to do is look at my HTML Report each day to see those horses marked in red. Those are my potential plays for the day.

[Light]

Hi Jeff P

I was wondering if you ever came up with code to retrieve horse racing html files results and put them into access or excel with a particular purpose in mind. I remember asking you about trying to automate making variants a particular way.You said you were working on it. Don't know if you ever came up with anything,but if you have anything related that works with HTML horseracing results,(that you care to share),I would appreciate it. Thanks for your generosity.

[Jeff P]

One small correction to the code snippet I posted above:

In the declarations section, use:
Dim FieldVal(1435)

Instead of:
Public FieldVal(1435)

[Jeff P]

Light,

My apologies.

Was working on it sporadically and got sidetracked.

Shoot me an email.

[Larry Hamilton]

Jeff,
There is a similarity to my own code in what you do, though slightly different. You don't mention the "unzip" necessity, nor the need to include in your loop,"DIR", to capture all the files in your target folder. You saving those for a rainy day?

[Jeff P]

Larry,

I have tons of code that fires before the code snippet that I provided.

I created a separate module to clear and load individual race cards. To read files from different folders, I use a dual strategy. First I have a Drive ListBox, a Folder ListBox, and a File ListBox sitting on a form. Code behind the change event procedure for the drive listbox resets the folder and file listboxes. Code behind the folder listbox resets the file listbox. This interface very closely mimics the interface used by Microsoft in Windows Explorer. This combination of listboxes allows user navigation to any folder on my system. Code behind the double click event procedure for the file listbox will load a race card any time a user double clicks on the file name.

The second part of my file loading strategy involves instantiating Microsoft's File System Object (FSO.) I use this to populate an array that always contains the file names with a match for the extension characters ".drf" (these are the extension characters that all bris files of the type I use have) that are sitting in the currently selected folder. The module has buttons with code behind them that allow a user to either: 1. Clear all loaded race cards and: 2. Find and load all race cards for a specific date.

As for unzipping, I've been using a combination of Windows Explorer and WinZip.

Currently, I'm working on writing my own unzip file utility into the above module. My strategy here is to use the free Info Zip library (Unzip32.dll - this is the same library used by WinZip even though they aren't the ones who developed it.) and have my own program act as a "wrapper" so that I can call some of the methods contained in the library to unzip files.

[Larry Hamilton]

Sorry Jeff, I was trying to be funny and I didn't pull it off