Welcome, Guest. Please Login
Tinderbox
  News:
IMPORTANT MESSAGE! This forum has now been replaced by a new forum at http://forum.eastgate.com and no further posting or member registration is allowed. The forum is still accessible via read-only access for reference purposes. If you wish to discuss content here, please use the new forum. N.B. - posting in the new forum requires a fresh registration in the new forum (sorry - member data can't be ported).
  HomeHelpSearchLogin  
 
Pages: 1
Send Topic Print
Converting date formatted text (Read 3269 times)
Derek Van Ittersum
Full Member
*
Offline



Posts: 168

Converting date formatted text
Aug 20th, 2014, 2:23pm
 
I've got a CSV file that lists dates using DD/MM/YYYY, but I need (I think) MM/DD/YYYY since I'm in the US and that's how my computer's configured. Is there any way to transform these dates via an agent? Right now the dates are getting imported via the CSV in the $Name, but that's easy enough to change.

Or, I suppose, easier to change in Numbers?
Back to top
 
 
  IP Logged
Mark Anderson
YaBB Administrator
*
Offline

User - not staff!

Posts: 5689
Southsea, UK
Re: Converting date formatted text
Reply #1 - Aug 20th, 2014, 2:57pm
 
So you've a pattern "^(\d{2})\/(\d{2})\/(\d{4})$". Start of string, 2 digits as back-reference #1, a literal forward slash, 2 digits as back-reference #2, a literal forward slash, 4 digits as back-reference #3, end of string.

Edit - corrected typo in next paragraph's regex.

The query finds that then $MyNewString="$2/$1/$3". Check that works than you can do date($MyNewString) is you want an actual date object. I've not tested this, but that's the general process. With this sort of task I'd definitely test in a different doc before trying it on my real data.

Back to top
 
« Last Edit: Aug 20th, 2014, 6:32pm by Mark Anderson »  

--
Mark Anderson
TB user and Wiki Gardener
aTbRef v6
(TB consulting - email me)
WWW shoantel   IP Logged
Derek Van Ittersum
Full Member
*
Offline



Posts: 168

Re: Converting date formatted text
Reply #2 - Aug 20th, 2014, 4:03pm
 
Thanks for the help Mark. I'm not sure I totally get it though. Here's what I put into the Agent:



But the agent remained empty: it didn't contain any of the notes with the dd/mm/yyyy names ...
Back to top
 
« Last Edit: Aug 20th, 2014, 4:04pm by Derek Van Ittersum »  
  IP Logged
David Bertenshaw
Full Member
*
Offline



Posts: 182

Re: Converting date formatted text
Reply #3 - Aug 20th, 2014, 4:27pm
 
Derek,

I think there may be a typo in the action code. I think it should be

$myDate = "$2/$1/$3"

The Query breaks down the date into 3 parts

$1 = DD
$2 = MM
$3 = YYYY

So all you're doing is shoving the three parts into $myDate in a different order.

As for the agent not picking up any data, I think(!) Mark's code presumes that the date is the entire string). Could you give us an example line from your import please?

Or you could try changing the Query to

$Name.contains("(\d{2})\/(\d{2})\/(\d{4})")

and see what happens (note there's no ^ or $).
Back to top
 
« Last Edit: Aug 20th, 2014, 4:31pm by David Bertenshaw »  
  IP Logged
Derek Van Ittersum
Full Member
*
Offline



Posts: 168

Re: Converting date formatted text
Reply #4 - Aug 20th, 2014, 4:42pm
 
I always forget about the .contains function. It wasn't part of TBX when I really learned it, so I've had a hard time remembering to use it.

At any rate, David's suggestion worked at returning notes. However, I still can't get the action code to return correct results. I've tried both:


$Name.contains("(\d{2})\/(\d{2})\/(\d{4})")

and

$Name.contains("^(\d{2})\/(\d{2})\/(\d{4})$")

and I've tried:

$myDate = "$2/$1/$3"

and

$myDate = "$2/$/$3"

In every case, notes end up with $myDate as $2/$/$3 instead of e.g., 07/23/2014


Back to top
 
 
  IP Logged
Mark Anderson
YaBB Administrator
*
Offline

User - not staff!

Posts: 5689
Southsea, UK
Re: Converting date formatted text
Reply #5 - Aug 20th, 2014, 6:37pm
 
David is correct - there was a typo (now fixed in my earlier post).

I've not time to test now but unless the name is the whole date you might need to match the whole string first, i.e.

$Name.contains(".*(\d{2})\/(\d{2})\/(\d{4}).*")

I can't recall if/when that's needed to get the back references to work.

It might help to post an example of the actual $Name strings you're trying to match.
Back to top
 
 

--
Mark Anderson
TB user and Wiki Gardener
aTbRef v6
(TB consulting - email me)
WWW shoantel   IP Logged
Derek Van Ittersum
Full Member
*
Offline



Posts: 168

Re: Converting date formatted text
Reply #6 - Aug 20th, 2014, 9:19pm
 
I'm still not getting any luck. Here are the strings I'm looking for:

28/07/2014
29/07/2014
30/07/2014
31/07/2014
14/08/2014
15/08/2014
18/08/2014
19/08/2014
20/08/2014

And for good measure, an anonymized csv:

http://cl.ly/1L3W1O1g3e3E/data.csv
Back to top
 
 
  IP Logged
David Bertenshaw
Full Member
*
Offline



Posts: 182

Re: Converting date formatted text
Reply #7 - Aug 21st, 2014, 3:54am
 
Derek,

What happens when you try this?

Query:

$Name.contains(".*(\d{2})\/(\d{2})\/(\d{4}).*")

Action

$MyDate=date($2 + "/" + $1 + "/" + $3)


Using your data it returns a date for me in $MyDate -- but I'm in the UK and date() gives me the UK locale, so there are some strange results (it's forcing e.g. 30 into a Day field) which I don't think (!) you'll get when you try it.
Back to top
 
« Last Edit: Aug 21st, 2014, 3:57am by David Bertenshaw »  
  IP Logged
Mark Anderson
YaBB Administrator
*
Offline

User - not staff!

Posts: 5689
Southsea, UK
Re: Converting date formatted text
Reply #8 - Aug 21st, 2014, 4:00am
 
OK, it appears the backreferences are used as discrete strings, not within a string. Theres' no formal documentation so there's an element of guesswork, e.g. using .replace() the opposite is true and the $-refs are placed inside the string.

The following agent works with your data in v6.0.2, the 'My' attributes are of the data type used in their name:

Query:   $Name.contains("(\d{2})\/(\d{2})\/(\d{4})")

Action:
$MyString=$2+"/"+$1+"/"+$3;
$MyDate=date($2+"/"+$1+"/"+$3);


The last action shows, you can go direct to a TB date in one action without pre-making a string. I just used 2 actions to show different possible uses.

Back-references are something that clearly need better documentation, if some contexts need $2+"/"+$1+"/"+$3 and others "$2/$1/$3" for essentially the same task. This sort of thing is un-guessable for the non coder.

Edit: whitespace error in body text.
Back to top
 
« Last Edit: Aug 21st, 2014, 10:55am by Mark Anderson »  

--
Mark Anderson
TB user and Wiki Gardener
aTbRef v6
(TB consulting - email me)
WWW shoantel   IP Logged
Derek Van Ittersum
Full Member
*
Offline



Posts: 168

Re: Converting date formatted text
Reply #9 - Aug 21st, 2014, 9:36am
 
Thanks David and Mark. That did it! Appreciate the persistence in helping me sort it out.
Back to top
 
 
  IP Logged
Pages: 1
Send Topic Print