Taking the first 10 chars from a string and converting 3

[keepingbusy]

Hi

I have a field in a table called COLUMN4 that is a character type, length 20 which contains a date, time and place of origin which has been imported from a csv file. An exmaple of what the data looks like is:

08/02/2008 22:09:34 Europe/London

I am trying to extract part of that field (just the date or first 10 characters) and convert it into a date and replace another field which is a date field (INDATE) within the same table.

The following has been tried:

DO WHILE NOT EOF()
  STORE CTOD("  /  /    ") TO mdate
  STORE LEFT(COLUMN4,10) TO mdate
  REPLACE INDATE WITH mdate

and

DO WHILE NOT EOF()
  STORE CTOD("  /  /    ") TO mdate
  REPLACE INDATE WITH mdate

both of which cause data type mismatch errors.

I would be grateful for any suggestions or guidance please.

Many thanks
Lee

 

[CajunCenturion]

Have you tried:

STORE CTOD("  /  /    ") TO mdate
STORE CTOD(LEFT(COLUMN4,10)) TO mdate

--------------
Good Luck
To get the most from your Tek-Tips experience, please read
FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[GriffMG]

Hi Lee,

I would have thought that your second example would work, you do have a variable of date type (albeit empty) in mdate at the time you do the replace.

I'd probably have done something like this:

replace all indate with ctod(left(column4,10))

Regards

Griff
Keep [Smile]ing

 

[mmerlinn]

[ ]
Try:

[blue]
DO WHILE NOT EOF()
REPLACE INDATE WITH CTOD(LEFT(COLUMN4, 10))

[/blue]

mmerlinn

http://mmerlinn.com

"Political correctness is the BADGE of a COWARD!"

 

[mmerlinn]

[ ]

Dang! FOUR SECONDS too late.

mmerlinn

http://mmerlinn.com

"Political correctness is the BADGE of a COWARD!"

 

[keepingbusy]

Wow! Fantastic and quick response guys.

CajunCenturion
Tried your suggestion and that worked

GriffMG

I perhaps I didn't make it clear that each record in the imported table may have different dates so I couldn't use the replace all etc.. (But I have no doubt from your previous help it would work)

mmerlinn

Yes my friend, you were beaten by just a few seconds!

My sincerest thanks to all and whilst this was only a small piece of code that resolved the issue it made a huge difference in sorting the problem out.

Worthy of star if nothing else, for the quick response.

Cheers guys

Lee

 

[TamarGranor]

Lee - it sounds like you don't quite understand REPLACE ALL. It doesn't necessary put the same value into each record. In the example Griff gave you, he's figuring out for each record what to store based on another field of the record, so REPLACE ALL will do exactly what you need.

Anytime you have a loop through a table and the only thing inside it is REPLACE, you should assume that you can use REPLACE ALL and figure out how.

Oh, and one other note. Unless there are additional conditions to check, SCAN is a better choice for looping through a table than DO WHILE NOT EOF().


Tamar

 

[keepingbusy]

Hi Tamar

Very good points and advice - Thank you

Yes, my misunderstanding of replace all and I'm sure this will save a lot of time. (Sorry Griff)

I read about the scan versus Do while but in this scenario there are only usually a couple of hundred records to process each time a csv file is imported so speed isn't an issue.

Thanks again for the advice.

Lee

 

[Olaf Doschke]

Yes, get used to write SCAN..ENDSCAN, it's simply shorter to implement, if you do SELECT cAlias right before the scan there is no reason the scan loop would loop another cursor.

And instead of REPLACE or REPLACE ALL you could also use SQL

SET DATE AMERICAN && or whatever format the string dates are
UPDATE yourtable SET Indate = CTOD(LEFT(yourtable.Column4,10))

When there are missing dates or wrong dates you might need to precheck if CTOD really returns a date value. The good thing about CTOD is, it doesn't even error on totally wrong inputs like CTOD("blah") gives you an empty date.

you could do a selective update like this:

SET DATE AMERICAN && or whatever format the string dates are
UPDATE yourtable SET Indate = CTOD(LEFT(yourtable.Column4,10)) WHERE DTOC(CTOD(LEFT(yourtable.Column4,10)))==LEFT(yourtable.Column4,10)

Which would only set those records that convert to date and would reconvert to the string you converted.

SELECT Column4 from yourtable WHERE NOT DTOC(....)==LEFT...
would then show you the records where conversion fails.

Bye, Olaf.

 

[GriffMG]

Funny, I never use scan... endscan

I've never seen an advantage over do while ... enddo

But I'm an old git, I don't do that OOPs stuff, and rarely use SQL (unless I'm working in asp)...

B-)

Regards

Griff
Keep [Smile]ing

 

[Mike Lewis]

Funny, I never use scan... endscan
... I've never seen an advantage over do while ... enddo

One advantage is that it's more concise -- slightly.

Instead of this:

DO WHILE NOT EOF()
  <do something>
  SKIP
ENDDO

you can do this:

SCAN
  <do something>
ENDSCAN

Not a big deal admittedly, and probably not worth changing the habit of a lifetime. But worth considering ...

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

there is one thing: scan endscan automatically selects the alias you scan.

To use a while loop and be sure it loops the right alias you would rather need:

GO TOP IN alias
DO WHILE !EOF("alias")
   SELECT alias
   <do something>
   SKIP IN alias
ENDDO

compared to

Select alias
SCAN
   <do something>
ENSCAN

Bye, Olaf.

 

[ChrisRChamberlain]

It also selects the correct alias within a SCAN...ENDSCAN loop

SELE THISALIAS
SCAN
[tab]* Do something
[tab]SELE ANOTHERALIAS
[tab]* Do something else
[tab]* No need for SELE THISALIAS
ENDSCAN

FAQ184-2483​

Chris
PDFcommander.com
motrac.co.uk

 

[GriffMG]

I think we've discussed this before, as I said I'm cromagnon man - and I'm happy with the do while loop!

Concise is nice though.


Regards

Griff
Keep [Smile]ing

 

[Mike Lewis]

Griff,

I'm cromagnon man

Would that be cro-magnon, as in the earliest known European examples of homo sapiens, or Cromagnon, the psychedelic rock/avant garde collective?

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[GriffMG]

Former I'm afraid - I wanted to type neathandral but I wouldn't get my keys round it!

Regards

Griff
Keep [Smile]ing

 

[TamarGranor]

In addition to the advantages already listed:

- In most cases, SCAN is about 25-30% faster than DO WHILE().
- SCAN is more maintainable. When I'm reading code and see SCAN, I immediately know that I'm about to loop through a table or cursor. With DO WHILE, I have to go through one more processing step mentally to see that it's DO WHILE NOT EOF() (as opposed to DO WHILE NOT m.lFound, my most common use of DO WHILE) and thus a table.

I'm actually in the middle of writing an article about using the right loop construct for the situation. Did you know that using FOR rather than DO WHILE for a counted loop is an order of magnitude faster?

Tamar

 

[Bertiethedog]

A couple of times I have been called in to speedup other peoples code. The last time a few years ago I achieved 3250%

Just using scan, scan while & scan for, if the for statement is used Rushmore is invoked but you need a simple compound index, no fancy functions at all otherwise it will be ignored.

For a very large table I have used if seek then scan while, you have to be carefull not to change the index however as it will drop out.

There is quite a lot of theory about the appropriate code to use.

 

[Mike Lewis]

Tamar,

Did you know that using FOR rather than DO WHILE for a counted loop is an order of magnitude faster?

No.

Sounds like a good article. I'll look forward to reading it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk