Need Expertise from someone regarding csv files to database layout...

[AutumnLotus]

You'll have to excuse me if I don't entirely make sense here. But hopefully you'll understand what I'm trying to do. If not, just let me know. I could certainly clarify to the best of my ability. basically over 20 some odd years ago, when I was first introduced to the sport of thoroughbred racing and handicapping. The person who introduced me to it was knowledge. very knowledgeable and became my tutor and teacher. he was also a computer programmer, and he had written a simple. Program that enabled us to download all the past performances, all the entries for whatever tracks we wanted, and have it upload with the click of a button. All of the information into a database. That he had built which. included somewhere. around. I believe 108 different variables, each pertaining to the information in the past performances. I could be off by that number, but I'm pretty sure it. comes up to around 108 pieces of information in a past performance, I mean, we literally downloaded or uploaded every. Piece of information that a past performance had. Entries and results. at the beginning of the day. And then, of course, the results at the end of the day. So, just to clarify, we had a database at the time. we were using whatever the Lotus program was, I can't Remember, what Lotus's version for database was. You have Microsoft Access, Microsoft Excel, and then whatever Lotus was.. but again, to clarify, in the database, we had columns and rows. and it began. today's date., track name, race number the number of the horse post. post position and it continued straight across, filling in all the pertinent information of the past performance. So of course then there would be the horses morning light odds The name of the owner, the trainer, the jockey, the age of the horse, the class for today's race, Etcetera. Etc I'm sure you understand.

So I download past performances from BRIs single data file and when you double click it, it turns it into a CSV file. But when I open it in Excel this what it looks like. The field names and the correct data just arent there. I right clicked and saved the image in PROPERTIES so you can see it is a CSV file.

I don't know why this has become so difficult because. I just want to make a very easy database for myself. I don't care if I have to input the data by hand. It's. not a lot of data. I basically have about 30 fields that I want to enter and then certain races I want to put in the pertinent information. now if I could cut my time in half and be able to open this file and have it show the proper information that would be great.

Otherwise. if someone could simply help me. if I were to. send you. all the field names that I want. Like I said, I'll enter all the information by hand. But would someone be willing to assist me? in setting up. the basic outline of the database.? So in other words, If I could send someone. what field names I want running across the top Would someone be willing to create that for me? So that every day I simply open that file. And I. could in the information I want by hand. And what I am trying to do and what I want to be able to do. I mean is run queries. and reports like I used to back in the day 20 some odd years ago. and I just cannot remember how to do it.. I could make a quick mockup on a sheet of paper. and post it on here. Preventer understanding. As a matter of fact that's what I'm going to do. So let me do that. And I will post a follow up in about 10 or 15 minutes with what. I am trying to do. Thank you.

[AutumnLotus]

So here's just a quick example Of some of the fields that I want to have. Obviously, I want to put in more, but I want to have. access to design view and data sheet view and be able to run queries. So I would prefer to use Access. Over Excel but you guys tell me what's best. I appreciate any and all help.

[Jeff P]

Before I dive in, WELCOME to Paceadvantage.

Brisnet files are similar to but just a little different than CSV files.

CSV files are (typically) comma delimited text and like you mentioned, the top row contains column headers.

Brisnet files are comma delimited text (meaning the data fields are separated by comma characters.)

But they are considered comma delimited text files because the top row contains data instead of column headers.

Do a Google search for the phrase "brisnet library" (without the quotes) and you'll get a link to the Brisnet site in the search results.

From there, click the Datafile Structures link to see a page with links to field mapping for their various file types.

From there, click Single File Past Performances to see field mapping for the Brisnet DRF Single Format Data File.

The file structure is one row per horse with 1435 data fields per row.

Each of the data fields (or columns) are numbered.

For example, Field #45 is the name of the horse:

45 Horse Name CHARACTER 25

And fields #210 and #211 contain Run Style and Speed Points:

