Search and Replace with Regex

[Wiggo]

I have a column (#startdates) with comma separated text, like tags, with a date in the following format: 2024.09.21

I want to insert this date into a date column (#dateread) with the format 'dd MMM yyyy'.

Could someone please help me?

Edit: Or does it work easier with action chain and a template?

[chaley]

Originally Posted by Wiggo

I have a column (#startdates) with comma separated text, like tags, with a date in the following format: 2024.09.21

I want to insert this date into a date column (#dateread) with the format 'dd MMM yyyy'.

Could someone please help me?

Edit: Or does it work easier with action chain and a template?

Attachment 211076

Is the date always the first value in #startdates? The only value? The last value? If not, how do you know which value to use?

If it is the first or only value, then I suggest you use a search & replace using "Template" as the source. The template would be

program: format_date(sublist(#startdates, 0, 1, ','), 'dd MMM yyyy')

If it is the last value then this should work:

program: format_date(sublist(#startdates, -1, 0, ','), 'dd MMM yyyy')

The search & replace dialog contents:

• Search mode: Regular expression
• Search field: {template}
• Template: as above
• Search for: ^(.*)$
• Replace with: \1
• Destination field: #dateread

If the date is buried somewhere in a multi-valued column then things are more complicated. You will need to search in the template for the date probably using a for loop, processing the first date you find.

As always, be careful when using search & replace. Try it on a few books before running it on your entire library. Backing up your library is a good plan.

[Wiggo]

Thank you so much, chaley!

Unfortunately I get this error

calibre, version 7.19.0
FEHLER: Fehlgeschlagen: Unknown string format: S/R-Vorlagenfehler Formatter: Failed to scan program. Invalid input '#startdates, 0, 1, ','), 'dd MMM yyyy')' near the end of the program

Traceback (most recent call last):
  File "calibre\gui2\dialogs\metadata_bulk.py", line 198, in do_it
  File "calibre\gui2\dialogs\metadata_bulk.py", line 535, in do_all
  File "calibre\db\cache.py", line 85, in call_func_with_lock
  File "calibre\db\cache.py", line 1636, in set_field
  File "calibre\db\write.py", line 568, in set_books
  File "calibre\db\write.py", line 568, in <dictcomp>
  File "calibre\db\write.py", line 73, in adapt_datetime
  File "calibre\utils\date.py", line 114, in parse_date
  File "dateutil\parser\_parser.py", line 1368, in parse
  File "dateutil\parser\_parser.py", line 643, in parse
dateutil.parser._parser.ParserError: Unknown string format: S/R-Vorlagenfehler Formatter: Failed to scan program. Invalid input '#startdates, 0, 1, ','), 'dd MMM yyyy')' near the end of the program

[chaley]

Sorry, I forgot the '$' when I replaced my test lookup name with your lookup name. It should be

program: format_date(sublist($#startdates, 0, 1, ','), 'dd MMM yyyy')

[Wiggo]

Originally Posted by chaley

Sorry, I forgot the '$' when I replaced my test lookup name with your lookup name. It should be

program: format_date(sublist($#startdates, 0, 1, ','), 'dd MMM yyyy')

It works!

But I get this error message for 'Dezember' - language thing?

calibre, version 7.19.0
FEHLER: Fehlgeschlagen: Unknown string format: 30 Dez 2023

Traceback (most recent call last):
  File "calibre\gui2\dialogs\metadata_bulk.py", line 198, in do_it
  File "calibre\gui2\dialogs\metadata_bulk.py", line 535, in do_all
  File "calibre\db\cache.py", line 85, in call_func_with_lock
  File "calibre\db\cache.py", line 1636, in set_field
  File "calibre\db\write.py", line 568, in set_books
  File "calibre\db\write.py", line 568, in <dictcomp>
  File "calibre\db\write.py", line 73, in adapt_datetime
  File "calibre\utils\date.py", line 114, in parse_date
  File "dateutil\parser\_parser.py", line 1368, in parse
  File "dateutil\parser\_parser.py", line 643, in parse
dateutil.parser._parser.ParserError: Unknown string format: 30 Dez 2023

Edit: Looks loke a language thing. When I switch calibre to English, everything works.

[chaley]

Originally Posted by Wiggo

But I get this error message for December, but the tag is still written - language thing?

calibre, version 7.19.0
FEHLER: Fehlgeschlagen: Unknown string format: 30 Dez 2023

Yes, it is a language problem. Calibre is trying to parse the date string to store it in the database and doesn't know what "Dez" means.

The right way to fix this is to change (if necessary) the definition of the #dateread column to have the format you want, 'dd MMM yyyy'. Once you do that then you don't need the format_date() at all. Simply copy the date from #startdates to #dateread as it is. Calibre will convert it to a date then display it using the format you specify in the column definition.

In this case the template becomes

program: sublist($#startdates, 0, 1, ',')

You can do it without using template mode if the date you want is the only value in #startdates.

[Wiggo]

Originally Posted by chaley

Yes, it is a language problem. Calibre is trying to parse the date string to store it in the database and doesn't know what "Dez" means.

The right way to fix this is to change (if necessary) the definition of the #dateread column to have the format you want, 'dd MMM yyyy'. Once you do that then you don't need the format_date() at all. Simply copy the date from #startdates to #dateread as it is. Calibre will convert it to a date then display it using the format you specify in the column definition.

In this case the template becomes

program: sublist($#startdates, 0, 1, ',')

You can do it without using template mode if the date you want is the only value in #startdates.

It works again! Fortunately my column was already defined with 'dd MMM yyyy'.

[Wiggo]

One more question please, chaley.

The timestamp has the time 00:00:00 CEST. Can this be changed?
When daylight saving time is changed again, all dates have a timeshift of -1 day, which admittedly annoys me a bit.

[chaley]

Originally Posted by Wiggo

One more question please, chaley.

The timestamp has the time 00:00:00 CEST. Can this be changed?
When daylight saving time is changed again, all dates have a timeshift of -1 day, which admittedly annoys me a bit.

Use date_arithmetic() to add the number of hours you want. Example: to add 12 hours to the date making it noon, use:

program: date_arithmetic(sublist($#startdates, 0, 1, ','), '12h')

[Wiggo]

Thx, chaley!