Notepad++ and Regular Expressions


I had to modify a CSV file for import into one of our databases. The date/time formatting was displayed differently for two of the years of data versus the one year of data.

1/3 of the records had the date in this format: Thu 01-Sep-2011 05:29:00

2/3 of the records had the date in this format: 20/01/2007 01:45 PM

I only need the date, not time, but I had to get it into one format before importing into a table.


Before: Thu 01-Sep-2011 05:29:00

(\D\D\D) (\d\d)-(\D\D\D)-(\d\d\d\d) (\d\d):(\d\d):(\d\d)


After: Sep/01/2011

Now I have to change the Sep to 09.

Find What: (Sep)/(\d\d)/(\d\d\d\d)

Replace With: 09/\2/\3

After: 09/01/2011


Before: 09/17/07 12:00 AM

Find What: (\d\d)/(\d\d)/(\d\d) (\d\d):(\d\d) (\D\D)

Replace With: \1/\2/20\3

After: 09/17/2007


Now to change them all into DD/MM/YYYY format

Before 09/17/2007

Find What: (\d\d)/(\d\d)/(\d\d\d\d)

Replace With: \2/\1/\3

After: 17/09/2007



Add a Comment

Your email address will not be published. Required fields are marked *

(adsbygoogle = window.adsbygoogle || []).push({});