Community

Hi,

I've been trying to find a wildcard expression that will basically find a range of text and replace it.

Here's what I want to do:

Find
1/9/2004 5:11:00 PM

where the time stamp can literarlly be anything,

and replace it with just
1/9/2004

So, in other words, I want to remove the time stamp from a large number of lines in a file and just leave the date, whatever that might be. Finding the /2004 is easy enough, as that's generally a constant. But how can I indicate to 'mark' the time part so I can have it removed?

Thanks for any help.

David

Search for regexp
^\([0-9]+/[0-9]+/2004\).*$
replace with
\1

Assumes non-Posix, no other characters on the line with the date, the date is at the left hand edge of the line and the year is always 2004.

If these assumptions are wrong either try and figure it out or re-post with the correct assumptions
Good luck

Ed

Hi Ed,

>Assumes non-Posix, no other characters on the line with the date, the >date is at the left hand edge of the line and the year is always 2004.
>If these assumptions are wrong re-post with the correct assumptions

Actually, the date isn't always on the left. It's just one field in a line (a database import file) that contains a large amount of other field data. So, no, the date isn't on a line by itself.

In terms of the field names, and data, it would like something like this:

OrderId Email FirstName LastName Company Address1 Address2 City State Zip Country Homephone Workphone Fax OrderDate ProductsOrdered SKU Total User1

"Order Date" being the field I'm trying to convert.

Thanks for any suggestions.

David Nevue

Not to be dense but couldn't you simply import the data into a dbms and do an update on the column and then re-export?
Otherwise, you'd might want to do a regexp based on column (assuming fixed width) which I believe the format has been covered in other threads (hint search for either bob_hansen or s_reynisson ).

regexp
^\(.*[0-9]+/[0-9]+/200[0-9] \)[^ ]+ [AP]M \(.*\)$
replace with
\1\2

nearly does it but you say

where the time stamp can literarlly be anything

What indicates the end of the time field?

This will look for most valid date values in the format mm/dd/yyyy for 2000-2009.

POSIX Search for:

^.*([1-9]|1[012])/([012]?[1-9]|[123]0|31)/(200[0-9]).*$

Non-POSIX Search for:

^.*\([1-9]\|1[012]\)/\([012]?[1-9]\|[123]0\|31\)/\(200[0-9]\).*$

Replace with:

\1/\2/\3

Explanation of Non-POSIX Search Regex:
^..........................................From beginning of the line
.*.........................................Any group of characters
\(...........................................Beginning of tagged expression 1
[1-9]\|1[012]..........................Numbers 1-12 (1-9, or 10,11,12)
\)...........................................End of tagged expression 1
/.........................................../ (Date separator)
\(...........................................Beginning of tagged expression 2
[012]?[1-9]\|[123]0\|31........Numbers 1-31 (1-9,11-19,21-29, or 10,20,30, or 31)
\)...........................................End of tagged expression 2
/.........................................../ (Date separator)
\(...........................................Beginning of tagged expression 3
200[0-9]................................2000-2009
\)...........................................End of tagged expression 3
.*..........................................Any group of characters
$............................................End of the line

Explanation of POSIX Search Regex:
^..........................................From beginning of the line
.*.........................................Any group of characters
(...........................................Beginning of tagged expression 1
[1-9]|1[012]..........................Numbers 1-12 (1-9, or 10,11,12)
)...........................................End of tagged expression 1
/.........................................../ (Date separator)
(...........................................Beginning of tagged expression 2
[012]?[1-9]|[123]0|31........Numbers 1-31 (1-9,11-19,21-29, or 10,20,30, or 31)
)...........................................End of tagged expression 2
/.........................................../ (Date separator)
(...........................................Beginning of tagged expression 3
200[0-9]................................2000-2009
)...........................................End of tagged expression 3
.*..........................................Any group of characters
$............................................End of the line

Explanation of Replace Regex:
\1...............Tagged expression 1
/................./ (Date separator)
\2...............Tagged expression 2
/................./ (Date separator)
\3...............Tagged expression 3

NOTES:
1. Per your sample, it does not handle months/days padded with leading 0 like 02/07/2006.
2. It does not skip dates like 4/31 which only has 30 days.
3. This assumes no other field will have data in the format of mm/dd/yyyy, and will ignore any other formats for time.
4. If doing cut and paste from this forum, be sure to delete trailing spaces at end of lines.

> nearly does it but you say where the time stamp can literarlly be
> anything

Thank you, I'll try it.

> What indicates the end of the time field?

The 'M'. It will either be AM or PM.

David

Bob,

Thanks very much for posting all those commands. That helps a great dea.

David