How to read 31-Mar-2006 4

[mikeopolo]

I have a csv file which includes dates in the above format. How should I read/convert it into a date field?

Regards
Mike

 

[Mike Gagnon]

Check you Regional settings for long date format. It should be set to dmmmyyyy (not available on all OS).

SET DATE TO LONG
SET CENTURY ON
? DATE()

Should give you 31-Mar-2006

Or modify the CSV file and convart to date field format to something VFP understands.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[GriffMG]

Mike G,

I *think* Mikeopolo wants to *import* the data from a text file and the set date stuff isn't going to help him much.

VFP just ignores the data and leaves empty date fields.

The only way to do it is to import to a character field then convert the date string to a 'proper' date afterwards. I suspect he'll need to write a simple function for that too.






Regards

Griff
Keep [Smile]ing

 

[baltman]

It'd be faster to re-write the text file (see faq184-4275 for an example), but you could also try this.

Brian

&& create test text file
lcString=[31-Mar-2006]
STRTOFILE(lcString, [test.txt])

&& create test table
CREATE TABLE datetest (date1 c(12))
APPEND FROM test.txt TYPE SDF

&& apply conversion
SCAN 
	REPLACE date1 WITH Date_Conv(date1)
ENDSCAN 

&& set date field(s) to date
ALTER TABLE datetest ALTER COLUMN date1 d

LOCATE
BROWSE NOWAIT 

RETURN 
PROCEDURE Date_Conv
LPARAMETERS lcDate
	nDay = VAL(GETWORDNUM(lcDate, 1, [-]))
	uMonth = UPPER(GETWORDNUM(lcDate, 2, [-]))
	nYear = VAL(GETWORDNUM(lcDate, 3, [-]))

	DO CASE 
		CASE uMonth = [JAN]
			uMonth = 1
		CASE uMonth = [FEB]
			uMonth = 2
		CASE uMonth = [MAR]
			uMonth = 3
		CASE uMonth = [APR]
			uMonth = 4
		CASE uMonth = [MAY]
			uMonth = 5
		CASE uMonth = [JAU]
			uMonth = 6
		CASE uMonth = [JUL]
			uMonth = 7
		CASE uMonth = [AUG]
			uMonth = 8
		CASE uMonth = [SEP]
			uMonth = 9
		CASE uMonth = [OCT]
			uMonth = 10
		CASE uMonth = [NOV]
			uMonth = 11
		CASE uMonth = [DEC]
			uMonth = 12
		OTHERWISE
			MESSAGEBOX([Problem])
	ENDCASE

	RETURN DTOC(DATE(nYear, uMonth, nDay))
ENDPROC

 

[GriffMG]

That is exactly what I meant Baltman!

Regards

Griff
Keep [Smile]ing

 

[baltman]

Thank you. It would be nice if VFP had a few more internal date functions.

Since most of my data comes either in Excel or csv format I usually allow Excel to do my date conversions, import the numerical value and convert it with {12/30/1899}+value.

Brian

 

[mikeopolo]

Thanks to all of you for your prompt postings. The file I'm importing is too big for Excel 2003. I was hoping not to have to create a function, but you have confirmed there's no simple way to handle this particular format.

Many thanks to baltman for the conversion procedure. Also to the reference to the csv clean-up procedure, which I will try out first.

Regards
Mike

 

[mikeopolo]

fyi, ended up using the date function. It stumbled on any date in JUN, and I realised there was a typo above (JAU)....

Regards
Mike

 

[baltman]

Sorry

 

[Nifrabar]

Baltman,

Great to use this (until now) unknown GETWORDNUM function!
There are so many in VFP....<G> to much to remember all.

-Bart

 

[baltman]

Just for fun, let's try to optimize performance a bit more.

CLOSE ALL

CREATE TABLE test (date1 c(12),date2 c(12))
FOR xx = 1 TO 30000
  INSERT INTO test VALUES ;
    (TRANSFORM(MAX(1,ROUND(28*RAND(),0)))+"-"+;
    UPPER(LEFT(CMONTH(CTOD(TRANSFORM(MAX(1,MOD(xx,12)))+"/1/1900")),3))+;
    "-"+TRANSFORM(YEAR(DATE())-(ROUND(RAND()*5,0))),"")
ENDFOR

\
\without array
FOR x = 1 TO 5
  yy = SECONDS()
    REPLACE ALL date2 WITH DTOC(Date_Conv1(date1))
  ?SECONDS()-yy
ENDFOR

\
\with array
FOR x = 1 TO 5
  yy = SECONDS()
    LOCAL ARRAY aa[12,2]
    Date_Conv_Array(@aa) &&only create array once
    REPLACE ALL date2 WITH Date_Conv_With_Array(@aa, date1)
  ?SECONDS()-yy
ENDFOR

BROWSE  NOWAIT 
RETURN

