Regexp to reformat dates

Regexp to reformat dates

9

    Apr 07, 2007#1

    I'm very new to the UE Regexp language so I need a bit of help.

    I have a text file with various lines that contain a date in the following format:

    D21/6/99

    The first character is always a 'D'

    I need to convert the date from:

    D21/6/99
    D15/4/06
    D1/9/01

    to:

    D06/21/99
    D04/15/06
    D09/01/01

    I'm using the UltraEdit 13.00+5 regexp engine.

    It's probably really simple, but I've been battling for hours - even with the UE Help file!
    6,681583
    Grand MasterGrand Master
    6,681583

      Apr 07, 2007#2

      First, install hotfix 7 for UE v13.00.

      Then you can use the following macro. What you want cannot be done with a single regular expression replace.

      The macro property Continue if a Find with Replace not found must be checked for this macro (default since UE v13.00).

      It would be good to know which character is following the year number to avoid wrong replaces. Is there always a space or the line termination or any other character?

      However, the first regular expression find all dates with a single digit day and inserts the leading 0.

      The second regex finds all dates with single digit month and inserts the missing leading 0.

      The third regex finds all dates with single digit year and inserts the missing leading 0. For this regex I was forced to use the negative character range specification because you have not posted which character is following the year number.

      And the last regex exchanges month and day in all the now well formatted dates.

      InsertMode
      ColumnModeOff
      HexOff
      UnixReOff
      Find MatchCase RegExp "D^([1-9]/[0-9]+/[0-9]+^)"
      Replace All "D0^1"
      Find MatchCase RegExp "^(D[0-3][0-9]/^)^([1-9]/[0-9]+^)"
      Replace All "^10^2"
      Find MatchCase RegExp "^(D[0-3][0-9]/[01][0-9]/^)^([0-9][~0-9]^)"
      Replace All "^10^2"
      Find MatchCase RegExp "D^([0-3][0-9]/^)^([01][0-9]/^)^([0-9][0-9]^)"
      Replace All "D^2^1^3"
      Best regards from an UC/UE/UES for Windows user from Austria
      9

        Apr 07, 2007#3

        Thanks! That looks like it's perfect! I will continue to test. Again THANKS!

        The character after the date is always a line termination. Here's a sample of one of the records in the source file:

        STARTS HERE with the date:

        D04/19/97
        U70.39
        T70.39
        C*
        NDEP
        PXYZ Bank
        LInterest Income:Bank Interest
        ^

        ENDS with the ^ character. The next record begins on the line immediately after the ^ character. There are no blank lines.

        The file format is a Quicken .QIF file - the UK version of Quicken. I'm trying to convert a UK QIF file into a format that will work with the US version of Quicken and the date format has to be converted from the UK format to the US format.

        How would your sample code change now that I've confirmed that the character after the date is always a line termination?

        One again THANKS!
        6,681583
        Grand MasterGrand Master
        6,681583

          Apr 07, 2007#4

          Well, now I would move the cursor always to top of the file, trim all trailing spaces, delete the negative character set definition in third regex and would use % (= start of line) and $ (= end of line) to avoid wrong replaces as much as possible.

          InsertMode
          ColumnModeOff
          HexOff
          UnixReOff
          Top
          TrimTrailingSpaces
          Find MatchCase RegExp "%D^([1-9]/[0-9]+/[0-9]+^)$"
          Replace All "D0^1"
          Find MatchCase RegExp "%^(D[0-3][0-9]/^)^([1-9]/[0-9]+^)$"
          Replace All "^10^2"
          Find MatchCase RegExp "%^(D[0-3][0-9]/[01][0-9]/^)^([0-9]^)$"
          Replace All "^10^2"
          Find MatchCase RegExp "%D^([0-3][0-9]/^)^([01][0-9]/^)^([0-9][0-9]^)$"
          Replace All "D^2^1^3"
          Best regards from an UC/UE/UES for Windows user from Austria
          9

            Apr 07, 2007#5

            EXCELLENT!

            Once again, THANKS.

              Apr 08, 2007#6

              One more question - this has really got my attention! A totally new part of UltraEdit to me that I didn't know existed!

              How would we change the regexp so that it outputs the full 4-character YEAR, ie 1998 or 2007, etc? It would only have to evaluate for the years from 1990 to today.

              So, my original before and after sample data would be:

              Before:

              D21/6/99
              D15/4/06
              D1/9/01

              After:

              D06/21/1999
              D04/15/2006
              D09/01/2001

              Thanks...
              6,681583
              Grand MasterGrand Master
              6,681583

                Apr 09, 2007#7

                Add the following 2 regular expression replaces:

                Find MatchCase RegExp "%^(D[01][0-9]/[0-3][0-9]/^)^(9[0-9]^)$"
                Replace All ^119^2"
                Find MatchCase RegExp "%^(D[01][0-9]/[0-3][0-9]/^)^([0-9][0-9]^)$"
                Replace All ^120^2"
                Best regards from an UC/UE/UES for Windows user from Austria
                9

                  Apr 09, 2007#8

                  Another perfect solution! THANKS. I really appreciate it...