Results 1 to 17 of 17

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
    217
    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 5 Users Say Thank You to archibalduk For This Useful Post:


  3. #2
    Join Date
    14-06-14
    Location
    the English Channel
    Posts
    217
    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. The Following 2 Users Say Thank You to archibalduk For This Useful Post:


  5. #3
    Join Date
    14-06-14
    Location
    the English Channel
    Posts
    217
    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.

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


  7. #4
    Join Date
    07-07-12
    Posts
    3,143
    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

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


  9. #5
    Join Date
    07-07-12
    Posts
    3,143
    vCash
    590
    So I've tried to import some players. Not sure what I've done wrong as I got it working before. Used this spreadsheet:

    https://www.sendspace.com/file/9pei2s

    he created the players, but except the players name almost everything seemed to go wrong..

  10. #6
    Join Date
    14-06-14
    Location
    the English Channel
    Posts
    217
    vCash
    0
    You're using the wrong spreadsheet template for the Transfer Tool. There is a template file called "import_template.csv" included in the ZIP download link in the top post. It looks like you have been trying to use the Updater spreadsheet template (which is not compatible with the Transfer Tool).

    Alternatively, create a new blank text file and copy and paste the below into it. Then change the file extension of the text file to ".csv" and open it in Excel. That will give you the correct template.

    Code:
    First Name;Last Name;Common Name;DOB;Nation1;Nation2;Contract start;Contract end;Transfer Value;Club;On loan from;GK;SW;D;DM;M;AM;ST;WB;FR;Right;Left;Centre;Right Foot;Left Foot;Int Caps;Int Goals;Current Ability;Potential Ability;Home Reputation;Current Reputation;Squad Number

  11. #7
    Join Date
    07-07-12
    Posts
    3,143
    vCash
    590
    Thanks for the quick answer! I will try it!

  12. #8
    Join Date
    31-10-11
    Posts
    21,245
    vCash
    3000
    I definitely need to give this a whirl, both for:

    CM Vipers - Where I create a team in each new data update and add members from this site as players into it. Shouldn't be an issue?

    DTL - Where a number of existing players have been signed by managers in the competition so you'd get for example Muller and Hazard at Aston Villa. I imagine this one is a lot more complicated though?
    The Alphabet Game #3
    Try your luck at winning any trophy in your game but you must go in order from A-Z in the alphabet!
    Click here to find out more and join in!

  13. #9
    Join Date
    14-06-14
    Location
    the English Channel
    Posts
    217
    vCash
    0
    CM Vipers - Yes but note you can only set basic attributes for now (e.g. CA, PA, etc).

    DTL - Do you mean transferring existing players in a DB? If so, this will do it. Just enter the player's name and DOB along with the name of the new club.

    Sorry for the brevity of this post - I'm typing from my mobile. I'd be happy to post screenshots of a few examples if that would help.

  14. #10
    Join Date
    31-10-11
    Posts
    21,245
    vCash
    3000
    Not to worry, that pretty much answers my questions

    As the DOB changes each year after each data update release, I guess if I do keep a log of all player names, DOB and clubs, I can use Excel to roll back a year in the spreadsheet before using the transfer tool.

    Great stuff, going to save me so much time as I've literally inputted 12 seasons of transfers from the DTL into the DB every time there's been a new release since 2014
    The Alphabet Game #3
    Try your luck at winning any trophy in your game but you must go in order from A-Z in the alphabet!
    Click here to find out more and join in!

  15. #11
    Join Date
    04-03-12
    Location
    Serbia,Belgrade,Zemun
    Posts
    937
    vCash
    900
    How hard is to add player attributes when creating new player?

  16. #12
    Join Date
    14-06-14
    Location
    the English Channel
    Posts
    217
    vCash
    0
    Quote Originally Posted by Mark View Post
    As the DOB changes each year after each data update release, I guess if I do keep a log of all player names, DOB and clubs, I can use Excel to roll back a year in the spreadsheet before using the transfer tool.
    Actually you don't have to worry about this! The Transfer Tool has a Year Adjustment setting which will adjust the DOBs in your spreadsheet when importing them. So just enter the real life DOBs in the spreadsheet and then set the Year Adjustment setting in the Transfer Tool accordingly. If you take a look at the screenshot in the first post of this thread, you'll find the Year Adjustment setting in the bottom right hand corner.

    To calculate the correct Year Adjustment setting you subtract the person's year of birth in the database from the year of birth in the spreadsheet. E.g. if the player's DOB is 29/09/1995 in the database and it is 29/09/2004 in the spreadsheet then the Year Adjustment should be set to 9 (2004 - 1995 = 9). Let me know if that's not clear or if you've got any other questions.

    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).
    __________________________________________________ __________________________________________________ ___

    Quote Originally Posted by djole2mcloud View Post
    How hard is to add player attributes when creating new player?
    In terms of adding this function to the Transfer Tool? Not especially hard; it's more a case of having spare time to add in this functionality. I plan to do so eventually but only once my work on Eastside Hockey Manager has settled down a bit (we are working on the new season's database right now).

  17. #13
    Join Date
    04-03-12
    Location
    Serbia,Belgrade,Zemun
    Posts
    937
    vCash
    900
    Quote Originally Posted by archibalduk View Post
    Actually you don't have to worry about this! The Transfer Tool has a Year Adjustment setting which will adjust the DOBs in your spreadsheet when importing them. So just enter the real life DOBs in the spreadsheet and then set the Year Adjustment setting in the Transfer Tool accordingly. If you take a look at the screenshot in the first post of this thread, you'll find the Year Adjustment setting in the bottom right hand corner.

    To calculate the correct Year Adjustment setting you subtract the person's year of birth in the database from the year of birth in the spreadsheet. E.g. if the player's DOB is 29/09/1995 in the database and it is 29/09/2004 in the spreadsheet then the Year Adjustment should be set to 9 (2004 - 1995 = 9). Let me know if that's not clear or if you've got any other questions.



    __________________________________________________ __________________________________________________ ___



    In terms of adding this function to the Transfer Tool? Not especially hard; it's more a case of having spare time to add in this functionality. I plan to do so eventually but only once my work on Eastside Hockey Manager has settled down a bit (we are working on the new season's database right now).
    Yes,that is what i meant.
    Further,JL tool has this option for adding all attributes of a player to DB.
    For the player position i think it is much better to add position in shape like this AM/FLC.

    One more question,if i take empty DB,and export data from any update,how long will tool take time to add,let's say 50.000 players to DB?

  18. #14
    Join Date
    14-06-14
    Location
    the English Channel
    Posts
    217
    vCash
    0
    Quote Originally Posted by djole2mcloud View Post
    Yes,that is what i meant.
    Further,JL tool has this option for adding all attributes of a player to DB.
    For the player position i think it is much better to add position in shape like this AM/FLC.

    One more question,if i take empty DB,and export data from any update,how long will tool take time to add,let's say 50.000 players to DB?
    You can import 80,000 players in approx 1 minute 30 seconds.

  19. #15
    Join Date
    04-03-12
    Location
    Serbia,Belgrade,Zemun
    Posts
    937
    vCash
    900
    Quote Originally Posted by archibalduk View Post
    You can import 80,000 players in approx 1 minute 30 seconds.
    That is really fast.Wonderful job.

  20. #16
    Craig Forrest's Avatar
    Craig Forrest is offline Manager
    Official Challenges Mod
    Programmer
    Forum Enforcer
    VIP
    Holy Trinity Member
    Join Date
    02-03-12
    Location
    The Great White North
    Posts
    4,080
    vCash
    1547
    Off topic

    Quote Originally Posted by archibalduk View Post
    I plan to do so eventually but only once my work on Eastside Hockey Manager has settled down a bit (we are working on the new season's database right now).
    Do you guys have a Philly scout? I watch every game and am familiar with the entire organization

  21. #17
    Join Date
    07-07-12
    Posts
    3,143
    vCash
    590
    One more question:

    How do I set date in the import_template (14.01.16 or 14-01-16 or 14-01-2016 etc. etc.) ?

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
  •