EzWinscore Registration Import

[Rob Boudrie]

The decision has been made to add a registration import feature to EzWinScore, and the code is currently under development. This will be released in a 4.X update to EzWinScore, and will be an officially supported feature.

Now, on to some details:

• Do NOT call USPSA HQ to ask about this. They will not be able to help, and the code is being developed in Boudrie Labs and has not yet been delivered to central command.
  
• No, you can't have a pre-release. Until sufficient testing is done, the risk of a data import problem causing EzWinScore problems in other features is just too great.
  
• No, we don't have a specific timeline yet - but I certainly expect it by spring or earlier.
  
• The initial version will support handgun, but may not initially support three gun or multigun.
  
• Input will be the user's choice of CSV or TSV (Comma/Tab Separated Values)
  
• Import will not update the EzWinScore master names database
  
• Do NOT call USPSA HQ to ask about this.
  

[Graham Smith]

The decision has been made to add a registration import feature to EzWinScore, and the code is currently under development.

Thank you.

[Jim Norman]

Rob,

Thank you! We currently use a pre-registration form to gather the names for a match. I would be happy to send you a copy to look at. Or you can simply go to our webpage and sign up for a match. You'll get asked all the questions to gather the required info and you can either submit or cancel out.

Being able to load Ez from a CSV or Excel file will make Ez more truly Ez!

Thanks again

[Rob Boudrie]

The format will be very simple, and 75% of the project is data validation. Import is all or nothing - a single error (for example, specifying a division that does not exist; a major power factor production shooter; etc.) will result in a message describing the problem and a refusal to accept the import data.

The file format will be very simple, and you will be able to supply data in CSV or TSV. It will be necessary to have a header line, and one line per competitor, with no gaps in competitor numbers.

Import will also be "whole match only". Import will not support adding competitors. If competitors are already defined in a match, you will be prompted asking if you wish to replace competitors with those form the import file. Registration import will be disabled once a score is entered for the match.

The format is going to be simple enough for a caveman to generate. The project is already underway and I won't be negotiating specific formats (translation: I won't rewrite the code so you app can use it without modification) but, if an inability to do something you should be able to do, or a bug, is found, it will be addressed.

Just pay attention to the first and last items in the list on my original posting

Being able to load Ez from a CSV or Excel file will make Ez more truly Ez!

It will not load an Excell file,. but as I expect you know, Excell can save a file in CSV or TSV format. I prefer TSV since CSV necessitates that you be certain no data field contains a comma like "Brian Enos, KBE".

[Poppa Bear]

Is there a good simple program out there that will do the CSV or TSV file? I have not dealt with online squadding or registration but are they easy to set up in a format that is importable to a CSV or TSV program so that they can then be dumped into the updated EZWin? I have made Adobe Acrobat files that are fill-able so that the party just needs to email back the completed form. I can also produce Excel sheets that will perform much the same function, but there has to be a program out there that is much easier to use and distribute that will allow the needed data to be input by the competitor.

[JThompson]

This is good news... I already have data collection in place for the Area 5 match. I was going to have a friend do some kludging to get it to import. One less thing to do manually for a big match will help out the masses for sure. I know MDs everywhere will be sending you a bottle of Scotch.

When it comes time for a beta, I should have a good sized db for A5 we can test if you like. PM me if you would like to use the data.

Until then, good luck...

JT

[Rob Boudrie]

Here is my long term vision:

1. When a major match is listed in the calender, the Match Director may elect to allow "self-registration"
   
2. If self-registration is enabled, the shooters may enter themselvse into the match and will show show up as "pending payment". The fact the person has signed up will show on the squadding page (under a separate "pending" section), but the will not be able to squad themselves, or be included int the match until....
   
3. The match registrar will receive payment via check, paypal, etc. The registrar then visits the admin page for the match and checks off the persons who have signed up but not paid.
   
4. The day before the match, the squadding is locked, and an EzWinScore registration file of all paid shooters is generated and downloaded.
   

The question is - would matches use this?

[JThompson]

Yes and no. It's a great idea, but we still need to process CC and get the payments in. I have it setup now so they can register and pay all in one place. If we do it this way I have to manually process the payment or have them put in all the CC info on my site as well and then match that data to a user. That's to much mucking around. What would be cool is an import to self squadding though. Say just the shooter name, email, USPSA # , and division. Then the self squad system would generate a password and send an email to each email addy with a unique password.

Combine that with the Ez import and I think you will have something quit workable.

That would pretty much cover everything on my wish list...

JT

Edited September 24, 2011 by JThompson

[Rob Boudrie]

What would be cool is an import to self squadding though. Say just the shooter name, email, USPSA # , and division. Then the self squad system would generate a password and send an email to each email addy with a unique password.

Combine that with the Ez import and I think you will have something quit workable.

