Jump to content

Excel and the FM Editor - FM Editing


magicmastermind124
 Share

Recommended Posts

I will preface this post by stating that SI generally do not support files that have content created outside of the FM Editor, including Excel.

 

I've used Excel with FM over the past year to help with big projects when it comes to importing a massive amount of data. It can rapidly reduce the amount of time you would otherwise spend in the Editor making the changes manually, and as long as you create the file separately from your main project, and test it in game to ensure that it works, you should find that it works flawlessly.  This includes changes to the database for items like histories etc.

One example of this is when setting up Fixtures in the editor - this can take a long time and when you have a few leagues you want to do, this can become monotonous (I have probably done a 100 of these over the past few years, and I did all fixtures not included in game for last years EEE release, minus 3 L9 leagues that still announce their fixtures monthly, in other words, the EPL, L7, L8 and 11 L9 leagues - 21 in total for one season while SI had the EFL and ENL covered with 7 leagues). Using excel text formulas you can manipulate the data in released fixture files and tabulate all the necessary details, such as the date, the home team and away team, and the time for the fixture.

As an example, here is one original fixture announced for the EPL this year:

13/08/2016      15:00   Arsenal v Liverpool

This is how the fixture is formatted by the FA in their original announcement here.

The way I tabulated this data was first of all to separate the date, which could then be separated into the day of the week, the day of the month, the month and the year.  The formula I used was "=LEFT(A2,10)" to retrieve the information for the date.  I could then obtain all the other data by using formulas such as "=LOWER(TEXT(B2,"ddd"))" for the day of the week, "=DAY(B2)" for the day of the month, "=LOWER(TEXT(B2,"mmm"))" for the name of the month and "=YEAR(B2)" for the year.  Similar formulas are done for the rest.

The trickiest part here is getting the data for the teams separated so that you can find the team Unique ID.  The formula "=MID(A2,25,100)" separated the teams from the original data, at which point you can then use formulas to get the home and away team.  The " v " is the key part here, as you can use this as the dividing line to separate the data in formulas.  I retrieved the home team using "=LEFT(D2,SUM(SEARCH(" v ",D2,1))-1)" and the away team using "=RIGHT(D2,SUM(LEN(D2)-SEARCH(" v ",D2,1))-2)".  A separate sheet should be setup with the Unique ID's and the names of the clubs in question - not too hard to get from the Editor and not too time consuming.  From here, you can use a VLOOKUP function to get the ID's of the clubs. I used this formula: "=VLOOKUP(J2,[ENG.xlsx]ENG!$A:$B,2,FALSE)", just a simple VLOOKUP to another spreadsheet which has the club name in the first tab and the ID in the second.

So my end result is the following:

 

 DATE

 TIME

 MATCH

 Day 

Date

 Nmonth

 Year

 Time

 Home Team

 Away Team

 Home Team ID

 Away Team ID

13/08/2016      15:00   A.F.C. Bournemouth v Manchester United

 13/08/2016

 15:00

 A.F.C. Bournemouth v Manchester United

 sat

13

 aug

 2016

 1500

 A.F.C. Bournemouth

 Manchester United

 600

 680

13/08/2016      15:00   Arsenal v Liverpool

 13/08/2016

 15:00

 Arsenal v Liverpool

 sat

13

 aug

 2016

 1500 

Arsenal

 Liverpool

 602

 676

13/08/2016      15:00   Burnley v Swansea City

 13/08/2016 

15:00

 Burnley v Swansea City

 sat

13

 aug

 2016

 1500

Burnley

 Swansea City

 622

 724

etc.

So, with this data I can now set up the xml data for the fixtures.  Having exported a copy of the Advanced Rules file for England into an xml, I was able to ascertain which data I would need and inputted each xml line into a tab on the first row, and removed the data which would require altering.

<record>

<integer id="fixture_order" value="0"/>

<string id="day_of_week" value=""/>

<integer id="day_of_month" value=""/>

<string id="month" value=""/>

<integer id="year" value=""/>

<string id="time" value=""/>

