Excel (or similar) genius needed

[Petroni]

As a result of someone importing multiple copies of the same tables we have inherited some problems that need sorting with the MapMoveEvent and the Mob spawn tables.

Easy one first

The MapMoveEvent table list all the points of contact between maps so you stand on one cord and the system moves you into the next map at the appropriate cords set in the table.

As this table has had multiple copies of the same table imported there are up to 4 versions of the same identical information in the table. So when we want to change something there are multiple entries to deal with and the risk of conflicting results if you miss/cock up one. This is a serious PITA when you want to change one of them. I need someone who is competant with EXCEL or similar to take the table, and delete all multiple copies of each UNQIUE record leaving just one of each in the table and then renumber the records with a unique ID.

It can be done manually but your head impodes after 15 minutes as you lose the will to live (and your concentration). Any takers or ideas?

Harder one

The Mob Spawn table has over 15,000 lines of instructions which is why it takes so long to spawn all the mobs after a reboot. Each instruction line has unique information about map/mob/cords for spawn/amount of mobs etc so it is a little more complex trying to consolidate this information as we are not removing unique records, we want to consolidate some which are very similar them. The aim is to massively reduce the number of instruction lines to something under 5000 (less if possible) by changing the following:

Where it currently has something like

Map100 BlackMaggot 5 cords 10 50
Map100 BlackMaggot 10 cords 50 100
Map100 BlackMaggot 10 cords 100 200

I want to consolidated these three lines into

Map100 Blackmaggot 25 cords 10 200

Therefore the search would need to be where map/mobtype were the same, add the total amount of mobs set to spawn and then replace with a single line that spawned them between the lowest xy cord (10) and highest xy cord (200) (if thats doesnt make sense I'll rewrite it until it does, just say)

Anyone familiar with this table type who also has expertise in running automated routines in EXCEL?

All idea welcome.

If we can crack this it will get the game operational faster after reboot which would also allow us to experiment with faster respawns in busy places without taxing the system too much.

Cheers :-)

Penny

[Provectus]

Dall is your man for this (possible Yoof), they're both positively awesome with Excel things.

[Petroni]

Great to hear, ty Prov (where are you guyssssssss!)

There is no rush but its on the Things To Do list when time permits.

Penny

[U4ea]

It's a database - use SQL!!

For the first one, you can create an empty copy (TBL_New) of the existing table (TBL_Old) and select the distinct rows to that table using something like:

SELECT DISTINCT TBL_Old.FLD_A, TBL_Old.FLD_B INTO TBL_New FROM TBL_Old

The bit in red is the fields that should match to be considered a duplicate entry.

Then you'll need to rename the two tables or empty the first table and move the data back with another SELECT INTO statement. Make sure you keep the primary keys the same!

The second one can be done in the same way, but you may need an update query or 2 before the new table is correct.

[Petroni]

It's a database - use SQL!!

For the first one, you can create an empty copy (TBL_New) of the existing table (TBL_Old) and select the distinct rows to that table using something like:

SELECT DISTINCT TBL_Old.FLD_A, TBL_Old.FLD_B INTO TBL_New FROM TBL_Old

The bit in red is the fields that should match to be considered a duplicate entry.

Then you'll need to rename the two tables or empty the first table and move the data back with another SELECT INTO statement. Make sure you keep the primary keys the same!

The second one can be done in the same way, but you may need an update query or 2 before the new table is correct.

Thats great info tyvm but Lone is not as well versed in SQL as he would like to be and I am positively noobie!

If its simple for you or someone familiar with SQL and these tables, any chance you could do it? ;p

Penny

[Lone]

Thats great info tyvm but Lone is not as well versed in SQL as he would like to be and I am positively noobie!

If its simple for you or someone familiar with SQL and these tables, any chance you could do it? ;p

Penny

lol dont worry to much that makes more sense to me than you might think :P (enough so that i can have a play around with it and work it out lol i found out how to do Wildcard searches today as well which sped up the BG copy )


ty U4

Have instantly run into a problem though there are multiple entries for Fld_Ref 1 (like 60 rows with it a Ref1 which throws a massive spanner in the works >.< )

my thinking was i would need to use a Combo of and and OR statesment in the to try and get the individual ones.

buuuuut do you know of a statement which i could use where, if there was a duplicate exact copy it would delete the duplicate and leave just one line??


(if you want i could send you a copy of the table and you can see the kind of mess its in :P )