If you are good with SQL, then this should be the way to go, no? No need to force KM into domains that are easier solved by the proper ways.
But, as @Sleepy has proposed, manipulating a CSV export would be a good solution, too. Always better than tinkering around with GUI stuff (pressing buttons, selecting menus, etc.)
1 Like
Tom,
Yes, the second column/field is always blank to begin with.
Whether it actually has a null (ASCII 0) in it I'm not so sure. Doubt it.
In any case with the process I'm trying to automate it's always safe to paste the after-the-colon-maybe-with-space(s) string into that field regardless.
I'm also quite happy to do this record by record a) to keep an eye on things; b) because some colons are to be retained and c) because some Titles have more than one colon.
Your points are valid. I don't think I'm worried about your first point, but the second one does worry me. I might have to use awk instead of sed, then. However Mark said he might enjoy creating the KM macro in this case, so who are we to rob him of that pleasure? This is really a KM forum, not an SQL or AWK or SED or CSV forum.
But I don't see colons in the other columns, so maybe it actually can be done in a truly simple one liner.
1 Like
You're right, Tom, about SQL; and the vendor (at Bruji) is always extremely helpful.
But in this case, using KM serves two purposes - one of which is for me to teach myself (and learn from all of you, thanks!) just what it can and can't do.
Am working my way through the MacSparky videos atm 
Well, then it’s easy:
Set the maxEntries variable in my script to 1 and run the script record by record. Just make sure you order the database in a way that isn’t affected by the changes.
Edit:
Nope, sorry, above I said nonsense. You have to order the records in a way that a not yet changed record is on top. (So, maybe reverse changed time, or whatever criteria you have at your disposal.)
1 Like
Time for me to experiment with that script, thanks, Tom.
And high time I thanked you for it.
As I said before - what a terrific forum this is.
Discourse has told me that - as a new poster - I've now reached the maximum number of posts I'm allowed for one day:-(
So I have to add this here:
Sleepy - Yes, I always work from backups; thanks… would it surprise you to know that in addition to TM I actually rotate SuperDuper clones across no fewer than 11 (that's right eleven) external backup drives?
Tom - point taken about understanding where KM fits in to all other (such) utilities. I have played with awk, sed and a lot with Perl. I'd actually have no difficulty massaging those strings with those - after a bit of de-rusting
I'm committed to familiarising myself with KM and thereby making sure I use it only in the most appropriate ways.
I've also experimented with Automator and have Alfred installed.
I advise you to make an export of your library in case you mess things up. You aren't likely to mess things up, but it seems prudent at this juncture to have a backup.
1 Like
KM might appear as a all-in-one tool. And if you force it, you will likely succeed. Somehow. But in reality KM is probably “only” the closest to an all-in-one tool I’ve seen on the Mac. There are many situations where you’re better off with some other tools, for example CSV files -> Awk, Perl; SQL -> SQL; if an app has an AppleScript dictionary -> AppleScript, etc.
The great thing with KM is that you can combine all those other tools into a KM macro, if you like (in form of scripts, and even combine them with KM actions inside the macro). And that KM provides a wide choice of triggers for those macros then.
1 Like
I think your current requirement is a great opportunity to learn how to use KM. I'm hesitant to give you a "one-line solution" because I want you to jump out of your nest and learn to fly.
1 Like
Weird that the forum script doesn’t allow more than 3 posts a day. But we got your PSs.
And Yes, me too I always have to de-dust after having written the last Awk script 8 months ago. Similarly, but not that hard with Perl.
But, since you said you wanted to go one by one, the UI scripting approach seems more reasonable either way. Could you try out the script?
1 Like
@MarkSealey, what about the UI AppleScript? Is it fine or do we have to prepare for further efforts? 
1 Like
Mark, I've removed the posting restriction for you, so you should be able now to make more posts and new topics. You may need to log out / log in for this change to take effect.
1 Like
Thanks so much!
All extremely helpful
Have been experimenting with Gabe's script for the past couple of hours.
I set the BookPedia window to '125, 125' - per a version of Sleepy's Action here:
But where I'm stuck is the four parameters in the OCR Area action:
I understand the basic principles of X,Y co-ordinates, of course - but not how they're implemented in this case… the Wiki docs are still in progress; and I couldn't find anything anywhere else. Nor what the various 'inner' and 'outer' (???) co-ordinates and negative values must mean in the Mouse Display window.
I think if I can get the params to capture the exact text I need, I'll be close.
Thanks!
Mark, I understand you are just experimenting, and that is fine.
But I have to say that using OCR for your use case is going to be the slowest and least reliable method.
Of all of the methods I've seen presented (and I haven't studied any of them in detail), I suggest trying @tom's UI scripting. I'm a bit concerned about being in a loop of 8,000 records, but if you test with 10 records or so, and put a good pause (say 0.2 sec) between each record, then I think you should be OK,
But as the other's mentioned, be sure to make a backup of your BookPedia just before you start testing and before you start a production run.
Some other thoughts:
- As part of the UI scripting, do a save every few records.
- Make sure the script does a test to ensure that the data needs to be converted; else just skip to the next record (but issue a log statement for the skipped record).
- The more I think about this, the more I'd suggest running the script from Script Debugger 7, rather than KM. This will give you more control, and make it easier to make changes to the script and test.
Let us know if you have any questions.
1 Like
Me, too (afraid of a 8000 loops). That’s why I limited the script by default to three rows.
But later the OP said that…
I'm also quite happy to do this record by record a) to keep an eye on things; b) because some colons are to be retained and c) because some Titles have more than one colon.
So, the script should be at least an ideal testing ground.
2 Likes
Thanks, JMichaelTX!
Your points are well taken. Yes, this has been a great way for me to experiment and find out how KM really works.
I always back everything up. Fanatically so!
Thanks for the endorsement of Tom's work. In fact, I really need to be able to edit BookPedia records one by one - chiefly because, although splitting the titles on a colon is the main objective, there are times when:
- a title has more than one colon - and so cannot be reliably split
- I need to be able to override such a redistribution of the title string - and so must not be split
I've actually learnt a lot today - courtesy of all the posters who kindly contributed.
This is what I've come up with: BookPedia Title Split.kmmacros (8.9 KB)
BookPedia doesn't lend itself easily to export (as CSV etc), external manipulation, and then re-import.
The only thing which appears not to be working in the Macro I've built is that it cuts off characters at the end of the 'Original Title' (second portion, after the colon) string.
I really didn't expect such an overwhelmingly friendly and helpful reception here. Very grateful!
Tom,
Indeed it is. I'll take another closer look at it tomorrow and see if I can adapt the text-massage portions of it to work record by record. Thousand thanks…
The script already works record by record (by nature of the script). Just set the maxEntries variable to 1. So the loop will stopped after the first record.
Not sure what you mean with that… Any chance to explain?, sometimes I’m blind…
1 Like