Results 1 to 4 of 4

Thread: Transfer Tool - An open source program to add and edit players using spreadsheets

  1. #1
    Join Date
    14-06-14
    Location
    the English Channel
    Posts
    212
    vCash
    0

    Transfer Tool - An open source program to add and edit players using spreadsheets

    On and off over the past 18 months I have been working in conjunction with Dermotron to develop a new tool to try and make work on the Data Updates a little easier. The end result of this is the Transfer Tool. I am releasing this as open source software so that others can do what they wish with it (all you need is Qt Creator and Visual C++ 2015 Build Tools). The source code is included in the download zip (see link below). Note that the Tool was written in a bit of a piecemeal fashion and so I wouldn't claim it to be the tidiest set of code! I have released the Tool under the GNU General Public License v3 which basically means that you can edit and/or use my code so long as you release your program under the same licence (i.e. you make your program free and open source).

    Here is the download link to version 1.0.0: https://1drv.ms/u/s!AsXFKQMOdPqgiEtszMgGW6Q1k2ej


    System Requirements
    You must first install the Visual C++ Redistributable for Visual Studio 2015. Make sure you download the 32-bit (x86) version regardless of your version of Windows. The Tool is 32-bit which is why the 32-bit Redistributable is required.

    Windows 7 or higher is required (IIRC it won't work with Vista).


    What does this tool do?
    The Tool can be used to do any of the following:

    • Create new players and set certain basic data (contract details, international caps/goals, position, ability and reputation);
    • Edit certain basic data of existing players; and
    • Edit contract details of existing non-playing staff.


    The Tool is not intended to cover every single attribute. Instead, it is simply for editing the contract data, international caps/goals, positions, ability and reputation as this forms the core part of working on the Data Updates. If you want to edit all attributes then I suggest using my CM 01/02 Updater instead.

    I have written the Tool to be as fast as I can make it. For instance, the Tool can process 80,000 players from a spreadsheet and apply them into a database in around 1 minute 30 seconds.


    How to use it?
    A readme.html file is included with the download zip file. This is also displayed on the User Guide screen in the Tool itself.

    Included in the download zip file is a file entitled "import_template.csv" which you can use as a template to enter your edits to import.

    The Tool includes a number of settings to try and make importing your data as easy as possible. You can use long names and/or short names of clubs, automatically correct for differences in birth years (e.g. if the database has an offset/adjusted start year), preset alternative club names, protect players from being inadvertently overwritten, automatically calculate World Reputation and Salary, etc.


    Feedback/Questions
    If anybody has any feedback or questions then I'd be very interested. The primary aim is to ensure the Tool is useful for the Data Updates, but I'd be interested in seeing how it could be useful to others and their own projects.


    Screenshots

    Transfer Tool 002 by EHM The Blue Line, on Flickr

    Transfer Tool 001 by EHM The Blue Line, on Flickr
    Last edited by archibalduk; 19-07-17 at 09:05 PM.

  2. The Following 4 Users Say Thank You to archibalduk For This Useful Post:


  3. #2
    Join Date
    14-06-14
    Location
    the English Channel
    Posts
    212
    vCash
    0
    Here is the current readme file for reference:


    Step by Step Guide

    Step 1: Open the database

    Click on Open to open a database.

    Step 2: Open the spreadsheet

    On the Transfer Import tab click on Open File and select the spreadsheet you would like to import. The Tool will then process your spreadsheet.

    Note: Before opening the spreadsheet, ensure that the Row Buffer size at the top of the Tool's window is greater than or equal to the number of rows in your spreadsheet. A smaller Row Buffer may slow down the import process slightly.

    Step 3: Check for errors

    A process summary will be displayed in coloured boxes once the Tool has finished processing the spreadsheet as follows:

    • Players matched: The number of players in the spreadsheet which the Tool has identified as matching players in the database;
    • Players to be created: The number of players in the spreadsheet which the Tool could not match against players in the database. Consequently those players will be added as new players in the database;
    • Protected players: The number of players identified as being protected (see Protected Players and vlookup_protected_players.csv below); and
    • Rows with errors: The number of rows that contain a club and/or nation which could not be found in the database. These rows will not be processed if you proceed with the import.


    If the number of players to be created is more than you expected or if there are rows with errors:

    1. Click on Save File to save a copy of your spreadsheet;
    2. Open your saved spreadsheet and scroll to the far right columns of the spreadsheet where you will find the following additional columns:

      Staff ID: An ID of -127 means that the player will be created. Any other number refers to the player's Staff ID in the database;

      Nation 1 ID & Nation 2 ID: An ID of -127 means that the chosen nation does not exist and this should be corrected in the spreadsheet. A -126 means that the cell is blank (this is not an error - it just means that the existing Nation ID value in the database will not be modified). A -1 simply means no nation selected (this is not an error) and any other number refers to the nation's Nation ID in the database;

      Club ID & Loan Club ID: An ID of -127 means that the chosen club does not exist and this should be corrected in the spreadsheet. A -126 means that the cell is blank (this is not an error - it just means that the existing Club ID value in the database will not be modified). A -1 simply means no club selected (i.e. free agent - this is not an error) and any other number refers to the club's Club ID in the database; and

      Protected: 1 = the player is protected and 0 = the player is not protected.
    3. If you decide that the spreadsheet is fine and there are no errors then proceed with Step 4 below.
    4. If there errors that you need to correct then do the following:

      -- Click on the Close File button in the Transfer Import screen of the Tool. This will cancel the import process;

      -- Correct the errors in the spreadsheet (you can either edit your original spreadsheet or the one you saved from the Tool - it doesn't matter which); and

      -- Start again from Step 2 above.


    Step 4: Import the data

    Once you are happy that the spreadsheet has no errors, click on the Import Data button to import the spreadsheet into the database.

    Step 5: Close the spreadsheet

    Click on the Close File button in order to close the spreadsheet once the import process is complete.

    Step 6: Save the database

    Click on Save or Save As to save your database.


    Important Information

    Non-Playing Staff

    The Tool can be used to edit the contract data of non-playing staff but it cannot be used to (1) edit Non-Player Ability or Non-Player Reputation or (2) create new non-players.

    Contract Start Date

    This column is imported as Date Joined Club in the database.

    First & Second Name vs Common Name

    To edit an existing player, you can either enter his first and second name or his common name. You cannot edit an existing player's name or common name.

    When creating a new player, you can set their first and second name and/or their common name.

    Transfer Value, Wage and World Reputation

    Note that the Tool will automatically set a player's Wage and World Reputation based upon the Transfer Value entered into the spreadsheet (see the Transfer Value column). If you do not want this to happen then do not enter any Value.


    Settings

    Row Buffer

    This is used to fine-tune the performance of the Tool. The smaller the buffer, the less RAM used by the Tool (in theory). The buffer should be set to a value which is greater than or equal to the number of rows in the spreadsheet to be imported. If the buffer is less than the number of rows in the spreadsheet then the Tool may load the spreadsheet at a lower speed because it will need to continually reallocate memory for each row over and above the buffer whilst loading the spreadsheet (the effect of the speed decrease might only be marginal however). Unless you have very little RAM or you are importing a huge spreadsheet then you can usually leave this setting at the default value.

    Accent Matching

    Determines whether accents are taken into account when attempting to match any text from the imported spreadsheet against text in the database (e.g. person names, club names, competition names, etc). Use the enabled setting to apply strict accent matching. This means that "Dembele" would not match against "Dembélé". Use the disabled setting to relax matching such that "Dembele" in the spreadsheet would be treated as a match against "Dembélé". This means that you do not need to worry about using the correct accented characters in the spreadsheet.

    Club Name Lookup

    Determines whether the club names used in the imported spreadsheet will be corrected using the data from the vlookup_clubs.csv file which is located in the same folder as the Transfer Tool.exe file. Use the enabled setting to allow lookup correction. This is essentially a find and replace function. A "Find" and a "Replace" setting is specified in the vlookup_clubs.csv which the Tool uses to find and replace club names in the imported spreadsheet. This is intended to be a quick and easy way of correcting club names in the imported spreadsheet. Use the disabled setting to prevent any club name lookup.

    Club Name Source

    Determines whether the club names in the imported spreadsheet will be matched against short names and long names or short names only from the database.

    First Name Lookup

    This setting does not do anything yet.

    Protected Players

    Determines whether the players listed in the vlookup_protected_players.csv file will be protected when importing changes from a spreadsheet. Using the enabled will prevent protected players from having any of their protected details modified by the Transfer Tool. The disabled setting prevents this behaviour, meaning that no players will be protected.

    The protected details are: Footedness, Positions, Sides and Current & Potential Ability.

    Exchange Rate

    This setting can be used to correct transfer value data in the imported spreadsheet if it has been entered using a foreign currency. Use an Exchange Rate of £1.00 if no exchange rate correction is to be applied.

    Year Adjustment

    This setting allows the Transfer Tool to match up the dates of birth in the database against the dates of birth in the imported spreadsheet where different year adjustments have been used. To calculate the adjustment, take the year from the spreadsheet and subtract the year in the database. For example, if David de Gea's year of birth in the database is 1976 and it is 1990 in the spreadsheet, the adjustment to be entered is 14 (1990 - 1976 = 14).


    Vertical Lookup Settings Files

    The vertical lookup files are all located in the same folder as the Transfer Tool.exe file. These files are used to apply corrections to the spreadsheet import file similar to the Excel VLookup function or a find and replace function. Each vlookup file contains a Find: and a Replace: column. The Find: column is used to list out the text you want to find and correct. The Replace: column is used to list out the corrected text to be inserted in place (this text should match the appropriate text from the database).

    vlookup_clubs.csv

    Find and replace club names.

    vlookup_clubs_prefix_suffix.csv

    This file is different from the other vlookup files. Rather than searching and replacing, this file sets out club name prefixes and suffixes which are to be disregarded by the Transfer Tool when attempting to match club names. The prefixes/suffixes are only used as a last resort - the Transfer Tool will in the first instance try to match against the full club short/long name and only if it cannot find a match will it try again with the prefixes/suffixes disregarded. For instance, if "AFC" is contained in the vlookup file, you can use "Wimbledon" in the imported spreadsheed even if the club is called "AFC Wimbledon" in the database (or vice-versa). This is because "AFC" will be a disregarded prefix/suffix.

    vlookup_nations.csv

    Find and replace nation names.

    vlookup_protected_players.csv

    List the names and dates of birth of the players that are to be protected for the purposes of the Protected Players setting. The Staff ID column is populated automatically by the Transfer Tool (it will insert the IDs from the most recently loaded database and will auto-update them each time a new database is opened - do not attempt to set the ID numbers manually as they will be ignored and overwritten by the Tool).


    Other Settings Files

    delimiter_repair.txt

    Files using the csv format should use commas and semi-colons only for the purposes of indicating the end of a column/cell. However, it is easy to overlook this and mistakes can creep in. To mitigate against this, the delimiter_repair.txt file is used to auto-correct common mistakes. One such common mistake is to enter "Korea South" as "Korea, South". By listing this error in this file, the Transfer Tool will automatically remove the comma/semi-colon for you.

    settings.dat

    Internal settings for the Transfer Tool. Nothing to see here! ;-)

  4. #3
    Join Date
    14-06-14
    Location
    the English Channel
    Posts
    212
    vCash
    0
    Just one more thing to add: To edit an existing player or non-player, enter his name and DOB in the spreadsheet. Then just complete any of the other columns in order to edit those values. You can either enter the first and second name of the player/non-player or his common name.

  5. The Following 2 Users Say Thank You to archibalduk For This Useful Post:


  6. #4
    Join Date
    07-07-12
    Posts
    2,697
    vCash
    590
    I've tried this and I managed to get it to work

    Thanx Archibalduk! Great job, this mean the DB team can work on 24/7 and with more people working at the DB at the same time

  7. The Following User Says Thank You to CMCZ For This Useful Post:


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •