Question about best approach to merge growing csv

Panorama X
#1

Hello,
Been away for awhile, hope everyone is well. I apologize ahead of time if I’m not searching the forum properly for this question, but I don’t think I found the exact solution.

Here’s my situation:
As one of my many “day” jobs I compose music. That music is registered online with a place called ASCAP, and it has all sorts of information important to the individual music cue. When I write a new cue that will be used in a production the first thing I have to do is register it there. It’s now a giant (and growing) list of cues and info that I am able to download as an excel or CSV.

What I need create is a database where, in addition to MOST of the info at ASCAP (not all), I need to add all kinds of additional metadata (song genre, instruments used, cue description, etc.)

What I’d like to do is take the CSV or Excel from ASCAP, automatically import only a few of the attributes (song title, registration number, etc.) so that they’re ready for additional metadata. However, I’m stumped as to how to set this up so that as new cues are added to ASCAP, I can run the process to ONLY import new cues, and of course I don’t want to overwrite the existing cues and lose all the metadata I’ve added.

Is this relatively easy to do? Is this going to be easier to do with the new version of Panorama X that’s been recently announced?

Thank you in advance for any guidance, and I hope everyone is staying sane and healthy!

All the best,
Biagio

#2

Are there multiple cues per record and are the cues saved in a single field. If so, how is one separated from another? If that is so, you’re creating an array. In that case you can import all cues, nit just new. Then run ArrayDeduplicate to remove the dupes and you’ve effectively imported only the new cues.

Or, are the cues all individual records and you’re wanting to just add the new? Do the import and sort by the cues. The new cues should be the second of all duplicated records. Use UnPropagateUp on the cue field to empty the duplicated (second) records. Select those cues that are not empty, delete the unselected and you’ve got it.

1 Like
#3

If I understand what you are asking for, I think the upcoming new version of Panorama will help you greatly with this. With the new version, you could import the csv data into a separate, temporary database. Then you can use the new join feature to bring just the data you want into your main database, without touching any of your existing data. Finally, you can discard the temporary database.

1 Like
#4

Thank you @JamesCook and @admin -

James - yes, cues are single records. Right now they have info like:

-cue name- -registration number- -registration date- -a bunch of other stuff I don’t need-

I want to pull those into a full PanX database where I can keep what I do need and then add things like

  • genre - -instruments- -description-

but ignore a whole bunch of fields that I don’t need, and then most importantly, I don’t want to accidentally replace records since I’ll always be pulling from a source that has duplicates. I think I follow what you’re saying with UnPropagateUp on the cue field. THANK YOU for taking the time.

Jim - if the new version makes that easier that’s AWESOME. I fancy myself a tech guy but I’m not :slight_smile:

I’m more of a “get help, copy and paste code, mess around until it works” person unfortunately! (But at least I know that about myself!)

In peeking at what you have coming, it does seem like it’s going to get easier - certainly from your description above as well. I will probably wait for that and just sort of map things out in Excel in the mean time. Congrats by the way - sounds like exciting stuff is coming to Pan X!

All the best,
Biagio

#5

Thanks again @JamesCook @admin for the previous replies. Getting ready to dive into this in earnest with the new version 10.2 public beta. After thinking it through (hopefully clearly!) here’s what I’m looking at doing. Would love your input on if this makes sense:

  1. I’m going to create an ASCAP database that essentially just gets updated when I download the spreadsheet from ASCAP. I’ll update this as discussed above by re-downloading it when necessary and merging / using UnPropagateUp to make sure they’re in sync and there are no duplicates. I’ll keep every field.

  2. I’m going to use the new relational database features to sync only certain fields from the ASCAP db into my new db that needs all the additional metadata, we’ll call this new one the Keyword db.

I think I have that right. Now for the tricky part —

I realized that in some cases I may want to create the cue and the metadata in this new keyword db before it’s ever added to ASCAP’s site, where it gets a registration number, etc. So the logic I think I want the Keyword db to use when looking at the ASCAP db is:

  1. Is there a new cue in the ASCAP db that’s not in the Keyword db? Pull it in.
  2. Is there a cue in the ASCAP db that’s ALSO in the Keyword db, but the cue in the Keyword db is missing a registration number? Just pull in the registration number and add it to the cue in the Keyword db (thus keeping all the keywords I’ve already added while waiting for the ASCAP registration number to come in.)

I hope that makes sense. Thanks for taking the time and feel free to just point me in the right direction or redirect me. Have a great day!

Biagio

#6

Are you saying that the online ASCAP database will contain duplicates? That seems odd.

Sounds like a good plan.

If I understand you, the join operation (new in 10.2) should do that for you automatically. Which may be what you are saying.

I don’t know what you mean by “pull in” in this context.

Ok, maybe I do know. They registration number is your key field, correct? So what you will have to do is manually figure out the registration number and input it into the proper record. in the Keyword db. Then you can do a join to bring over the rest of the fields from the ASCAP database. Hopefully you could do searches based on the other fields to narrow down your search for the new registration number.

I think it sounds like you have a good handle on your approach to this.

1 Like
#7

Thanks @admin - as always I really appreciate your reply and sorry if some of these were pretty basic.
I think I wrongly explained a few things above, but overall I think I’m good until the “key field”.

Once I upload a cue to ASCAP it gets an “ASCAP Work ID” and a few other fields are filled in automatically.

The thing that would be exactly the same between my Keyword db and my ASCAP db would be the cue title itself - those are unique. Is it a bad idea to use the cue title as the “key field” or is that actually the right solution, where I could do a join based on the cue name itself?

Thank you and have a great night!

Best,
Biagio

#8

If the cue title is unique then that would make an excellent key field.