PROCEDURE Date_Conv_Array
LPARAMETERS m.aa
LOCAL i
  FOR m.i = 1 TO 12
    m.aa[m.i,1]=UPPER(LEFT(CMONTH(CTOD(TRANSFORM(m.i)+"/1/1900")),3))
    m.aa[m.i,2]=TRANSFORM(m.i)
  ENDFOR 
  RETURN m.aa
ENDPROC 

PROCEDURE Date_Conv_With_Array
LPARAMETERS m.aa, m.cDate
  RETURN m.aa[1+ASCAN(m.aa,UPPER(GETWORDNUM(m.cDate, 2, "-")),1,12,1,2)]+"/"+;
    GETWORDNUM(date1, 1, "-")+"/"+GETWORDNUM(m.cDate, 3, "-")
ENDPROC 

PROCEDURE Date_Conv1
LPARAMETERS m.tcDate
LOCAL lnDay, lcMonth, lnYear, lnMonth
  lnDay = VAL(GETWORDNUM(m.tcDate, 1, [-]))
  lcMonth = UPPER(GETWORDNUM(m.tcDate, 2, [-]))
  lnYear = VAL(GETWORDNUM(m.tcDate, 3, [-]))
  lnMonth = INT(AT(m.lcMonth,"~JAN~FEB~MAR~APR~MAY~JUN~JUL~AUG~SEP~OCT~NOV~DEC~")/4)+1
  RETURN DATE(m.lnYear, m.lnMonth, m.lnDay)
ENDPROC

 

[baltman]

Pls note a small 'bug' in Date_Conv_With_Array that would show up if not run on test table due to direct field reference.

Brian

PROCEDURE Date_Conv_With_Array
LPARAMETERS m.aa, m.cDate
  RETURN m.aa[1+ASCAN(m.aa,UPPER(GETWORDNUM(m.cDate, 2, "-")),1,12,1,2+4)]+"/"+;
    GETWORDNUM(m.cDate, 1, "-")+"/"+GETWORDNUM(m.cDate, 3, "-")
ENDPROC

 

[baltman]

And here's an example of how to apply the function to a text file. It takes longer, but remember you wouldn't need to ALTER TABLE xxx ALTER COLUMN.

I also added a character field to the front of the table to better demonstrate that we can pick and choose columns for modification.

I often use this approach to clean-up incoming text files with empty time portion of datetime and extra leading or trailing (post decimal) zeros.

Also a great way to split delimited files with more than 255 columns into two+ files vertically (while putting the primary key into both).

This is slower than my example above, but you have a lot more control over the incoming data.

Brian

CLOSE ALL

CREATE TABLE test (textdata c(20), date1 c(12),date2 c(12))
FOR xx = 1 TO 30000
  INSERT INTO test VALUES ;
    (REPLICATE(CHR(65+MOD(m.xx,65)),18),;
    TRANSFORM(MAX(1,ROUND(28*RAND(),0)))+"-"+;
    UPPER(LEFT(CMONTH(CTOD(TRANSFORM(MAX(1,MOD(xx,12)))+"/1/1900")),3))+;
    "-"+TRANSFORM(YEAR(DATE())-(ROUND(RAND()*5,0))),"")
ENDFOR

COPY TO test.txt TYPE DELIMITED

FOR xx = 1 TO 5
yy=SECONDS()
	CREATE CURSOR curResult (textdata c(20), date1 d,date2 c(12))

	LOCAL ARRAY aa[12,2]
	LOCAL cString, f1, f2, cDelim, nInPutFile, nOutFile 
	nInPutFile = FOPEN('test.txt')
	nOutFile = FCREATE('test_out.txt')
	Date_Conv_Array(@aa) &&only create array once

	DO WHILE !FEOF(nInPutFile)
		m.cString = CHRTRAN(FGETS(m.nInPutFile),CHR(34),"")
		m.f1 = GETWORDNUM(m.cString,1,",")
		m.f2 = DTOS(CTOD(Date_Conv_With_Array(@aa,GETWORDNUM(cString,2,","))))
		m.f3 = GETWORDNUM(m.cString,3,",")
		FPUTS(m.nOutFile,m.f1+","+m.f2+","+m.f3)
	ENDDO 

	FCLOSE(m.nInPutFile)
	FCLOSE(m.nOutFile)

?SECONDS()-yy
ENDFOR 

SELECT curResult 
APPEND FROM test_out.txt TYPE DELIMITED 
GO TOP 
BROWSE NOWAIT 

GO TOP IN test
SELECT test 
BROWSE NOWAIT 
MODIFY FILE test.txt nowait
MODIFY FILE test_out.txt nowait

 

[baltman]

You're right, I don't need two columns in the array. And you're also correct that it isn't encapsulated, but it is ~30% faster, not that miliseconds usually matter much.

I was just thinking about how Fox already knows the CMonth and that there must be a way to leverage that to make it faster and not need to hard code.

If you put the array creation into a single procedure it is encapulated, but much slower. It would work great for one call, but not thousands.

