Cleaning a SQL dump

Cleaning a SQL dump

1
NewbieNewbie
1

    Dec 31, 2006#1

    Hello,

    I have SQL dumps that must be "cleaned". Below is a sample of the text.

    Original text before edit:

    Code: Select all

    ', 0, -1, 0, 0);
    INSERT INTO `thread_id` VALUES(9, 'Bim Bousman', 'Hello', '2003-04-18 12:27:00', '<table border=0 cellpadding=2 cellspacing="0"><tr><td>
    <pre>Hello All,
    
    Just want said hello to everyone.
    
    Bim Bousman
    Honolulu
    ************************
    From: <<a href=forum/postID=ewrjw_-O7iw4x2sTK0kw8a1-OvSC8_P9qOb9_i80z0HJ4hjYAhmPsSusItNqv7EPSk9b0Sh-PLjtXhEVLViUO8qZnPpALg"></a>>
    To: <<a href="forum/postID=ewrjw_-O7iw4x2sTK0kw8a1-OvSC8_P9qOb9_i80z0HJ4hjYAhmPsSusItNqv7EPSk9b0Sh-PLjtXhEVLViUO8qZnPpALg"></a>>
    Sent: Thursday, April 17, 2003 9:50 PM
    Subject: Hello
    
    
    >
    >
    >
    > Your advertisement here.Click here for more information.
    >
    >
    ><br></pre>
    
    ', 0, -1, 0, 0);
    INSERT INTO `thread_id` VALUES(10, 'Ferromundo', 'Hello','2003-04-20 20:11:00', '<table border=0 cellpadding=2 cellspacing="0"><tr><td>
    <table bgColor="#ffffff"><tr><td>
    <div><font face="Arial" size="2">Welcome and enjoy your time.
    
    Thanks
    Ferromundo
    
    *********
    Avertisement
    here
    Click mor
    e details.
    *********
    ', 0, -1, 0, 0);
    INSERT INTO `thread_id` VALUES(11, 'Stanley', 'Hello','2003-04-21 16:22:00', '<table border=0 cellpadding=2 cellspacing="0"><tr><td>
    <pre>Hi Ferromundo,
    
    We miss you.When
    you can
    do war again.Call me after
    work hours.
    
    regards
    Stanley 
    =========
    Advertisement here
    Click more
    details.
    =========
    I want it like below after edit:

    Code: Select all

    ', 0, -1, 0, 0);
    INSERT INTO `thread_id` VALUES(9, 'Bim Bousman', 'Hello', '2003-04-18 12:27:00', '
    Hello All,
    
    Just want said hello to everyone.
    
    Bim Bousman
    Honolulu
    ', 0, -1, 0, 0);
    INSERT INTO `thread_id` VALUES(10, 'Ferromundo', 'Hello','2003-04-20 20:11:00', '
    Welcome and enjoy your time.
    
    Thanks
    Ferromundo
    ', 0, -1, 0, 0);
    INSERT INTO `thread_id` VALUES(11, 'Stanley', 'Hello','2003-04-21 16:22:00', '
    Hi Ferromundo,
    
    We miss you.When you can do war again.Call me after work hours.
    
    regards
    Stanley
    There are many sensitive characters in the file like ',; etc. because this is an SQL dump.
    So does anybody here know how to do that? I try find and replace one by one every character, but there are too many different word in file.

    Or if interested I am offering this job to UltraEdit developer or other members here who are experts on using UltraEdit.

    I have a lot of SQL dumps that must be "cleaned". So if this job is NOT FREE, I will pay for it.

    Regards,
    Ringo

    6,603548
    Grand MasterGrand Master
    6,603548

      Jan 02, 2007#2

      Here is a macro which does the job for your example. The macro is designed to run on a file with MS-DOS line terminations. If your SQL dump is in Unix format opened in Unix mode without temporary conversion to DOS (which is never a good setting on Windows) you must replace every ^p (means CRLF - carriage return and line-feed) with ^n (means LF only).

      First the macro trims all trailing spaces on the whole file to be able to work usefully with ^p.

      The first loop finds '<table border=0 with or without preceding space(s) and if found expands the selection to the position where after the character > a character is following which is not the character < or a line termination. Additionally the second find in first loop also selects 0 or more occurrences of spaces and tabs after last >. The selection is reduced by 1 character - the character after last > if there are no spaces/tabs following - and then deletes the selection. If now the current character is either a space or a tab, delete this character too. Because it is important and deleted before a space with a following ' and a line termination is inserted. So this loop should delete the HTML tags before the email content.

      The second loop finds 1 or more line terminations followed by at least 5 * or =. If such a string is found, it is first replaced by a single line termination, before everything to next SQL entry or end of file is also selected and then deleted. This loop should delete the advertisements in the emails.

      The third loop finds with an extremely strange but because of some bugs only working regular expression in UltraEdit style 3 successive lines where none of the 3 lines start with a '. Such a block is expanded to end of the paragraph without including the last character of the paragraph and then converted with ReturnToWrap to a single line. Don't know if this is really needed, but your example has such a modified email content line.

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

      Code: Select all

      InsertMode
      ColumnModeOff
      HexOff
      UnixReOff
      Top
      TrimTrailingSpaces
      Loop 
      Find RegExp " ++'<table border=0"
      IfNotFound
      ExitLoop
      EndIf
      StartSelect
      Find RegExp ">[~<^p][ ^t]++"
      Key LEFT ARROW
      Delete
      EndSelect
      IfCharIs 32
      Delete
      EndIf
      IfCharIs 9
      Delete
      EndIf
      " '
      "
      EndLoop
      Top
      Loop 
      Find RegExp "[^p]+^{^*^*^*^*^*^}^{=====^}"
      IfNotFound
      ExitLoop
      EndIf
      "
      "
      StartSelect
      Find "', 0, -1, 0, 0);"
      IfSel
      Key HOME
      Else
      SelectToBottom
      EndIf
      Delete
      EndLoop
      Top
      Loop 
      Find RegExp "%[~'^p][~^p]+^p[~'^p][~^p]+^p[~'^p]"
      IfNotFound
      ExitLoop
      EndIf
      StartSelect
      Find "^p^p"
      Key UP ARROW
      Key END
      Key LEFT ARROW
      ReturnToWrap
      EndSelect
      EndLoop
      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. The macro command UnixReOff sets the regular expression option to UltraEdit style.
      Best regards from an UC/UE/UES for Windows user from Austria

      4
      NewbieNewbie
      4

        Mar 23, 2007#3

        A really quick and dirty way to strip the HTML markups from the text is to display the file in a browser, then "select all" the displayed text in the browser window (Ctrl-A), and then copy/paste the selection into an open and ready UltraEdit window.

        Usually this works for me. Your mileage may vary.

        "share and enjoy"