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/Replace where searched text can be anything
Moderators: AmigoJack, bbadmin, helios, Bob Hansen, MudGuard
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
^\([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
>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
- talleyrand
- Posts: 624
- Joined: Mon Jul 21, 2003 6:56 pm
- Location: Kansas City, MO, USA
- Contact:
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 ).
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 ).
I choose to fight with a sack of angry cats.
- Bob Hansen
- Posts: 1516
- Joined: Sun Mar 02, 2003 8:15 pm
- Location: Salem, NH
- Contact:
This will look for most valid date values in the format mm/dd/yyyy for 2000-2009.
POSIX Search for:
^..........................................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.
POSIX Search for:
Non-POSIX Search for:^.*([1-9]|1[012])/([012]?[1-9]|[123]0|31)/(200[0-9]).*$
Replace with:^.*\([1-9]\|1[012]\)/\([012]?[1-9]\|[123]0\|31\)/\(200[0-9]\).*$
Explanation of Non-POSIX Search Regex:\1/\2/\3
^..........................................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.
Hope this was helpful.............good luck,
Bob
Bob