MySQL Dump Row Exporting

Alexlx · Mar 30, 2007#12007-03-30T01:55+00:00

Hi Guys,
I have one small Problem, i wanna do from this:

....
.......
(7506,5,7744,0,0),
 (7506,6,1784,0,0);
INSERT INTO `character_action` (`guid`,`button`,`action`,`type`,`misc`) VALUES 
 (7506,11,4604,128,0),
 (7507,11,4540,128,0),
 (7509,0,2457,0,0),
 (7509,3,1,64,0),
 (7509,12,7384,0,0),
.......
....
(8558,0,22,5948875,4496);
INSERT INTO `character_inventory` (`guid`,`bag`,`slot`,`item`,`item_template`) VALUES 
 (7509,0,22,5948946,5439),
 (8629,0,26,5949029,2187),
......
...

This here:

Code: Select all

INSERT INTO `character_action` (`guid`,`button`,`action`,`type`,`misc`) VALUES 
 (7509,0,2457,0,0),
 (7509,3,1,64,0),
 (7509,12,7384,0,0);
INSERT INTO `character_inventory` (`guid`,`bag`,`slot`,`item`,`item_template`) VALUES 
 (7509,0,22,5948946,5439),

I wanna export all "7509" queries in One file... this will the best thing, i think...

I did this here:

Code: Select all

HexOff
Find "(7509,"
Key END
StartSelect
Find "(7509,"
Key HOME
EndSelect
Key DEL
"
"

It will delete all things, and leave all "7509" queries...
But that didn't work right...

Because, something like:

Code: Select all

Find "(7509,"
SelectLine
CopyToClipboard (dunno) Cut?
NewFile
Paste

Something like that?

But it will be nice, if the queries before "IMPORT" ends with ";" and not ","
And that the script don't erase the imports...

Can anyone help me?

Cheers

Mofi · Mar 30, 2007#22007-03-30T11:35+00:00

Here is the macro which needs macro property Continue if a Find with Replace not found not checked.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Clipboard 9
SelectAll
StartSelect
Copy
NewFile
Paste
IfColNum 1
Else
"
"
EndIf
Top
ColumnModeOn
ColumnInsert "#"
Key LEFT ARROW
ColumnInsertNum 1 1 LeadingZero
ColumnModeOff
ClearClipboard
Loop
Find MatchCase RegExp "%*INSERT*^p"
IfFound
CopyAppend
Else
ExitLoop
EndIf
EndLoop
Top
TrimTrailingSpaces
Loop
Find RegExp "%*(7509*^p"
IfFound
CopyAppend
Else
ExitLoop
EndIf
EndLoop
SelectAll
Paste
ClearClipboard
Clipboard 0
Top
SortAsc 1 -1 0 0 0 0 0 0
Find RegExp "%[0-9]+#"
Replace All ""
Find MatchCase ",^pINSERT"
Replace All ";^pINSERT"
Bottom
Key UP ARROW
Key END
Key LEFT ARROW
Find ","
Replace ";"
Top

Alexlx · Mar 30, 2007#32007-03-30T23:36+00:00

Thanks Mofi,
Lol, your Macro is very big

I find that very nice, that Ultraedit can handle big Files (200mb)...
I didn't seen any other Editor that can handle that... some other editors Crashed, lol...

I have 3gb ram... if that helps you little bit...

And thanks very much for you Help

And for that, that you spend your time for me...

Okay, Sorry Mofi, but i think my Ram is Full...
Because a 200mb Sql File can be to Big for Ultraedit...

Shit Happens...
But the Macros in Ultraedit are really "The Funktion" that UltraEdit makes to the Best Editor...

Okay, i must look now forward...

Cheers and thx for your Help

Mar 31, 2007#42007-03-31T03:48+00:00

WoW, nice, your macro Work finally Perfect

But i have one last thing, how i can realize that?

Code: Select all

INSERT INTO `character_inventory` (`guid`,`bag`,`slot`,`item`,`item_template`) VALUES 
 (1201,0,25,539421,6948),
 (1201,0,5,540289,1369);
 (1201,0,6,540510,5398),
 (1201,0,19,540906,5571);
 (1201,0,4,546927,4907),
 (1201,0,20,547493,5571);

and get that:

Code: Select all

INSERT INTO `character_inventory` (`guid`,`bag`,`slot`,`item`,`item_template`) VALUES 
 (1201,0,25,539421,6948),
 (1201,0,5,540289,1369),
 (1201,0,6,540510,5398),
 (1201,0,19,540906,5571),
 (1201,0,4,546927,4907),
 (1201,0,20,547493,5571);

I need one Macro only for replacing the ";" to ","... and on the last line from the current Insort replace "," to ";"...

Cheers

Mofi · Mar 31, 2007#52007-03-31T19:07+00:00

My quick first approach was not very good, especially for large files. Next time please write first how many MBs your file has, if it has more than 4 MB.

Here is a better and faster solution which I now will explain because I have more time now.

The first part from command Bottom to command Top only makes sure, that the last line of the source file has a line termination. If it already has one, the source file is not changed. If it is missing, a CR/LF (for a DOS file) is inserted at bottom of the source file. This is important because the search strings later include always the DOS line termination and if the last line of the source file does not have one, it would not be recognized.

Then a simply loop comes which searches for a line which contains the case sensitive string "INSERT" or "(7509" and when found appends it to active user clipboard 9 which was cleared before. This is the same as using the find option "List lines containing string" in the find dialog and press then the clipboard button.

Hope the number of lines found are not too much for your RAM. I could rewrite the macro to paste for example after 5000 lines the clipboard content to a new file appended to lines copied before, clear the clipboard and continue in the source file until no string is found anymore in the source. But that would require a submacro because a loop inside a loop (nested loops) is not possible.

For this solution after finding all lines a new file is opened where all the lines found in correct order are pasted, clear the clipboard (free RAM) and switch back to the Windows clipboard.

After deleting all spaces and tabs at end of all lines, a very interesting part comes for you. With a very simple regular expression replace, all semicolons at end of a line are replaced by commas.

The next non regular expression replace finds all commas at end of a DOS terminated line where next line starts with "INSERT" and replaces those commas with a semicolons.

And last the comma at end of the file is replaced also by a semicolon.

Hope the function of this macro is now comprehensible for you and you can adapt it to your needs.

The macro property Continue if a Find with Replace not found must be checked for this macro.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Bottom
IfColNum 1
Else
"
"
EndIf
Top
Clipboard 9
ClearClipboard
Loop
Find MatchCase RegExp "%^{*INSERT^}^{*(7509^}*^p"
IfFound
CopyAppend
Else
ExitLoop
EndIf
EndLoop
NewFile
Paste
ClearClipboard
Clipboard 0
Top
TrimTrailingSpaces
Find RegExp ";$"
Replace All ","
Find MatchCase ",^pINSERT"
Replace All ";^pINSERT"
Bottom
Key UP ARROW
Key END
Key LEFT ARROW
Find ","
Replace ";"
Top

Add UnixReOn or PerlReOn (v12+ of UE) at the end of the macro if you do not use UltraEdit style regular expressions by default - see search configuration. Macro command UnixReOff sets the regular expression option to UltraEdit style.