<record id="home_team_id">

<integer id="id" value="1752003689"/>

<integer id="Ttea" value=""/>

</record>

<record id="away_team_id">

<integer id="id" value="1635218537"/>

<integer id="Ttea" value=""/>

</record>

</record>

                               

These were the first row tabs.  As an example, the first cell that would need changing is '<string id="day_of_week" value=""/>'. Using a formula you can change this to find the day of the week easily from the previous worksheet. I used "=CONCATENATE(LEFT(C1,32),fixtures!E2,RIGHT(C1,3))", and similar ones for the rest.  As for the cells that didn't need extra data, it was  a case of equalling the first cell (e.g. "=B$1").

Once this was done, you could drag the formula all the way down to get the fixtures.  I've attached the file to this so you can check out the exact worksheet I used so you can create your own.

Lastly, you can use the xml data and paste it into an FM XML file. I just found the EFL fixtures, pasted that into the EPL file and overwrote the main fixtures part with the one from the worksheet.  I've attached that file too so you can see the EPL Fixtures in the editor.

So why go through all this, it seems like a lot of work from what I've written?  If you've had to do this before, you'll know how long inputting fixtures into FM can take.  This has taken me from 09:21 when I saved the original fixture list, to 10:04 when I saved the FM XML file with the fixtures formatted. A total of 43 minutes for 380 fixtures. And once you've done this only once, it becomes quicker the second time, and you can use this as a basis to edit other things like records and histories into the game.  You could even use it to add players, although I have not done it.  You just need to add a few records into the same and save the file as an xml to check the data and how it is formatted.  Then you can identify what you need and implement a worksheet with Excel for what you need.

I'm hoping to bring out more content like this in the future, please let me know if you found this useful or interesting! And please feel free to ask questions if you think I have missed something or if you think there is another way the work can be improved.

 

EPL Fixtures Workbook.xlsx

EPL Fixtures.xml

Link to post
Share on other sites

do you have some idea for kits? google forms are good solution for all other information but kits are problem.  i want to find some easy way to get info about LL team kits and if i use google forms its complicated for people to choose from 50 options - kit design, foreground colour, bgnd colour... best way is to create some application similar to kit designer in editor but i dont know how to do this. 

Also its everything about information - transfer that information to xml is not problem at all. this can be done in 3 ways.

1.with that script above- this is easiest solution for begginers

2.with my unix scripts -this is easy for me but probably more difficult for begginers but with some tutorial this can be usefull

3. directly from excel with "xml tools" add -on  - this is also easy way with some easy tutorial

Link to post
Share on other sites

Is it possible to create XML files for anything you put into the editor?  I have a semi-working "robot" that takes in a huge amount of data then translates that into how a user would enter it into the editor, but translating it into an XML file instead would be much quicker and cleaner.

Link to post
Share on other sites

On 8/24/2016 at 18:01, Chesters86 said:

Urgh! I spent a rather long time using Notepad to create the correct fixtures and results for the Czech League and now I see there is a simpler way of doing it!!

It's okay, you can still use those results in Excel if you were to do it that way.  It's just a case of learning a few new formulas to manipulate the data.

 

With regards to the scripts, like I mentioned earlier it would be great to have a tutorial on this much like what I have done in the OP of this thread. I may take some time this weekend to teach myself and see if I can become comfortable in it to help others.

Link to post
Share on other sites

12 hours ago, krlenjushka said:

@metal_guitarist javascript

@forameuss its possible. upload your "robot" so maybe we can change it for you

It's nowhere near uploadable state.  It works, it's just tedious and prone to error if it runs "too fast".

I investigated the XML files the past few days, seems the better option.  Can easily take in an excel line representing a database object and generate an XML through Python that would be editor readable.  It's actually quite a nice format it uses.  Just a matter of getting them working together.

Link to post
Share on other sites

  • 1 year later...
  • 1 year later...

Hi all,

Sorry for the thread dredge, but does anybody know what the rules are around the <large id="db_unique_id" and <integer id="db_random_id" attributes?

