DB to Excel thru Decypter and back again!

Here is my part: My method to retrieve brutes out of a DB and import into Excel, B@mBam's method for data manipulation in Decripter will follow.

1-in the bc folder you wish to export from, just open the “brutal-combo.jar”
file

2-When opened select “Fichier” tab and select “Exporter”.
Pick the folder you wish to
save to and name it but with .txt in it.
ie: name.txt.(you can close BC now)

3-Open Excel

4-Now select open file, and in the window navigate to the folder where the
txt was saved. In the “File of Type” dropdown window” select
“Text”
and the file will appear you made. Select the file





5-A
import wizard will appear

6-“Delimited” should already be selected.
Click “Next”

7-Add a check to “Simicolon” click “Next”

8-click
“Finish”

Now to Sort:

1-click on the “letter” u wish to sort and that will
highlight the column to sort.(ie; F for agile)

2-At the top in the “Data” tab select Sort, In the window select “sort”
Then Sort By” click
Descending and then ok. Now let the hunt begin.

Tip: data is for level 80. For receiving a skill or weapon it is by level up to level 80. If the brute
gains a skill or weapon after lvl 80 it will not be shown.

When you find a brute to look at; Copy the name in Column “A” and overwrite
it onto another brutes name in his cell and enter.

When done save as a xls file


This was done with Excel 2003. Your’s may
difer.

alright, here's my part:

for manipulating data using sql and eumigen's decrypter, first you'll need to extract the data from your db. if you do not already have SQL, you can follow eumigen's tutorial at this link: https://mybrute.forumotion.com/cheats-scripts-f13/how-to-connect-squirrel-2-bc-db-t5207-30.htm


once you open SQL, extract the data following the instructions in eumigen's decrypter tutorial, https://mybrute.forumotion.com/cheats-scripts-f13/tutorial-eumigen-s-brutal-combo-decrypter-v-103-final-t6049.htm


whether you want to sort by server, master, level, etc, you must first follow subman's steps for delimiting (separating) the columns of data, and then sort as you want.



to put the data back together in a format usable with eumigen's tool, here are the steps:

Spoiler:

if you have any questions, feel free to ask

****bonus****


if you are only sorting pupils by levels, you do not need to entirely separate your data and put it in excel. instead, you can open the db in SQL, use the usual command, "select * from brutes", and when the table comes up, you will see "name | ID | master | level | password".

click on the "level" column once, and it will sort the pupils automatically from lowest to highest. then save the data to csv as usual, convert to .txt, and you can go directly to eumigen's tool for getting the pupils' names and adding them to a fresh or existing db, based on your preferences

A few (maybe) useful SQL requests in Squirrel. Of course you can change the column names in the SQL requests below in order to match specific needs. The column names are:

• name
  
• id
  
• master
  
• level
  
• password
  
• date
  

NB:

• If you are running SQL requests on a BC 1.2.02 or earlier database, then you should use the table brutes.
  
• If you are running SQL requests on a BC 1.3.00 or later database, then you should use the table brutes2.

Directly sort all brutes by level in ascending order:

Code:

select * from brutes2 order by level ASC

Select all brutes that are at level 0:

Code:

select * from brutes2 where level = 0

Select all brutes that are between level 1 and 5 (for example):

Code:

select * from brutes2 where level > 0 and level < 6

Find all brutes that are between level 1 and 5 and order them by date in ascending way:

Code:

select * from brutes2 where level > 0 and level < 6 order by date ASC

Find all brutes IDs that exist at least 2 times in the database:

Code:

select id,count(*) as NumberMatches from brutes2 group by id having (count(*) > 1)

Find all brutes IDs that exist only a single time in the database:

Code:

select id,count(*) as NumberMatches from brutes2 group by id having (count(*) = 1)

Count the number of distinct IDs in the database:

Code:

select count(distinct id) from brutes2

Delete all level 0 brutes:

Code:

delete from brutes2 where level = 0

Delete all brutes that are in a first list of IDs but that are not in a second list of IDs:

Code:

delete from brutes2 where (ID in (325440, 40984, 32194, ...) AND NOT ID in (0, 495, 1308, 2624, 3588, ...) )

Delete all brutes that have unique IDs and that moreover are in a given list of IDs:

Code:

delete from brutes2 where brutes2.ID in (select brutes2.id from brutes2 group by brutes2.id having (count(brutes.id) = 1) and ID in (325440, 40984, 32194, ...))

Delete brutes by name:

Code:

delete from brutes2 where name in (X'ddff44678f5c1e41ed0d122e', X'ddff4460905d074fed0d122e', X'ddff4465865e054eed0d122e', ...)

Change the date of all brutes (for example in order to do all fights again because BC won't do fights if the date isn't at least 24 hours before the current date)

Code:

update brutes2 set date = '2010-01-30'

Select all brutes whose date is prior to a given date and show the result in ascending order:

Code:

select * from brutes2 where date < '2010-02-01' order by date asc

Most important is missing... how to decode the name directly from a SQL command in order to do a domain (FR, COM, ES) cleanup directly from Squirrel?

what'd I tell ya sub ? thaks for the info, sioc!

and yes, where is the function to decode from hex into regular words? (eumi? )

bamb@n wrote:

where is the function to decode from hex into regular words?

Unfortunately after some research I don't think it is that easy to decode the domain names from the HEX names with SQL statements within Squirrel. Eumigen's Decrypter is needed.

[TOOL] MyBrute Removal 1.0.3 (edit)

I've made a small tool to remove COM/FR/ES/DE pupils from a BC 1.2.02 COM/FR/ES/DE database: http://ifile.it/5x19e7o/MyBrute-Removal.zip
(after you click on the link locate "Reqest download ticket", click it, then click on "Download")

Moreover granted that this little tool uses the brute ID instead of the brute Name you can erase all FR brutes without leaving a single FR brute and without erasing a single XP pupil.

Screenshot & procedure:

Spoiler:

NB: If you prefer an Excel version of the tool (was not updated for a while and much slower though), here it is: http://ifile.it/691qjoa/MyBrute-FRRemoval.zip
Screenshot:

Spoiler:

Manual procedure (obsolete)

If you instead want to do it manually with Squirrel here is how to remove FR pupils from a COM or/and ES database (without removing any XP pupil).

The drawback of this method compared to the tool is that it will not remove a pupil on FR if at least another pupil has the same ID (for example, if there is a pupil with ID 6345 on COM and another one with the same ID 6345 on FR, none of them will be removed). But that's a rather low percentage of garbage that will be kept in the BC database. And I prefer to keep a very little amount of garbage instead of removing any XP pupil from my databases.

Please be sure to always make a copy of your database before playing around with Squirrel.

• 1) Export all brutes with Squirrel (as usual, do not forget to remove the "Limit rows" of 100):
  

  Code:

  
  select * from brutes
  

• 2) Find names using Eumigen's Brutal Combo Decrypter ("get Names" button) in order to generate the "_IDsByDomain.txt" that give the IDs of FR pupils.
  
• 3) Export all duplicate IDs from Squirrel:
  

  Code:

  
  select id,count(*) as NumberMatches from brutes group by id  having (count(*) > 1)
  

• 4) Open that duplicate list in a spreadsheet tool (Excel, Openoffice, etc) and construct a list of IDs of the form (245, 495, 1308, 2624, 3588, 5485, 6172, 7398, ... ), i.e. one row of IDs from a column of IDs. You have to feel at least a bit comfortable with spreadsheet tools to do so.
  
• 5) Erase the FR brutes that are not duplicates in Squirrel with the following command (the first "in" refers to the list obtained at step 2 and the second "in" refers to the list obtained at step 3):
  

  Code:

  
  delete from brutes where (ID in (325440, 40984, 32194, ...) AND NOT ID in (0, 495, 1308, 2624, 3588, ...) )
  

Nota Bene:

• Steps 3 to 5 above can be put into a single step but then be warned because this SQL request takes ages to complete:
  

  Code:

  
  delete from brutes where brutes.ID in (select brutes.id from brutes group by brutes.id having (count(brutes.id) = 1) and ID in (325440, 40984, 32194, ...))
  

  where the second "in" refers to the list obtained at step 2.
  
• Of course if you have a FR database and want to remove only the FR pupils donated to BC team you'll have to use the "get Masters" button in Eumigen's tool and then to play a bit more in a spreadsheet in order to create the list of step 2) above.
  

Sioc wrote:

bamb@n wrote:

where is the function to decode from hex into regular words?

Unfortunately after some research I don't think it is that easy to decode the domain names from the HEX names with SQL statements within Squirrel. Eumigen's Decrypter is needed.

Yes, Sioc is right, the brute names and the domain are encrypted in the HEX-Code, so it is not posible to do the domain splitting by SQL in Squirrel
You need a decrypting function to get the name and the domain out of the HEX-Code!

@Sioc,
great Tut, good job

aww, just missed you, eumi! can you or anyone figure out how to save time on conversion from excel to txt for use with the decrypter? it took me time to figure out those steps I take:

- excel to csv to save with commas
- csv to txt to eliminate table information
- txt to doc for replacement of "," with ";"
- doc to txt for use with the decrypter

but surely there's a faster way to make it happen, like instead of csv with commas, shouldn't there be something like csv with semicolons, or a way to make it save with semicolons? I do see a csv with tabs, but it's the same situation as with commas...

Just enter a formula like the one in the yellow column in the picture below (you got the idea of the formula, just adapt it to your needs - do not be afraid of the RC[-x] notation which comes from "Tools" -> "Options" -> "General" -> "R1C1 Reference style" in Excel, that's just when you refer to current row and current column - x):



which yields a result like



and just copy-paste that column to notepad for use with the Decrypter.

so, in the case of a txt that already had decrypted names, and has been delimited in excel, it would be in column 10, and it would be written as:

=RC[-9] & "," & RC[-8] & "," & RC[-7] & "," & RC[-6] & "," & RC[-5] & "," & RC[-4] & "," & RC[-3] & "," & RC[-2]

UPDATE: instead of putting & "," & , you can put & ";" & . that will put semicolons in the data instead of commas, which is the intended result of trying to skip all those steps in the conversion process. so, it will actually be:

=RC[-9] & ";" & RC[-8] & ";" & RC[-7] & ";" & RC[-6] & ";" & RC[-5] & ";" & RC[-4] & ";" & RC[-3] & ";" & RC[-2]


BUT, there is one more hurdle. this only works for a row with a cell with this formula in it, so you have to copy the formula to the same column other rows. since I already learned a very, very small amount about formulas in excel thanks to my time trying to find duplicates, I know a bit about copying formulas from one cell to the next.

in order to do this for the maximum 65,536 cells possible in an excel document, here are the steps to complete this task:

Spoiler:

thanks for all your help, sioc!


psst, sioc, please post instructions in broken-down steps for people who don't know anything about this stuff. I would have been the one asking again for more info if I hadn't learned at all about formulas

Quote :

you'll have to delete all the unneeded leftover ;;;;;;; at the end of the txt before using the decrypter.

Not really... just use this formula:

Code:

=IF(TRIM(RC1)<>"";RC1 & ";" & RC2 & ";" & RC3 & ";" & RC4 & ";" & RC5 & ";" & RC6 & ";" & RC7 & ";" & RC8;"")

so you can copy the formula from first to last row of Excel and it won't show ";;;;;;" if there's no data in the corresponding row.
Note that depending of the local settings of your computer you may have to use "," separators instead of ";" in the if conditions: IF(condition;if true;if false) or IF(condition,if true,if false).

And I just changed RC[-x] (which is a relative reference) to RCx (which is an absolute reference and shortens the formula).

And you don't need to copy-paste as special the cell: just press CTRL+C on the cell (copy), select the whole column 10 (by clicking on its header number '10') then press CTRL + V (paste).

And finally that way you wouldn't even need to go to R1C1 reference style (I had just posted such an example because for coding purposes my Excel is by default in R1C1 reference style) if you use plain A1, B1, C1, etc... references (when you copy-paste the row numbers for normal reference style are adjusted automatically by Excel):

Code:

=IF(TRIM(A1)<>"";A1 & ";" & B1 & ";" & C1 & ";" & D1 & ";" & E1 & ";" & F1 & ";" & G1 & ";" & H1;"")

Column 10 would then be column "J". You can now simplify several steps of your tut...

Quote :

please post instructions in broken-down steps

Sorry, this seems to me so obvious that I didn't even realize there could be the need for many steps. Moreover I didn't feel like starting Excel tutorials here which seemed to me off topic and moreover this is extremely well documented everywhere on the web as well as in Excel built-in help... sorry...
I've therefore also added a little tool to help out at step 4 in my tutorial above on how to remove FR pupils from a COM/ES database.

ok, I just tested it, and my excel is saying there's an error when I add the =IF(TRIM(A1)<>""; to the formula.


this works fine:

=A1 & ";" & B1 & ";" & C1 & ";" & D1 & ";" & E1 & ";" & F1 & ";" & G1 & ";" & H1



this gives an error:

=IF(TRIM(A1)<>"";A1 & ";" & B1 & ";" & C1 & ";" & D1 & ";" & E1 & ";" & F1 & ";" & G1 & ";" & H1;"")

so what needs to be changed? I copied it exactly as you posted it, but it's saying I need a true/false statement for the "if" to work, and I don't know what logic statement to use for this. I will keep experimenting to see if I can figure it out until I get your response...

1) Do you have an English version of Excel?
If not then IF and TRIM functions have another name corresponding to your local language.
See this page for the translations of the Excel functions.
If you however open, say a workbook made in Swedish, with a Dutch version of Excel the functions will be automatically translated from Swedish to Dutch which ensures compatibility of Excel files.

2) What are your system settings for formula separators?
If it is not ";" you may have to replace ";" by "," in the formulas for IF(CONDITION ; TRUE ; FALSE) (just look at Excel's proposal from menu "Insert" -> "Function" to determine your separators settings).

EDIT: We may perhaps continue by PM which seems more appropriate in order to keep this thread as clean as possible.

thanks again, sioc! I don't know why it wasn't working with commas or semicolons before, but now it is working fine with the commas


for all who want to do this but can't get the formula input right, sioc uploaded a file you can use to put the formula in your excel doc: http://ifile.it/036npv9/Bam.zip

This is how I quickly change the delimiters to any format using OpenOffice.



When finished editing, Select 'Save As'
Type the name of the file you want to save it as
Check 'Edit Filter Settings', then the save button

A new window will pop up allowing you to set the Field Delimiter and Text Delimiter to whatever you want.
Done :)

This beats having to do any search and replacing.


For Eumigen's Decryptor:
Type in the .txt extension when you save so it doesn't automatically use the .csv extension.
Change the field delimiter to ;
Delete the text delimiter so that it's blank

I've replied to a long post about doing a search and replace process, but deleted it since not everyone uses Open Office. Then just thought, Excel may able to do the same. I don't use Excel and only have it installed for other programs to use.

The delimiter of Excel depnet's on the used Language. If you use german Excel will use the ";" as delimiter

But the way with Open Office is charming, I often use Open Office instead of M$ Office because it's free and I like open-source project's like Open Office, Firefox, Gimp etc.

Quote :

But the way with Open Office is charming, I often use Open Office instead of M$ Office because it's free and I like open-source project's like Open Office, Firefox, Gimp etc.

So true... I can only encourage anyone to give a try to Open Office. For most users advanced features of Excel and Word won't be necessary and Open Office will just do fine anything required... for example all these data manipulations with the Decrypter. Will save you a few bucks and/or the pleasure to play with a virus infected crack...

I'll keep that in mind next time I need an upgrade or lose this copy I have, but mostly I just use word and excel, and only for a few things at that. before mybrute came along, I never had a personal need for excel, and this is a more advanced use of excel than I ever got at school or work

Or you can use the free beta version of M$ Office.

Get M$ Office 2010 beta here:
http://www.microsoft.com/office/2010/en/default.aspx

Shouldn't need a key since it's Beta. But if you do:
http://www.microsoft.com/office/2010/en/activation.aspx

Release Candidate 0 is already out on P2P sites. Use that at your own risk

But I love OpenOffice. Since I had to reinstall it, upgraded to the latest version 3.2. Calc now loads much faster from a cold start. It feels so much crisper than the bloated and sluggish MSOffice.
http://www.openoffice.org/

Hiho!

I think I will be CRAZY!!!

I am looking for the command to delete brutes from a DB with Squirrel.

I will delete all brutes they have the password "abcdef"

But when I use

DELETE FROM brutes2 where password = abcdef

I got a error. What I doing wrong???

Please help!

This may help: https://mybrute.forumotion.com/cheats-scripts-f13/db-to-excel-thru-decypter-and-back-again-t7123.htm#94231
i.e. for strings you need the ' character:

Code:

DELETE FROM brutes2 where password = 'abcdef'