That would pretty much cover everything on my wish list...

JT

Already done. Self squadding is populated by uploading a transfer file from EzWinScorr that contains exactly what you say, and fives the option of mailing a notice to the user. Members can using their exisitng PIN/PW in addition to the unique match password.

[MoNsTeR]

Awesome to hear this! I will gladly abandon my home-grown version in favor of an official release.

[JThompson]

What would be cool is an import to self squadding though. Say just the shooter name, email, USPSA # , and division. Then the self squad system would generate a password and send an email to each email addy with a unique password.

Combine that with the Ez import and I think you will have something quit workable.

That would pretty much cover everything on my wish list...

JT

Already done. Self squadding is populated by uploading a transfer file from EzWinScorr that contains exactly what you say, and fives the option of mailing a notice to the user. Members can using their exisitng PIN/PW in addition to the unique match password.

That's right, I was thinking outside of Ez, but as long as you can NOW import to Ez then the need for an independent import TO Self Squad is not needed.

No Rob, if you get the CSV type import going you will have taken a huge step to simplification on our part. This will save us a boatload of time and also decrease the number of errors from keying all that data by hand.

JT

[Jim Norman]

To set up a registration link on your website is fairly easy and inexpensive. We use a company called Formstack. We set up a form that requires certain information and returns to a csv or excel sheet. cost is under $10 a month. It will send a return email to the registrant confirming what he submitted. If someone registers twice, what I currently do is sort the file and delete the duplicates. We've been using this for about two years now.

[bgary]

Awesome news. Does this mean Kim will be able to retire "the access database"?

If so... it is long past time. Thanks.

[wgnoyes]

<Small bit of drift> Hopefully. The Access preregistration system does get them pre-match reports that ezws never considered having, like shirts (I think), and who's working where, and name tags and such. I don't know how much of that is still used. One thing the access system had first that HAS been ported over to ezws was the generic competitor stage labels, that is, 'x' number of stage-independent labels so that you give the competitor a sheet of labels and he goes from stage to stage where he peels off a label and sticks on the scoresheet waiting for him there. We even used the same layout with the big comp# in the square.

[Rob Boudrie]

One possibility is to continue to use the current Access database, but add a TSV or CSV export so the transfer of data into EzWinScore will use a standard interface, rather than open an ODBC connection and stuff data into the tables directly.

This will be easier (few people actually know how to do the ODBC hackery with the current utility), and force the data through a single supported interface that includes data validation. It would also mean that USPSA HQ would be using an officially supported method of importing registration data rather than a procedure that would be considered dis-recommended and unsupported if done by one of our clubs.

[JThompson]

Keep us in the loop will ya Rob?

JT

[1tcgc]

One possibility is to continue to use the current Access database, but add a TSV or CSV export so the transfer of data into EzWinScore will use a standard interface, rather than open an ODBC connection and stuff data into the tables directly.

This will be easier (few people actually know how to do the ODBC hackery with the current utility), and force the data through a single supported interface that includes data validation. It would also mean that USPSA HQ would be using an officially supported method of importing registration data rather than a procedure that would be considered dis-recommended and unsupported if done by one of our clubs.

Any recent updates on how soon we might have this available? We got our 1st registration in for 2012 Area 5 match today.

[Rob Boudrie]

This will be available very, very soon (almost certainly within a week)

The 4.08 build is done, and HQ is having some preliminary testing done. I expect to have the file format up on www.uspsa.org by the end of the week, however, I've pasted the data below as a preview. The update will be available on the USPSA EzWinScore page when it's released. If anyone calls HQ asking them to hurry up, I will suggest they delay release for a few days. Seriously - calling Roger is NOT going to speed things up, and will just suck his time away from getting the job done.

It seems to be working pretty well, however, there hasn't been much testing with Three Gun (particularly the calculation of the aggregate division based on the divisions for Rifle, Pistol and Shotgun).

If you are using this for a Level II or above match and have problems, email me directly (rob at boudrie dot com), and be sure to attach a copy of the file.

I recommend TSV rather than CSV since commas in the CSV file will confuse the parser since it's fairly simple and isn't fully RFC4180 compliant.

Registration Import file		
TSV or CSV		
EzWinScore prompts for name and location		
First line contains column labels, separated by tabs
Data starts on the line #2, with fields as follows:		

Field   Content
-----   -------
1	compno	Competitor number
2	uspsa member number	
3	first_name	
4	last_name	
5	address1	
6	address2	
7	city	
8	state	
9	zipcode	
10	country	
11	phone	
12	foreign	bool
13	email	
14	division	blanjk, Junior, Senior or Super Senior
15	female	bool
16	military	bool
17	law	bool
18	age	Same as prompted for in EzWinScore 
19	deleted	
20	power_factor	
21	rifle_power_factor	
22	shotgun_power_factor	
23	rifle_entered	bool
24	shotgun_entered	bool
25	rifle_division	
26	aggregate	boolean (true if aggregate entered)