I'm working on a tool to bulk import records, so I will need to generate these values on the fly. Obviously, the values need to be unique within the file, but there seem to be some other rules (incrementing an existing value by 1 didn't work).

Link to post
Share on other sites

"db_unique_id" is unique id for every recored you add to your file - club, player, city...  BTW when i add files like this - i use editor visible UID.

"db_random_id" is random UID used for every change you made in your file - this is here in case you use multiple files. i never use this line when i create files from excel and i never had any issues.

Link to post
Share on other sites

1 hour ago, DementedHammer said:

Cool. Thanks for the information. Since I posted I've written a unique ID generator, and it seems to be working well. (I imported 100 random new cities as a test and everything worked correctly.) 

if your tool can be used in linux - i will be happy to see how it works. I have script for windows and its 100% but im to lazy to load windows everytime i need something - and i cant make similar thing in linux because my skills are not that good :D

Link to post
Share on other sites

12 hours ago, krlenjushka said:

if your tool can be used in linux - i will be happy to see how it works. I have script for windows and its 100% but im to lazy to load windows everytime i need something - and i cant make similar thing in linux because my skills are not that good :D

At this stage it is Windows only, but I could potentially use Mono to convert it. 

Link to post
Share on other sites

Ok i will try.

 

EDIT: thanks to your advice - i tried to find solution for Mono and i managed to found solution for my script. It was quite easy TBH. I can run it with wine cmd command without any issues. Its not with Mono but who cares :D

Thanks anyway!

Link to post
Share on other sites

8 minutes ago, DementedHammer said:

Awesome. Glad that I could help indirectly. (You've helped me a lot so it's nice to be able to return the favour in some way.) 

Btw you can add "random uid" line if you want to be sure. I used random number generator for similar things.

Link to post
Share on other sites

  • 5 months later...
  • 11 months later...
Just now, Ivs said:

@magicmastermind124 how I can export the xml file once I finished all the process? How should I use the mapping xml requested by Excel to export in xml format?

I copied and pasted the XML straight into Notepad++.  I made sure the format would work by usually making a test change in the database to see how it would appear in the xml file.  From there, I would replace the xml with that from the excel file.  Once it's saved in the editor, it should be formatted correctly.

Link to post
Share on other sites

  • 2 months later...

How can import players from excel to editor?

I have players' info listed in columns in this order:

first name, last name, d.o.b., position.

I'd like to avoid adding players manually, so if there is a way to do it, I'd like to learn it..

Link to post
Share on other sites

12 minutes ago, g00dman said:

