Discussion:
Memo Search
(too old to reply)
Paul
2011-03-08 16:24:32 UTC
Permalink
I need to fix dates in memo fields. I have users who have used "."
instead of "/" for dates. For example 3.8.2011 or 3.8.11. I am moving
all of the memo fields to a new platform. I need to fix all of these
dates. Any suggestions on how to fix?

Thanks,
Paul
Dan Freeman
2011-03-08 16:38:31 UTC
Permalink
Post by Paul
I need to fix dates in memo fields. I have users who have used "."
instead of "/" for dates. For example 3.8.2011 or 3.8.11. I am moving
all of the memo fields to a new platform. I need to fix all of these
dates. Any suggestions on how to fix?
Thanks,
Paul
You could use Strtran() of course, but that'll mess up any dollar
amounts and sentence endings.

If there are no dollar amounts, then do the first Strtran() to replace
all "." with "/", and then do another to replace "/ " with ". ".
That'll get you close.

Otherwise you'll have to write a considerable bit of parsing code.
(RegExp may help in locating the strings to be doctored, but the
doctoring will take some code.)

Dan
Paul
2011-03-08 17:34:08 UTC
Permalink
Post by Dan Freeman
Post by Paul
I need to fix dates in memo fields. I have users who have used "."
instead of "/" for dates. For example 3.8.2011 or 3.8.11. I am moving
all of the memo fields to a new platform. I need to fix all of these
dates. Any suggestions on how to fix?
Thanks,
Paul
You could use Strtran() of course, but that'll mess up any dollar
amounts and sentence endings.
If there are no dollar amounts, then do the first Strtran() to replace
all "." with "/", and then do another to replace "/ " with ". ".
That'll get you close.
Otherwise you'll have to write a considerable bit of parsing code.
(RegExp may help in locating the strings to be doctored, but the
doctoring will take some code.)
Dan
Dan,

I wish it was as simple as replacing the ".". But that will not work.
The memo field is populated with sales notes. So parsing code would be
needed.
Dan Freeman
2011-03-08 20:40:57 UTC
Permalink
Post by Paul
Post by Dan Freeman
Post by Paul
I need to fix dates in memo fields. I have users who have used "."
instead of "/" for dates. For example 3.8.2011 or 3.8.11. I am moving
all of the memo fields to a new platform. I need to fix all of these
dates. Any suggestions on how to fix?
Thanks,
Paul
You could use Strtran() of course, but that'll mess up any dollar
amounts and sentence endings.
If there are no dollar amounts, then do the first Strtran() to replace
all "." with "/", and then do another to replace "/ " with ". ".
That'll get you close.
Otherwise you'll have to write a considerable bit of parsing code.
(RegExp may help in locating the strings to be doctored, but the
doctoring will take some code.)
Dan
Dan,
I wish it was as simple as replacing the ".". But that will not work.
The memo field is populated with sales notes. So parsing code would be
needed.
Put the memo field into an array with ALINES() and process each row.

* Start at the beginning of each line.
* One character at a time, grab 10 bytes into a variable.
* Use RegExp to see if the variable matches nn.nn.nn, if so replace
* (Note you'll also need to test for n.n.nn, nn.n.nn, and n.nn.nn)
* At the end, put the contents back.

Pretty straight forward.

Dan
Gene Wirchenko
2011-03-08 20:03:46 UTC
Permalink
Post by Paul
I need to fix dates in memo fields. I have users who have used "."
instead of "/" for dates. For example 3.8.2011 or 3.8.11. I am moving
all of the memo fields to a new platform. I need to fix all of these
dates. Any suggestions on how to fix?
In addition to Dan's reply, you should consider how to avoid bad
dates on the new platform. You may have to break out the dates into
one or more additional columns.

Sincerely,

Gene Wirchenko
Paul
2011-03-08 20:26:19 UTC
Permalink
Post by Paul
I need to fix dates in memo fields. I have users who have used "."
instead of "/" for dates. For example 3.8.2011 or 3.8.11. I am moving
all of the memo fields to a new platform. I need to fix all of these
dates. Any suggestions on how to fix?
     In addition to Dan's reply, you should consider how to avoid bad
dates on the new platform.  You may have to break out the dates into
one or more additional columns.
Sincerely,
Gene Wirchenko
Any suggestions on parsing code?
Gene Wirchenko
2011-03-09 06:06:45 UTC
Permalink
Post by Paul
Post by Paul
I need to fix dates in memo fields. I have users who have used "."
instead of "/" for dates. For example 3.8.2011 or 3.8.11. I am moving
all of the memo fields to a new platform. I need to fix all of these
dates. Any suggestions on how to fix?
     In addition to Dan's reply, you should consider how to avoid bad
dates on the new platform.  You may have to break out the dates into
one or more additional columns.
Any suggestions on parsing code?
Nothing that solves the whole problem, but just possible concerns
to consider.

1) Changing everything that looks like a date might not be safe. For
example, "3.8.11" might be a date or a reference to a section of a
document.

2) Are all of these dates in the same format? For example, what if
someone used "11.3.8" (YMD)? How do you know which format a given
date is in (as some are ambiguous)?

3) How much data is to be converted? If there are not that many
dates, then an eyeball review can be done.

Sincerely,

Gene Wirchenko
Paul
2011-03-09 14:22:26 UTC
Permalink
Post by Paul
Post by Gene Wirchenko
Post by Paul
I need to fix dates in memo fields. I have users who have used "."
instead of "/" for dates. For example 3.8.2011 or 3.8.11. I am moving
all of the memo fields to a new platform. I need to fix all of these
dates. Any suggestions on how to fix?
In addition to Dan's reply, you should consider how to avoid bad
dates on the new platform. You may have to break out the dates into
one or more additional columns.
Any suggestions on parsing code?
     Nothing that solves the whole problem, but just possible concerns
to consider.
  1) Changing everything that looks like a date might not be safe. For
example, "3.8.11" might be a date or a reference to a section of a
document.
  2) Are all of these dates in the same format?  For example, what if
someone used "11.3.8" (YMD)?  How do you know which format a given
date is in (as some are ambiguous)?
  3) How much data is to be converted?  If there are not that many
dates, then an eyeball review can be done.
Sincerely,
Gene Wirchenko
The fpt file is about 2 gig. I am going to chance the reference as a
document. Overal the majority will be dates.

Loading...