[Tutorial] How to add a whole bunch of Brutes to BC-Databas

sorry, eumi, you missed a bit this time

Hades wrote:

Here is what i did:

I made the text file in name;level;password format, i connected at derby_embedded first DB, i put in squirrel the file after i decrypted and i clicked the "running man" it started fine and then that message popped up:

Error: L'instruction a été abandonnée parce qu'elle aurait entraîné la duplication d'une valeur de clé dans une contrainte de clé ou d'index unique identifié par 'SQL090613211554890' définie sur 'BRUTES'.
SQLState: 23505
ErrorCode: 30000
Error occured in:
Insert into Brutes (Name,Level,Password) Values(X'c9f6417a994c0813ed0d122e',11,'')


Should i get an older version of squirrel???

as eumi said, it is not the version of sql that is causing this, because all will detect double entries in the same manner, as far as I know. your issue is different because you made a TXT file, rather than extracting from a db.

as you see, I also highlighted a few points. I had to test a name;level;password format brute myself because you missed a step in your details above (you did not decrypt the names, you clicked on the "sql inserts" button on the decrypter tool. yes, I'm technical on these things )

since you were successful at converting, and the only issue is the duplicate brutes from your text, there are two ways to do this:

1) convert your pupils into sql statements and add them one at a time to delete the duplicates when you encounter them

2) another option (not easy for me) is to go to put all your pupil's names in an excel doc and go to this link and follow the instructions: http://office.microsoft.com/en-us/excel/ha011366161033.aspx . once you find which ones are duplicated, you can delete them from your text file and re-run the sql inserts and add them and it will not encounter any duplicates


sioc or eumi should be able to help you follow the steps needed in excel to detect those double entries (and please post them here, eumi or sioc, so I can get them too )

There are many routes to detect double entries in Excel. I don't like the one you've pointed out.
If you want to detect duplicate entries in say column 1 in Excel or Open Office, just write in the first cell of another column the following formula and copy-paste it to the rows below:

Code:

=IF(COUNTIF(A:A,A1)>1,"This is a duplicate!","")

Again depending on your regional settings you may have to replace "," by ";".

Formula:



Result:

thanks, sioc! hope that helps you out, Hades, and I know I'll be saving that one for myself as well so I can delete that link from my saved info...

edit: two additional steps to make this process easier would be:

1) once you add the list of pupils' name;level;pass in column A, THEN put the formula in column B in just the same cells as you have pupils in column A, because if you put it in too many cells first, it will slow down excel and your computer while it tries to compare all cells at once. so, if you have pupils in colum A going to row 100, copy the first cell column B where you put the formula, and then highlight rows 2-100 of column B and paste it.

2) after it calculates which cells are duplicates, click your mouse on the letter "B" of column B to highlight that column. then, where you see A-->Z and Z-->A, choose one to sort the rows, and you can copy the pupils that were not duplicated back into your text and you can finish adding them to a db

bamb@m wrote:

sorry, eumi, you missed a bit this time

Upps, now I see, it's manualy import of brutes which are not created with BC

I'm sorry for missing that :!:

In this case it's the best way to clear the TXT-file with Excel or Open Office.

@Hades,
I think you are in good hand's here, bamb@m and Sioc doing a good job

Hello to everyone!

I tried something else today.

I made a blank database, to be exact a second butal combo but completely without any pupils.
I made the connection with sql and opened a new session.
From the text file i had from the beginning (the text file in name;level;password format) i used the "sql inserts" button on the decrypter tool.
I loaded the file that makes in the squirrel-sql and clicked the "running man".

It did the work without errors .

I supposed the problem is that squirrel kept history of my previous session. :oops:

Thanks to everybody!!!

P.S. bamb@m i am looking the excel method to see if i can help a bit.
but it is quite complicated i agree! :|

the second one that sioc provided and I broke down into steps is very simple. I'm not good at making video tuts, but I can show you through TV, if you like

sioc or eumigen: I don't know if it's normal, but how long would you expect it to take for excel to calculate for duplicates in 59,000 cells using the formula above? when I helped yaboi recently, I gave him a report on his duplicates, but it took about 10 minutes for excel to finish processing each time I made one simple change

I've got duo core intel centrino CPU, 2 GB RAM, running win XP

This is because automatic calculation of the cells is enabled.

1. Open the Excel file and disable automatic calculation: Tools -> Options -> Calculation -> Manual
2. Copy-paste all values and formulas in the Excel file
3. Once everything is finished, evaluate again the formulas by pressing F9 (or save the Excel file): this recalculates everything only once (and may take some time)
4. Close and Exit the file (do not forget to enable again automatic calculation before leaving in order to avoid problems next time you use Excel for another purpose).

thanks for the tip, sioc! actually, I'm saving different copies of excel docs to save me time doing the formulas for each task every time (I have one saved to automatically recombine data with ";" separators, and one saved with the "this is a duplicate" formula for ease of use) I'll go change that now


and here's an update from sioc:


if you are trying to detect all duplicates (meaning all but the original), especially if you are trying to create a sql insert list from a text list of pupils, first add them to excel and then sort all names (in an ascending way for example), then in column B where you were putting the formula, just include a further condition to detect the first match:


=IF(COUNTIF($A:$A,A1)>1,IF(A1<>A2,"","This is a duplicate!"),"")


This will leave blank the last occurrence (instead of the first one) but it is equivalent:





and for anyone who wants to learn more about excel, this is a good forum: http://www.xtremevbtalk.com/forumdisplay.php?f=78



thanks, sioc (and forgive my rewording for relevance in the thread)!

wow really confusing

Hello everyone!

Need help with the Squirell.
After I created the txt file, I created the sql database with the decrypter, logged in to squirell db and when I click on the running man the error appears:

"Error occured in:
Insert into Brutes (Name,Level,Password) Values(X'c9ec403cd1134d1de77765522ae7a882',3,'')

What am I doing wrong...it`s not like the list of brutes I created has tens of thousands...only around 20 pupils LOL!cause I only have a few pupils ~ 50 :) so I didn`t quite understand what`s with the duplicates but from that list I did of 20 pupils, there no duplicates...every brute has its own name :)
So what am I doing wrong? Please help!

copy the rest of the lines of the error and paste here. that will give more information, and it's likely that unless you used the decrypter to delete double brutes, you have attempted to enter another brute under the same name as one already entered...


in addition, have you tried checking for duplicates using excel, as mentioned in a previous post? you say there are no duplicates, but since I'm not seeing it with my own eyes, I have to ask

EDIT:Nevermind, it worked:) I did not point the path to the db folder :) It actually worked. I just read a few posts earlier and I saw someone else was having the same problem and a member did a short tutorial about the path so Thank you!:)