-
major problem; commas in our database
Hi, we’ve been using Cat DV for a few months now, we have it set up on 4 machines all feeding into our database, and we have 4 people in who are tagging our footage with keywords. our problem is that a couple of them didn’t get the hang of the comma/semi colon difference when they first started and have tagged quite a few clips with commas instead of semi colons. this means that instead of values like “blue” “red” “green” (separate) we have values like “blue, red, green” which is obviously not helpful. we could correct it all by hand but this would take a long time.
we’ve been told to use bulk edit, but this doesn’t perform the function we need, it seems to replace the contents of a selected box with the contents of another box or a preselected value. i was hoping search and replace would do the job as you can specify a value to replace, rather than a whole field, i.e. search for , replace with ; but it doesn’t seem to work like that.
i’ve tried this in many variations, using regular expressions etc, i was told that /n is a substitute for a comma as a separator of terms, but i haven’t been able to use this successfully.
can anyone help?
-
14 Replies
-
If you configure a user-defined field as a “multi-grouping” field then it can contain multiple keywords as separate items. When you view one of these fields in a single row the list of values is shown separated by a semicolon but internally it’s actually stored as a newline separator.
Normally you don’t need to know that and just use the CatDV user interface to add or remove keywords to a clip as required. If you need to “patch up the data” because it was entered incorrectly (and you don’t want to fix each one manually!) you can do so using Tools > Search And Replace. Choose the field you want to edit, enter the old delimiter eg. “, ” under Search For, and enter “\n” under Replace With. Don’t enter the quotes but do enter a space after the comma if that’s how the list is currently delimited. Check the “Use regular expressions”, then press OK.
The ability to enter \n as the replacement was only added a couple of months ago so make sure you’re using a recent version of CatDV (the current version is 8.1.2, see https://www.squarebox.co.uk/download1.html).
-
Hey there, you need a MySQL wizard to actually edit that stuff deep in the db.
Rolf or Kevin, if you have time to offer that service, let us know.
If not, I can hook you up with someone who could likely clean that up for you. Buzz me offline and we’ll see what we can do.
bryson
bryson “at” hidefcowboy.com
hidefcowboy.com
-
Hi Rolf,
Thanks very much for your message, I have previously tried what you suggested on 8.1.1 and it didn’t work, and i’ve just downloaded 8.1.2 and it still doesn’t seem to want to work.
Here’s a picture showing what settings i’m using (there’s a space after the comma) and the offending field with the commas in,
https://i47.tinypic.com/wj8f0g.png
let me know what you think,
thanks,
-
Sorry guys, my reader wasn’t up to date and I didn’t see that rolf was on it!
Keep us posted on that.
bryson
bryson “at” hidefcowboy.com
hidefcowboy.com
-
Are you seeing this like 40s/n50s/nfeminine?
You need \n not /n
-
“Are you seeing this like 40s/n50s/nfeminine?”
Sorry, i’m not sure what you mean, I’m seeing it in CatDV as it appears in the picture, with commas in it. That field (themes/trends/styles) has all sorts of different values in it, and sometimes they’ve been separated by commas.
the quickest way i’ve found of correcting the commas is to group by the field i’m checking for that catalogue (themes/trends… for example) and scanning down the list of terms until I see a value like ’40s, 50s, feminine’ which sticks out for it’s length. then i go in, click on the star at the end of the text box, type each value seperately and tab down to the plus sign, then back up for the next one. once they’re all in i remove the original value. as you can imagine this is quite time consuming.
i was hoping i would be able to use the settings in the picture i sent to replace the comma with a semi colon or a \n to seperate the values that have already been entered.
-
Sorry, it’s early, i understand what you mean now
i had tried both /n and \n and left the incorrect one in for the screen shot, but i tried both
it doesn’t seem to want to find ‘, ‘ at all, it said ‘0 clip(s) updated’ and didn’t put the incorrect ‘/n’ in when i tried that, as you imagine it would
i don’t know why it’s not recognising the comma, it’s the same whether i have regular expressions turned on or off,
maybe you could make a new catalog where you are, enter some values with some commas in and attempt to search and replace them if you have time, if not don’t worry, it would just help to know i’m not missing something obvious
-
Hmm, difficult to know what’s going on then, as it worked in my tests. Maybe your current delimiter isn’t a comma followed by a space (it could be some other non-printing character). Try copying and pasting the delimiter from the field into the search and replace dialog, and make sure there are no spurious extra characters in your ‘search for’ field. You could also try doing some simpler search-and-replace commands as a test, eg. replacing one word or one letter with another one to see if that works.
-
I FIGURED IT OUT 😀
i did a search and replace ON the semi-colon character ;
it worked perfectly, goodness knows why my copy works like this, but i’ve managed it, so i’m pretty happy now, that’s going to save me hours of work.
i appreciate your patient help, i know how frustrating remote support can be, thanks very much
-
Ah, that makes sense. Remember how I said the different values of a multi-field are stored internally separated with a new line character but displayed with a semicolon when they’re shown one line? Because of that, it would be very confusing if any of the values themselves contained a semicolon, so any semicolons are turned into commas before they are displayed.
Reply to this Discussion! Login or Sign Up