Notepad++ and Regular Expressions

Advertisements

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)

\3/\2/\4

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

[ad#ad-post]

Advertisements

Add a Comment

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

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