Approved division abbreviations		

LIM	Limited	
LTD	Limited	
L10	Limited 10	
Lim 10	Limited 10	
SS	Single Stack	
REV	Revolver	

Booleans may be True/False Yes/No or 1/0.  Note that the other files require Yes/No only.  Blank defaults to False/0/No.		

Competitor numbers must start at 1 and contain no gaps in sequence (but it is OK to mark a competitor as deleted)		

[TreblePlink]

Hi Rob, keep of the dedicated work!

"1","A41275","Fred" or no quotes ? or either way ?

Tabs, of course aren't visible in many editors ...

Also - what about incomplete fields like country or phone ...

I recommend TSV rather than CSV since commas in the CSV file will confuse the parser since it's fairly simple and isn't fully RFC4180 compliant.

Registration Import file		
TSV or CSV		
EzWinScore prompts for name and location		
First line contains column labels, separated by tabs
Data starts on the line #2, with fields as follows:		

Field   Content
-----   -------
1	compno	Competitor number
2	uspsa member number	
3	first_name	
4	last_name	
5	address1	
6	address2	
7	city	
8	state	
9	zipcode	
10	country	
11	phone	
12	foreign	bool
13	email	
14	division	blanjk, Junior, Senior or Super Senior
15	female	bool
16	military	bool
17	law	bool
18	age	Same as prompted for in EzWinScore 
19	deleted	
20	power_factor	
21	rifle_power_factor	
22	shotgun_power_factor	
23	rifle_entered	bool
24	shotgun_entered	bool
25	rifle_division	
26	aggregate	boolean (true if aggregate entered)

Approved division abbreviations		

LIM	Limited	
LTD	Limited	
L10	Limited 10	
Lim 10	Limited 10	
SS	Single Stack	
REV	Revolver	

Booleans may be True/False Yes/No or 1/0.  Note that the other files require Yes/No only.  Blank defaults to False/0/No.		

Competitor numbers must start at 1 and contain no gaps in sequence (but it is OK to mark a competitor as deleted)		

[Rob Boudrie]

"1","A41275","Fred" or no quotes ? or either way ?

Tabs, of course aren't visible in many editors ...

Also - what about incomplete fields like country or phone ...

No quotes needed - I'm not sure if the system routine I use implicitly strips qoutes out or not, but I have been doing my runs without them.

The easiest way to look at a transfer file, or manually generate one, is Microsoft Excell. TSV is valid for import and export.

Fields like Country and Phone are optional.

Member number has no dash, and may also be PEN or blank.

[Mark R]

Rob,

What are your division abbreviations for open and production? Also, where do we put in the shooters classification?

Mark

[Jim Norman]

Rob,

do we need all the fields for import to work?

All we need for a level one is number, name, division, class (actually that comes from the updater) power factor ans maybe age and gender, others may also want to add in LE and Mil.

I tried to list the info in a txt file tab delimited and I cant seem to get it to work. All I get is a message that I have an invalid competitor name.

We separated the USPSA prefix and numerical in our form. we tried to do drop down lists for most items as it is difficult to get people to actually fill out a form completely without the choices being presented to them. I realize I will have to recreate our form. FormStack gives me the following options, Excel, CSV and RTF.

Edited November 29, 2011 by Jim Norman

[Rob Boudrie]

1. There is no import of class. EzWinScore allows the user to update classifications using a classification file imported into EzWinScore.

2. OPEN and PROD should work for divisions. The identical spelling used in EzWinScore, plus some aliases, are supported. The listings in the earlier posts are the "Aliases" (for example, Limited is allowed as is the alias LTD).

3. If you have trouble, email the import file you are trying to "rob@boudrie.com". As long as the load remains manageable, I will report on the problem. I will try to post the info here so others can learn from any data format issues.

rob

[JThompson]

ETA?

[Graham Smith]

do we need all the fields for import to work?

Here is a possible solution to this. If I leave anything out of the explanation, it's because I'm too used to doing things like this rather than explaining them

Pre-format a spreadsheet with all the fields and with default values in the first row. Save this as a template. To start a new import, either copy the spreadsheet or copy it to a new tab.

Import your data from a CSV or TXT file and let it fill in the first few columns.

Copy the remaining columns from the first row and paste them into the subsequent rows.

Save the result as a CSV file. You now have a file with ALL the fields you need to import.

_________________

Addendum: Right now, the primary issue I have with this whole process is that it does not add information to the Master. This means that we will still have to go through the import and find all the new people and add them. For a Level II or above match, this is probably a non-issue because there is no need to keep the master file up to date, but in L1 matches, it's important.

Edited November 29, 2011 by Graham Smith