Data Import/Conversion

[Kappa]

I want to import a bunch of data and result files into databases to research various angles and trainer moves. Currently, I use a very complicated and time consuming method to pull fields from the datafile and add them to a database. Can anyone help with a front end (Visual Basic, maybe??) to read the file into a database??

Thanks

[MV McKee]

Not certain what type of datafiles you are trying to import (format). I just use the chart files off of Equibase (HTML to Text). It is a bit of a pain to set up this import process initially, but once it is set up, it runs very quicky (@ 4 seconds per file), and it is nice to have the charts up 1 hour after the last race.
If you are purchasing the delimited format files from ITS, Trackmaster or some other source I assume you have set up a database to import the data into. If this is the case, is the difficulty you are having related to doing a mass import of a large number of files?
If so, I can send you a code snippet that creates a file system object and imports all or a set of specified files in one fell swoop. You will just have to fill in a couple of the blanks to reflect your own directory structure, archiving schema etc.
Hope this helps.

[Kappa]

MV McKee

I have several years worth of comma delimited files, the most recent being in the TSN single file format and the ITS data format, along with the comma delimited results files.

I do have a database setup to receive the files, but don't have a convient way of doing the mass data entry necessary.

The code would be greatly appreciated.

Thanks:)

[MV McKee]

OK, I cut and pasted a really basic itteration loop that I use to do mass data imports (see attached).
But this is really about 1% of what is involved.
The idea here is that you put all the raw (delimited?) data that you want to import into a single directory. In my code here that directory is "C:\Horse\Data\Charts\Eqb\Upload\".
You will want to replace that string with your directory path.
The second part, which you will have to formulate yourself, is a
subprocedure (in my case it is called MonrachOutput). Each file within that directory will be processed by that subprocedure.
In my case, I have the free Equibase Charts (not delimited) in the directory and I use MonarchOutput to parse tham out and JetExport into my Access or SQLServer tables.
You will likely (in the case of ITS) use DoCmd.TransferText and possibly a query or two to get the data into your tables. If you are importing the data verbatim (from ITS), you really need not go beyond the TransferText command with some Import Specs.
Anyway, whatever logic you use, put it in a subroutine and simply replace MonarchOutput and it's parameters with the name of your sub.
This is a very simple and bare-bones explanation, and since I have no idea of how much programming experience you have, it may be too much or not enough info. But I hope it provides a good starting point.

[MV McKee]

For some reason I can't get the text file to attach, so I am just pasting the code into the body of this message.


Private Sub cmdImportEqbCharts_Click()

Dim objFilesCollection As Object, objFile As Object, strFileName As String, strPath As String

strPath = "C:\Horse\Data\Charts\Eqb\Upload\"
Set objFilesCollection = GetFilesCollection(strPath)
For Each objFile In objFilesCollection
strFileName = objFile.Name
MonarchOutput strPath, strFileName
Next

---------------------------------------------------------------------------------------------------------------

Public Function GetFilesCollection(strPath As String) As Object

Dim objFileSys As Object, objFileSysFolder As Object, objFilesCollection As Object

Set objFileSys = CreateObject("Scripting.FileSystemObject")
Set objFileSysFolder = objFileSys.GetFolder(strPath)
Set objFilesCollection = objFileSysFolder.Files
Set GetFilesCollection = objFilesCollection

End Function

[Kappa]

Thanks

I'll play with it, and let you know how it comes out.