210 BRIS Run Style designation CHARACTER XXX 3
211 "Quirin" style Speed Points NUMERIC 9 1

At a minimum, the project your friend created involves:

1. Writing code to parse all 1435 fields for each horse from the data file, assign relevant pieces of data to variables, create significant factors from the variables, and write the variables to a database.

2. Doing the same with data parsed from a comma delimited chart results file.

3. Creating a database (which involves considerable forethought and design work.)

4. Writing code to query the database and generating reports that are actually useful.

I can tell you from personal experience doing this is a major undertaking.

I'm not the only one on this site who has done what you are describing.

There are quite a few others.

In addition to Brisnet, Equibase data is also available from HDW, DRF, TimeFormUS, Trackmaster, and Post Time Solutions (apologies if I left anyone out.)


-jp
.

[MJC922]

I would try setting up an excel sheet for this and import the csv into Sheet1 as a staging area. Make sure you have column headings in Sheet1 which follow your data structure here:
https://support.brisnet.com/hc/en-us...e-File-format-

Then in Sheet2 I'd setup an Advanced Filter, use only a subset of columns in the Advanced Filter to pull from Sheet1. Once you have only the columns you want then you can save that into a fresh new csv which can be imported into Access.

It sounds like a lot of work but you can do it if you stay after it.

[vegasone]

A CSV file is just that, comma separated values. Whether it has headers depends on how it was saved. You can determine whether to save headers or not depending on your needs. When you input the file into existing data the database can be set to use the header if it exists, or not used. Once a database is set up with headers you can eliminate further headers with a setting usually. No need to keep adding headers if the database will not recognize headers by itself.

[MJC922]

Originally Posted by MJC922

I would try setting up an excel sheet for this and import the csv into Sheet1 as a staging area. Make sure you have column headings in Sheet1 which follow your data structure here:
https://support.brisnet.com/hc/en-us...e-File-format-

Then in Sheet2 I'd setup an Advanced Filter, use only a subset of columns in the Advanced Filter to pull from Sheet1. Once you have only the columns you want then you can save that into a fresh new csv which can be imported into Access.

It sounds like a lot of work but you can do it if you stay after it.

Here's an example that I hope will help you to understand the process I've described above.

There's an Excel xlsb file attached to this post inside of a zip; assuming you have Excel you can use it to follow along with these steps:

First thing to do is import the single file from BRIS, I used the sample file on the BRIS website here and unzipped it:

http://www.brisnet.com/samples/aqu0207k.zip

Next in Excel (I'm using Excel 2010 btw) click the Data tab choose 'From Text'

Browse to the sample file e.g. AQU0207.DRF
(Note you will have to choose all files from the drop down to see the file because of the DRF extension)

Click on the file to select it and then click the Import button

Should now see the text import wizard pop up, choose Delimited and click Next

For Delimeters place a checkmark next to 'Comma' and click Next then click Finish

At this point it should ask where you want to put the data, Important! on Sheet1 choose cell =$A$2 (NOT =$A$1)

Click OK and Sheet1 should now be populated with data from the imported file

Next click on Sheet2

Notice the headers that I've placed up there, several of these are columns the OP requested in the handwritten note

Click the Developer tab now and Click Macros.

Note:if you don't have a Developer tab showing go here to show it:
https://www.techonthenet.com/excel/q...er_tab2010.php


Highlight Macro1 and click Run

Bingo, so now you have a subset of just the columns

Click File > Save As CSV (MS-DOS) give it a name, you can save it to your Desktop for now.

You will probably get a popup asking to only save the active sheet, click OK, click Yes to incompatible features

That's it, you've already saved a csv so you can exit out of Excel and do what you want with the new csv, import it into Access or whatever.

If you need more columns then you're going to have to manually add the headers you want to Sheet2 and you will also have to edit Macro1 so it looks past G1 over to H1 for the next column that you add and so on.

Happy Thanksgiving

[Saratoga]

thx mj ...thats some good work

[Ted]

You might find this interesting. https://alldataexcel.weebly.com/

[MJC922]

Originally Posted by Saratoga

thx mj ...thats some good work

My pleasure, hope it helps a few people at least with food for thought. This was kind of an unusual way to use Advanced Filter since there's no criteria being applied. Anyone who wants to get into Excel big-time though should make learning how to use Advanced Filter a priority.

[Saratoga]

Originally Posted by Ted

You might find this interesting. https://alldataexcel.weebly.com/

Yes...Alldata has been a valuable tool for people that wants to start using Excel in horseracing ...

Even thou it was developed around 2015 or so , it still works....because the Bris data file structure hasn't change much in 20+ years....

[Saratoga]

Originally Posted by MJC922

My pleasure, hope it helps a few people at least with food for thought. This was kind of an unusual way to use Advanced Filter since there's no criteria being applied. Anyone who wants to get into Excel big-time though should make learning how to use Advanced Filter a priority.

Your knowledge with Excel is excellent ..

You even helped me with Solver a few years ago.....

[Saratoga]

MJ , you got me to dust this project off and give it another try

In picture 1 .....Last 3 weeks of Woodbine DT SP stats (left out Maiden special weights as it skews any results )

It looks fine as its the usual numbers you get if betting every horse in every race

11% winners .78 Roi and $13 win price

Pic 2....Is what Solver gives you after running it

At a 108% roi looks like it did a fine job

(The factors I picked is a from a general selection , that I believe , will cover all aspects of the horse.....early late class form pedigree workouts etc etc

See how ranks 5 and above is negative values to ones ranked 1-4 (exactly what you should look for)




Pic 3 .....Applying it against fresh data is always the test

these are the plays it pick for today

[MJC922]

Thanks for the kind words. Yea solver is cool, we should probably have a dedicated solver thread because I'm sure that thread is probably long forgotten. I enjoy helping people get started on their own stuff, I could show a lot of people a lot of things when it comes to excel, it's just a matter of having the time, right now with working full time I can't do much in-depth stuff. An hour here and there I can spend. The only reason On-Target survives is because I spent hundreds of man hours fully automating it. Folks really don't need anyone else's software, many can build their own if they learn how to leverage excel and VBA. Especially if they're just bringing in someone else's file like this BRIS stuff. That kind of thing is well within a lot of people's grasp if they just get a little kickstart in the right direction. The only thing that's kind of out of reach for most people is going to be like what I'm doing with On-Target, the performance ratings the class ratings etc, because those are all 100% organic, self-generated every night, that's not simply taking someone else's 'stuff' and putting a twist on it which is pretty much what most people tend to be doing with spreadsheets.

[Saratoga]

Originally Posted by MJC922

Thanks for the kind words. Yea solver is cool, we should probably have a dedicated solver thread because I'm sure that thread is probably long forgotten. I enjoy helping people get started on their own stuff, I could show a lot of people a lot of things when it comes to excel, it's just a matter of having the time, right now with working full time I can't do much in-depth stuff. An hour here and there I can spend. The only reason On-Target survives is because I spent hundreds of man hours fully automating it. Folks really don't need anyone else's software, many can build their own if they learn how to leverage excel and VBA. Especially if they're just bringing in someone else's file like this BRIS stuff. That kind of thing is well within a lot of people's grasp if they just get a little kickstart in the right direction. The only thing that's kind of out of reach for most people is going to be like what I'm doing with On-Target, the performance ratings the class ratings etc, because those are all 100% organic, self-generated every night, that's not simply taking someone else's 'stuff' and putting a twist on it which is pretty much what most people tend to be doing with spreadsheets.

Would love the idea of starting a thread like that....

But people need to have data files.....I think 3 months might be a start to what's significant "Now"
And over a year for those long term analysis....

[Saratoga]

First 2 Won...

10-1 Winner in 6TH