MySQL Dump Row Exporting

MySQL Dump Row Exporting

4
NewbieNewbie
4

    Mar 30, 2007#1

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

    Code: Select all

    ....
    .......
    (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 :-)

    6,605547
    Grand MasterGrand Master
    6,605547

      Mar 30, 2007#2

      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
      Best regards from an UC/UE/UES for Windows user from Austria

      4
      NewbieNewbie
      4

        Mar 30, 2007#3

        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#4

          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 :-)

          6,605547
          Grand MasterGrand Master
          6,605547

            Mar 31, 2007#5

            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.
            Best regards from an UC/UE/UES for Windows user from Austria