Funnily enough, same here... Looks like we all had the same brainwave... (The columns I'm dealing with are First name, last name, nationality, 2nd nationality, and club)

done details and international data so far. Just started the contract part.

i'll be posting it all here

Link to post
Share on other sites

7 hours ago, sporadicsmiles said:

This is all very interesting. 

I might look at putting together a series of python tools that can read lists of things you want added en masse. Seems like it would not be terribly hard to do such a thing, and I do like a good project to play with on this front. 

Sounds cool! Would the python tools be able to make a list of all the cities in the game for example? or would they be for writing bulk data once the format and information is collected?

Link to post
Share on other sites

15 minutes ago, Imadinnerjacket said:

Sounds cool! Would the python tools be able to make a list of all the cities in the game for example? or would they be for writing bulk data once the format and information is collected?

I'd initially be thinking of using them for writing bulk data from an initial user file. Perhaps a few little options that can create a randomized squad of players with randomized CA/PA to help fill out empty teams. 

The rest depends on where and how the data is located. I use python mostly for data analysis, so I am used to feeding it data in a format I can decide. I do not really know how and in what format FM stores is data and how easy that is to read. 

Link to post
Share on other sites

The game will fully flesh out a player even with no given stats so really all you need to do is lump in a bunch of blank players into a team and let the game do the rest on startup. That's actually what I want to do with this - playing with an emptied database and putting a bunch of blank players into every team to start fresh with a fully 'generated' database. Seeing as the 'use fake players' option isn't sufficient for this and simply letting the game generate players on its own will only give you players in teams whose leagues you've loaded, and even then those players are very poor. Blank players are also quite poor but you do at least get some decent ones to start with.

Link to post
Share on other sites

  • 2 months later...
On 07/12/2018 at 01:19, krlenjushka said:

"db_unique_id" is unique id for every recored you add to your file - club, player, city...  BTW when i add files like this - i use editor visible UID.

"db_random_id" is random UID used for every change you made in your file - this is here in case you use multiple files. i never use this line when i create files from excel and i never had any issues.

Sorry for bumping this, its the only ref i could find.

If i'm creating something in excel, how did i create a new db_unique_id that game will take when importing the file into the editor?  

Link to post
Share on other sites

3 hours ago, wkdsoul said:

Sorry for bumping this, its the only ref i could find.

If i'm creating something in excel, how did i create a new db_unique_id that game will take when importing the file into the editor?  

 

You can use whatever number you want or you can use real UID number from editor.  Also if you want to have "original" UIDs from xml - just add for example 100 empty clubs and copy db_unique_id number to your sheet.

I use real uid number for years without any issues.

Link to post
Share on other sites

6 minutes ago, krlenjushka said:

 

You can use whatever number you want or you can use real UID number from editor.  Also if you want to have "original" UIDs from xml - just add for example 100 empty clubs and copy db_unique_id number to your sheet.

I use real uid number for years without any issues.

ah ok, i'll keep trying.  Prob just missing a bit of code to get it to self generate, it was over typing 1 record, 30 times rather than create 30 players.   

Link to post
Share on other sites

8 minutes ago, krlenjushka said:

Its not difficult at all. If you need some help just ask.

I use this when i need to generate almost everything. I created 30000 players in just 2 hours.

damn. nice.  I have most of the UIDs i think, just not sure the best way to generate stuff.  (using mail merge at the min to ceate the code)

Link to post
Share on other sites

Script from 4th post - you just need to edit it to your needs.

 

This is content from that script:

 

@echo off

set in=in.txt
set out=output.txt

(for /f "tokens=1-12 delims=    " %%i in (
    %in%
) do (
    echo ^<record^>
    echo ^<integer id="day_of_month" value="%%i"/^>
    echo ^<string id="month" value="%%j"/^>
    echo ^<unsigned id="year" value="%%k"/^>
    echo ^<string id="time" value="%%l"/^>
    echo ^<record id="home_team_id"^>
    echo ^<integer id="Ttea" value="%%m"/^>
    echo ^</record^>
    echo ^<record id="away_team_id"^>
    echo ^<integer id="Ttea" value="%%n"/^>
    echo ^</record^>
    echo ^</record^>
)) > %out%

 

Its used to create fixtures. If you want to edit it to create new clubs, players, stadiums or whatever - you need to edit those lines.

Its really easy - script just writes all lines that you set using echo function.  Some lines are fixed - some lines have different data (variables).

Variable values are all data you have for your lets say clubs. Script will use "in.txt" file to read data and "output.txt" to write your new xml.

 

 

Link to post
Share on other sites

33 minutes ago, wkdsoul said:

I'll have a look, not sure i've any idea how those work.  thanks for your help, i'll do some homework :) 

Simple - try to add 1 empty club in editor and make just 1 change - for example clubs name.  Save your file as xml. When you open that xml you will see 20 +/- lines created by editor. Now - you will use those lines to set your script if you wanna add multiple empty clubs and set their names.

Take a look at my basic script for create club function.

 

@echo off

set in=coldo.txt                     - this is my input file - you can change name as you wish but make sure names are same. Also input file must be in same folder as script.
set out=coldo2.txt

(for /f "tokens=1-12 delims=    " %%i in (
    %in%
) do (
    echo ^<record^>                                                                                                 --- from here
    echo ^<integer id="database_table_type" value="55"/^>
    echo ^<large id="db_unique_id" value="-1"/^>
    echo ^<unsigned id="property" value="1094992978"/^>
    echo ^<record id="new_value"^>
    echo ^<integer id="database_table_type" value="3"/^>                              this is new record change - like when you add 1 empty club in editor using "add" button
    echo ^<integer id="dcty" value="0"/^>
    echo ^<large id="db_unique_id" value="%%i"/^>                                           
    echo ^</record^>
    echo ^<integer id="version" value="2773"/^>
    echo ^<boolean id="is_client_field" value="true"/^>
    echo ^</record^>                                                                                                       to here----
    echo ^<record^>                                                                                                    --- from here
    echo ^<integer id="database_table_type" value="3"/^>
    echo ^<large id="db_unique_id" value="%%j"/^>                                                
    echo ^<unsigned id="property" value="1131307373"/^>
    echo ^<string id="new_value" value="%%k"/^>                                                       
    echo ^<integer id="version" value="2773"/^>                                                       clubs name change
    echo ^<string id="odvl" value=""/^>
    echo ^<boolean id="is_client_field" value="true"/^>
    echo ^<boolean id="is_language_field" value="true"/^>
    echo ^</record^>                                                                                                              to here----
    echo ^<record^>                                                                                                                --- from here
    echo ^<integer id="database_table_type" value="3"/^>
    echo ^<large id="db_unique_id" value="%%l"/^>
    echo ^<unsigned id="property" value="1131638381"/^>
    echo ^<string id="new_value" value="%%m"/^>                                                                clubs short name change
    echo ^<integer id="version" value="2773"/^>
    echo ^<string id="odvl" value=""/^>
    echo ^<boolean id="is_client_field" value="true"/^>
    echo ^<boolean id="is_language_field" value="true"/^>
    echo ^</record^>                                                                                                               to here----
    echo ^<record^>                                                                                                                 --- from here
    echo ^<integer id="database_table_type" value="3"/^>
    echo ^<large id="db_unique_id" value="%%n"/^>
    echo ^<unsigned id="property" value="1131640942"/^>
    echo ^<string id="new_value" value="%%o"/^>                                                          clubs 3 letter change
    echo ^<integer id="version" value="2773"/^>
    echo ^<string id="odvl" value=""/^>
    echo ^<boolean id="is_client_field" value="true"/^>
    echo ^<boolean id="is_language_field" value="true"/^>
    echo ^</record^>                                                                                                             to here----
    echo ^<record^>                                                                                                                --- from here 
    echo ^<integer id="database_table_type" value="3"/^>
    echo ^<large id="db_unique_id" value="%%p"/^>
    echo ^<unsigned id="property" value="1130591353"/^>
    echo ^<record id="new_value"^>                                                                                  clubs city
    echo ^<integer id="city" value="%%q"/^>
    echo ^</record^>
    echo ^<integer id="version" value="2773"/^>
    echo ^<null id="odvl"/^>
    echo ^<boolean id="is_client_field" value="true"/^>
    echo ^</record^>                                                                                                                     to here---- 
    echo ^<record^>                                                                                                                      --- from here
    echo ^<integer id="database_table_type" value="3"/^>
    echo ^<large id="db_unique_id" value="%%r"/^>
    echo ^<unsigned id="property" value="1132029550"/^>                                                 year founded
    echo ^<integer id="new_value" value="%%s"/^>
    echo ^<integer id="version" value="2773"/^>
    echo ^<unsigned id="odvl" value=""/^>
    echo ^<boolean id="is_client_field" value="true"/^>
    echo ^</record^>                                                                                                                 to here----
   
)) > %out%

 

All %% lines are variables. Lets use my example:

2082082528    2082082528    FK Backa Mol    2082082528    Backa (Mol)    2082082528    BacMol    2082082528    62175081    2082082528    1914

 

1. first is uid of new added record(in this case club. Thats my first change.

2-3. Uid and new clubs name for change #2.

4-5. Uid and new clubs short name for change #3.

6-7. Uid and new clubs 6 letter name for change #4.

8-9. Uid and club city (uid of that city) for change #5.

10-11- Uid and year founded info for change #6.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...