I am coming from the perspective of a technical user, not a programmer. If I am getting many gigs of text files every day that needs this function. Do I want encapsulation or 30%+ speed boost?

The great thing about Fox it that you can do things different ways. There is no one absolute best programming method that fits every situation.

The reason I advocate creating a modified text file (which of course is custom work a far distance away from encapsulation) is that, based on your needs, you can reduce the amount of data you need to load into Fox thus saving tons of time and disk.

With a delimited file you can do (some of) this just by making the columns less wide, but with a fixed width the file is (usually) forcing you to make an ineffecient table structure to accomodate it.

So it is up to the programmer if they want to write an encapsulated procedure that'll be reusable or if they want to save the user/themselves time when running a specific process. You can't always have both.

Brian

 

[SGLong]

I've been following this post for a while because I've been using a function similar to baltman's July 23rd post. Today one of our vendors arbitrarily changed the format of the date field that they were providing to us in a CSV file. They changed it from "AUG 28 2006 14:42:07" to "08/27/2006" which caused my CASE statement to fail. I added a new case statement at the top - "CASE ISDIGIT(..." which will do a CTOD() on the parameter. Now if they change back we should be OK.

Steve

p.s. - I gave the vendor a verbal slap upside the head for making an unannounced change.

 

[baltman]

I get "Class definition SnippetParameters is not found" as an error.

 

[baltman]

Found it. It is in the article but not the "Download the code snippets shown in this article" link.

Your performance boost comes from removing the code from the procedure call overhead. If you use VFP's built in ascan VS your hand built array and remove the procedure call overhead the "Date_Conv_with_Array" retains the speed crown.

CLEAR
CLOSE ALL

CREATE TABLE test (date1 c(12),date2 c(12))
FOR xx = 1 TO 30000
  INSERT INTO test VALUES ;
    (TRANSFORM(MAX(1,ROUND(28*RAND(),0)))+"-"+;
    UPPER(LEFT(CMONTH(CTOD(TRANSFORM(MAX(1,MOD(xx,12)))+"/1/1900")),3))+;
    "-"+TRANSFORM(YEAR(DATE())-(ROUND(RAND()*5,0))),"")
ENDFOR

*\
*\with array
  yy = SECONDS()
    LOCAL ARRAY aa[12,2]
    Date_Conv_Array(@aa) &&only create array once
FOR x = 1 TO 5
    REPLACE ALL date2 WITH m.aa[1+ASCAN(m.aa,UPPER(GETWORDNUM(date1, 2, "-")),1,12,1,2)]+"/"+;
   	 GETWORDNUM(date1, 1, "-")+"/"+GETWORDNUM(date1, 3, "-")

ENDFOR
  ?"Date_Conv_with_Array:",SECONDS()-yy
  
PROCEDURE Date_Conv_Array
LPARAMETERS m.aa
LOCAL i
  FOR m.i = 1 TO 12
    m.aa[m.i,1]=UPPER(LEFT(CMONTH(CTOD(TRANSFORM(m.i)+"/1/1900")),3))
    m.aa[m.i,2]=TRANSFORM(m.i)
  ENDFOR 
  RETURN m.aa
ENDPROC

 

[baltman]

But if we move the code into a procedure (or Snippet-like program) it becomes reusable. I didn't think I needed to demonstrate that to you as that appears to be what Snippets is all about.

You can build the array solution into a 'cousin' of Snippets that builds a generic script which could execute a REPLACE, REPLACE ALL and/or RETURN a value based on an additional parameter.

That way you'd have reuasability as well as the speed boost of not needing a procedure call for each replace of a replace all.

I store Business Rules for my apps in DBFs and have flags to EXECSCRIPT them under particular procedure calls via SCAN FORs and SEEKS(). Some are stored in Char fields and some are stored in memo fields (when code blocks are needed). All are designed to run on DBFs if the dependancy fields are present. This is very much in the sprit of Snippets and I do agree the concept is a good idea.

For complicated rule tables I have a simple rule (Snippet) editor form that shows the memo fields in editboxes for easy editing.

It is not all about speed, but that doesn't mean we should not keep speed in mind.

The reason I use Fox is that is is the fastest environment for what I need to do based on my mix of development and run time.

BTW, changing the GETWORDNUM(date1, 3, "-") to
RIGHT(ALLTR(date1), 4) and dropping the (not needed in this example) "UPPER" in the ascan gets me another good % boost which I think is going to be the fastest possible implementation of this task we're going to be able to find.

This has been an interesting thread for me.

Thanks,
Brian

 

[mikeopolo]

Couldn't agree more, although it might take me a few years to be able to implement all these great ideas!!

Regards
Mike

 

[hgirish]

I use .net InterOp to convert Date string to Date. This works on almost all variations of date format.


My .net method
public DateTime Date_Conv(string date)
{
DateTime d;
d = DateTime.Parse(date);
return d.ToUniversalTime();

}