JCapper Message Board

          JCapper 101
                      -- Exporting StartersToday data to CSV file

"Hi Jeff,

I'm not a programmer. But I eventually want to write my own macro or vba function to handle .JCP file data in Excel.

I'm new to JCapper but very familiar with HDW data.

If my first order of business is to pull PSR plus a handful of other data fields into Excel:

What's the easiest way to hit the ground running?"

1. Open .JCP files for race cards I am interested in.


2. Read in selected data fields for each horse.


3. Write selected data fields as output to a .CSV file.

1. Download HDW data files for today's race cards.
   
   Note that this step includes running .JCP File Build Routines on the downloaded HDW files. This creates comma delimited text .JCP files on the current active data folder of the hard drive.
   
   Note that the entire file download process can be automated. But recommend you get the hang of running the HDW File Download Tool manually first. (And then automate once you have a clear understanding of the manual process.)



2. Use the DFM Card Loader to load .JCP files into JCapper.



3. Use Scratch BOT to get scratches and changes.



4. Run a SQL Calc Races



5. Use the JCX File Exports Module to execute a saved sql expression and export a daily .CSV file out onto the hard drive.
   
   Note that the .CSV file will contain the exact data fields you want as defined by the saved sql expression. More on that below. (For now I'm just covering the basic steps from a high level.)



6. Double click the .CSV file created in the above step to open it in Excel (so that you can see and manipulate the data.)

1. On the Main Module click System Settings.



2. On the System Settings Interface click Enhanced Settings.



3. In the Enhanced Settings Module - change four of your settings so that they match the four highlighted settings shown in this screenshot:
   
   



4. Click the SAVE button after you make the four settings changes and x-out of the Enhanced Settings Module.

1. Launch the HDW File Mgr.



2. Click MENU - select SWITCHES - then select GETDATAFILES TODAY AUTOBUILD NOEXIT - and answer YES at the prompt to run the switch.
   
   Here's a screenshot of what the Switches Menu looks like in the HDW File Mgr:
   
   
   
   
   
   Each time you run the GETDATAFILES TODAY AUTOBUILD NOEXIT switch...
   
   The HDW File Manager will perform the following tasks automatically:
   
   
   • Navigate the HDW site and queue up today's data files.
   
   
   
   • Download today's data files to the current active data folder persisted in the DFM.
   
   
   
   • Run a .JCP File Build Routine on files downloaded in the previous step and create .JCP files on the current active data folder.
     
     --The best part is that ALL of this runs automatically without you having to click anything else.
   
   

1. With .JCP Files loaded into JCapper via the DFM Card Loader



2. And after getting scratches and changes



3. And with JCapper set to run in SQL Mode



4. Click the Calc Races button.
   
   Note that if you loaded .JCP files into JCapper on Data Folder One in the DFM Card Loader - to run a Calc Races on the .JCP files that you loaded using Data Folder One: Click the Calc Races button for Data Folder One.
   
   Note that if you loaded .JCP files into JCapper on Data Folder Two in the DFM Card Loader - to run a Calc Races on the .JCP files that you loaded using Data Folder Two: Click the Calc Races button for Data Folder Two.
   
   Note that if you loaded .JCP files into JCapper on Data Folder Three in the DFM Card Loader - to run a Calc Races on the .JCP files that you loaded using Data Folder Three: Click the Calc Races button for Data Folder Three.
   
   Clicking the Calc Races Button for any of the three data folders will cause a SQL Calc Races to be run on the .JCP files loaded into the program on that folder.
   
   During the Calc Races you see progress bars that (slowly) fill as the events that perform number crunching are executed. At the end you'll get a Calc Races completed message.

1. Launch the JCX File Export Module -- On the Main Module click System Settings. Then on the System Settings Interface click the JCX File Exports button. (That's it. Module launched.)



2. Launch the SQL Expression Tool -- Click the "Use Custom SQL Expression" box on the face of the JCX File Exports Module to launch the SQL Expression Tool.



3. Click the following link -- to get a text file that contains a sql expression you can use as a template for this project:
   http://www.JCapper.com/MessageBoard/Reports/PSR_Export.txt
   
   Note that in the sql expression at the above link I am referencing the [date], track, race, saddlecloth, horsename, and valf30 fields of the StartersToday table.
   
   FYI, in the Default SQL Factor Setup for JCapper Silver: PSR is assigned to the VALF30 field. (Meaning that the VALF30 field contains PSR.)



4. Having clicked the above link to the PSR_Export.txt file: Copy and Paste the sql expression from the text file into the large box on the face of the SQL Expression Tool.
   
   When you are done - your SQL Expression Tool should look like the SQL Expression Tool in this screenshot:
   
   
   
   
   
5. With the SQL Expression from the text file pasted into the SQL Expression Tool: Key a name for the SQL Expression into the Name field and hit the Save button.
   
   Note that in the following screenshot, I used "_PSR-Export" (without the quotes) as the name for my new SQL Expression. And that after keying a name into the Name field - and after hitting the Save button - Your SQL Expression Tool should look like the SQL Expression Tool in this screenshot:
   
   
   
   
   Q. Why give the SQL Expression a name and Save it?
   
   A. Saving a SQL Expression enables you to pull it up later (by name) and re-use it.
   
   To pull up a saved SQL Expression: Click the SQL Expression Names drop down - and then select the sql expression you want (by name.)
   
   As soon as you click the name of a saved sql expression in the drop down: The interface will display that sql expression in the SQL Expression Tool.
   
   So the next time you go about completing these steps to create a daily export .CSV file: Select the sql expression (by name) from the drop down - instead of keying it manually (or cutting and pasting it from the linked to text file.)
   
   Here's a screenshot:
   
   
   
   
   
6. Click the APPLY button and then click Yes at the prompt. This will apply the sql expression (to drive your export) and close the SQL Expression Tool.
   
   The following screenshot shows what your SQL Expression Tool should look like after clicking APPLY and right before clicking Yes at the prompt:
   
   
   
   
   
7. Click the Browse button on the face of the SQL Expression Tool. This will launch a Dialog Tool that will auto-point at your c:\JCapper\Exe folder.
   
   Note: The Dialog Tool provides visual elements that will enable you to point it at any folder on your machine. (Use those elements to point at a different folder as needed.)



8. Key the filename for your .CSV export file into the File Name box on the Dialog Tool and click the Open button. This will close the Dialog Tool and cause the interface to paste the file name from the Dialog Tool to the Output File Name box on the face of the JCX File Exports Module.
   
   The following screenshot shows the Dialog Tool after I keyed "psr-export-05182017.csv" (without the quotes) into the File Name box on the Dialog Tool just before clicking the OPEN button:
   
   
   
   
   
   The following screenshot shows "c:\jcapper\exe\psr-export-05182017.csv" (without the quotes) pasted by the interface into the Output File Name box of The JCX File Exports Module after clicking the OPEN button on the Dialog Tool:
   
   




9. Click the CREATE EXPORT button on the JCX File Exports Module to run the export.
   
   Clicking the CREATE EXPORT button causes the interface to execute the sql expression that was applied in step 6 (above) and write data pulled from (in this case) the StartersToday table to the .CSV eport file.
   
   The following screenshot the shows the JCX File Exports Module after after clicking the CREATE EXPORT button:
   
   
   
   
   At this point the .CSV file is now sitting on your hard drive.



10. Use Windows Explorer to Navigate to your c:\JCapper\Exe folder. (Or alternately, the folder you used in the File Name box in step 8 above.)
    
    The following screenshot shows the "c:\jcapper\exe\psr-export-05182017.csv" file (without the quotes) displayed in Windows Explorer:
    
    




11. Double Click your .CSV File to open it in Excel.
    
    The following screenshot shows the "c:\jcapper\exe\psr-export-05182017.csv" file (without the quotes) displayed in Excel:
    
    
    
    Note that the columns in the resulting .CSV file are the same as the data fields (or column names) referenced in the sql expression that was used to drive the export.
    
    Note that each row contains data for one horse.
    
    Also note that PSR is displayed in the VALF30 column.