Text to Date

ilovejedd · 02-09-2018, 11:15 AM

Help!

I need to extract dates from a comments-type column to a date-type column via bulk search and replace.

For example:

Code:

Published: 2015-10-23 Completed: 2015-10-28 Chapters: 4/4 Words: 4726

Published: 2015-04-17 Updated: 2015-07-27 Chapters: 41/? Words: 81868

I have the following search regex:

Code:

.*(Completed|Updated): (?P<year>\d\d\d\d)-(?P<month>\d\d)-(?P<day>\d\d).*

What format should I use for replace?

Thanks!

Nicolas F · 02-09-2018, 11:47 AM

I don't know how to use named groups, but I tested your regex and simply replacing with :

Code:

\4 \3 \2

works for me! (assuming you use dates in the format day-month-year)

I'd be interested to know how to use the names you used in your parenthesis.

ilovejedd · 02-09-2018, 01:34 PM

I don't think named groups work with bulk metadata s&r. I just do it that way because I can get cross-eyed when editing regex.

Tried \3 \4 \2 (#custom to last_modified) and it didn't work. Note, I have all my dates set to MMM dd yyyy in Tweaks.

davidfor · 02-09-2018, 08:42 PM

Quote:

Originally Posted by ilovejedd

I don't think named groups work with bulk metadata s&r. I just do it that way because I can get cross-eyed when editing regex.

The search and replace uses the Python regex. Named groups do work in the replace string.

Quote:

Tried \3 \4 \2 (#custom to last_modified) and it didn't work. Note, I have all my dates set to MMM dd yyyy in Tweaks.

What does "didn't work" mean? Nothing happened, the date wasn't what you expected, or there was an error? If there was an error, then what was it? Some detail in what went wrong and what you tried makes it a lot easier to suggest a fix.

For me it is working. For the replacement string, I would recommend:

Code:

\2-\3-\4

or:

Code:

\g<year>-\g<month>-\g<day>

That is the least ambiguous format for a date. And as that is the date you are using in the comment, the following is what I would have used:

Code:

.*(Completed|Updated): (.+?) .*

Or maybe:

Code:

*(Completed|Updated): ([\d-]+).*

with the replace as simply "\2".

ilovejedd · 02-09-2018, 09:48 PM

The last_modified date changed but to Feb 09, 2018 (today), not to the one specified in the replacement regex (and I did try several variations).

After several failed trials, I eventually created a custom column #modified with format yyyy-MM-dd and it worked there. Mayhaps last_modified is a special column or something that can't simply be edited by the user?

Curious, though, will a datetime column accept yyyy-mm-dd regardless of the display format (e.g. MMM d, yyyy)? Thanks!

kovidgoyal · 02-09-2018, 09:57 PM

It's pointless trying to change last_modified. Whatever change you make will get overridden immediately, since doing a search and replace modifies data, which means last_modified gets updated.

ilovejedd · 02-09-2018, 10:47 PM

Was wondering if that might be the case after my 5th regex. At first, I just thought it didn't like the replacement format. Then I tried replacing with template {date} and when it still changed back to Feb 9, that was when I exited calibre and created a custom column.

02-09-2018, 11:15 AM	#1
ilovejedd hopeless n00b Posts: 5,111 Karma: 19597086 Join Date: Jan 2009 Location: in the middle of nowhere Device: PW4, PW3, Libra H2O, iPad 10.5, iPad 11, iPad 12.9	Text to Date Help! I need to extract dates from a comments-type column to a date-type column via bulk search and replace. For example: Code: Published: 2015-10-23 Completed: 2015-10-28 Chapters: 4/4 Words: 4726 Published: 2015-04-17 Updated: 2015-07-27 Chapters: 41/? Words: 81868 I have the following search regex: Code: .(Completed\|Updated): (?P<year>\d\d\d\d)-(?P<month>\d\d)-(?P<day>\d\d). What format should I use for replace? Thanks!

02-09-2018, 11:47 AM	#2
Nicolas F Groupie Posts: 161 Karma: 1842 Join Date: Jan 2016 Device: Kobo Glo HD	I don't know how to use named groups, but I tested your regex and simply replacing with : Code: \4 \3 \2 works for me! (assuming you use dates in the format day-month-year) I'd be interested to know how to use the names you used in your parenthesis.

02-09-2018, 09:48 PM	#5
ilovejedd hopeless n00b Posts: 5,111 Karma: 19597086 Join Date: Jan 2009 Location: in the middle of nowhere Device: PW4, PW3, Libra H2O, iPad 10.5, iPad 11, iPad 12.9	The last_modified date changed but to Feb 09, 2018 (today), not to the one specified in the replacement regex (and I did try several variations). After several failed trials, I eventually created a custom column #modified with format yyyy-MM-dd and it worked there. Mayhaps last_modified is a special column or something that can't simply be edited by the user? Curious, though, will a datetime column accept yyyy-mm-dd regardless of the display format (e.g. MMM d, yyyy)? Thanks! Last edited by ilovejedd; 02-09-2018 at 09:52 PM.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Date Like a Ninja: 75 Kick-Awesome Date Ideas	Teutonic0	Self-Promotions by Authors and Publishers	5	02-07-2015 12:26 PM
Help with S/R from text column to a date column	belton180	Library Management	2	04-02-2014 05:23 AM
Bulk Changing Published Date To Date	hmf	Calibre	4	10-19-2010 10:19 PM
new official shipping date / US invitation date	R2D2	iRex	18	07-06-2006 02:32 PM

02-09-2018, 01:34 PM	#3
ilovejedd hopeless n00b Posts: 5,111 Karma: 19597086 Join Date: Jan 2009 Location: in the middle of nowhere Device: PW4, PW3, Libra H2O, iPad 10.5, iPad 11, iPad 12.9	I don't think named groups work with bulk metadata s&r. I just do it that way because I can get cross-eyed when editing regex. Tried \3 \4 \2 (#custom to last_modified) and it didn't work. Note, I have all my dates set to MMM dd yyyy in Tweaks.

02-09-2018, 09:57 PM	#6
kovidgoyal creator of calibre Posts: 43,871 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	It's pointless trying to change last_modified. Whatever change you make will get overridden immediately, since doing a search and replace modifies data, which means last_modified gets updated.

02-09-2018, 10:47 PM	#7
ilovejedd hopeless n00b Posts: 5,111 Karma: 19597086 Join Date: Jan 2009 Location: in the middle of nowhere Device: PW4, PW3, Libra H2O, iPad 10.5, iPad 11, iPad 12.9	Was wondering if that might be the case after my 5th regex. At first, I just thought it didn't like the replacement format. Then I tried replacing with template {date} and when it still changed back to Feb 9, that was when I exited calibre and created a custom column.