mAirList Local Database Export CSV Data

Hi

I’m interested in using the database export option that creates a CSV file with all the tracks in the database in it.

Too a large extent I’ve been able to guess which fields are which…

DatabaseID, title, artist, filepath, ???

But I’m not sure about the final field, I’m pretty sure it’s something to do with track length? But the units make no sense to me.

Any help with how to (assuming the last field is actually track length), convert it to seconds?

Thanks
George

First of all, please make sure you’re using the latest version (3.1.6), because the output format has changed.

Here’s the current format:

  1. Internal (mAirListDB) ID - see comment below.
  2. External ID
  3. Filename
  4. Title
  5. Artist
  6. Comment/description
  7. Ending
  8. Duration
  9. Cue In marker
  10. Ramp 1 marker
  11. Ramp 2 marker
  12. Ramp 3 marker
  13. Hook In marker
  14. Hook Fade marker
  15. Hook Out marker
  16. Outro marker
  17. Start next marker
  18. Fade Out marker
  19. Cue Out marker
  20. Anchor marker

All durations and markers are given in units of seconds.

Thanks for the speedy response, I hadn’t thought to see if there was a new version available.

Now I can expand my project a little :slight_smile:

gargeorge-

Welcome. The announcements for new updates are always posted on the homepage of mAirList. It’s best if you check every so often to see whether a new update is available.

Regards, Alec M.

Or monitor any of the following:

http://twitter.com/mAirList
http://www.facebook.com/mAirList

Hi Torben, not exactly matching the topic, but would it be possible to transfer Data from Network to local Database and vice versa with this function?

No, because the information stored in the CSV file isn’t complete (no attributes, no folder assignments, no hour templates, …).

Not really; you would need to write some SQL queries for both SQLite AND PostgreSQL to be able to do that; and keeping the two DBs in sync on a regular basis would be a nightmare. I’ve done similar stuff before in my professional career, for multinationals, and it’s NOT as easy as it seems, unless you totally empty and re-write one or both databases each time.

You could do a copy as a one-off snapshot, but trying to copy changes from one database to the other regularly would take more work than I suspect you’d be willing and able to devote to doing it.

BFN
CAD

I see that mAirListDB seems to offer a CSV import option - I would like to offer this template in MyLibrary (my own program). Is the above template also valid for imports and do I need to provide/generate an “InternalID”? Typically, the systems I can export to rely on “Cart” numbers which seems to be your “ExternalID” so I guess it’s probably workable.

Sir has hit the proverbial nail on the head.

I don’t think I’m being improper when I say that External ID was added for precisely that purpose at the behest of a major European public service broadcaster, to ‘tie up’ with their in-house library system.

(PS: Charlie, can you please reply to the PM I sent you? If you dont see one, please PM me and I’ll re-send it.)

BFN
CAD

In fact, the purpose of the new “External ID” field is to keep track of the IDs that the particular files have in a third-party database. This is very useful if you want to connect mAirList to an existing third-party database.

Regarding the CSV import, yes, you can import CSV data (for existing files only!) using this file format as well.

Here’s some notes about the import feature (copied from an e-mail I sent someone a while ago):

You can set any field to "-" (without the quotes) to keep the current value. (Remember that CSV import will never add any new tracks, but only modify tracks already contained in the database.)

If an internal ID is specified, mAirList will try to identify the track by the internal ID. Otherwise, mAirList will try to identify the track by either its external ID or the filename.

So, for example, when you want to set the external IDs of a number of files, create a CSV file where the first column is empty (will force mAirList to use the file name for identification), the second column contains the new external IDs, the third contains the full file names, and columns 4 through 20 are set to “-”:

,MYNEWID,“F:\music\myfile.mp2”,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-

Thanks for the reminder: I do need to add this to the manual! :smiley:

BFN
CAD

Thanks for the info - this looks rather useful :slight_smile:

I’m sure it is. But I’ll probably have to add support for custom attributes. Which is rather tricky, because you don’t have a fixed set of columns anymore.

Just like Columns= for Playlists, right? :wink:

BFN
CAD

[quote=“Torben, post:11, topic:7259”]If an internal ID is specified, mAirList will try to identify the track by the internal ID. Otherwise, mAirList will try to identify the track by either its external ID or the filename.

So, for example, when you want to set the external IDs of a number of files, create a CSV file where the first column is empty (will force mAirList to use the file name for identification)[/quote]

Which of the two statements is correct? If it is the first one (which seems likely), then it may not be possible to change an existing External ID in mAirListDB for records which HAVE an existing External ID; in which case, the second statement (will force mAirList to use the file name for identification) cannot be true for all cases.

In other words, if a different item in mAirListDB now has the External ID which was previously used by another pre-existing item (it could happen!), then mAirList will match to the wrong item, because the External IDs match, correct? And this could happen (depending on how it is done internally) while processing a CSV file.

If all that is correct, then better import logic would be:

  1. Does Internal ID match? If so, process CSV record and exit.
  2. Does External ID match? If not, go to step 4.
  3. Does file path/name match? If so, process CSV record and exit.
  4. Does file path/name match? If so, process CSV record.

which simplifies to:

  1. Does Internal ID match? If so, process CSV record and exit.
  2. Does file path/name match? If so, process CSV record.

… because we just proved that a match on External ID is irrelevant to the logic and thus unnecessary. Even if the External ID matches, you also need to match on file path/name to be certain that you are updating the correct mAirListDB row.

If you are now thinking, ‘what if there’s an matching External ID but no filename?’ then you are correct: nothing will hapen using the logic above. We do need to add one further test:

  1. Does Internal ID match? If so, process CSV record and exit.
  2. Does file path/name match? If so, process CSV record.
  3. Is file/path name = ‘-’ AND External ID matches? If so, process CSV record.

Step 3 above assumes that the user wants to change some field(s) other than the file path/name, and we just have to trust the user to be using the correct External ID values. :smiley:

So in short, the order of precedence for matching should be:

  1. internal ID,
  2. file/path name
  3. external ID (only tested if file/path name is ‘-’ == ‘leave intact’).

Changing the order (if this is not currently how it works) WOULD allow you to change the External ID of any item in mAirListDB after it has been assigned. This may be necessary if the external database has been exported/imported or rebuilt.

BFN
CAD

Ok, so your point is that the filename (if present) should have a higher priority than the external ID, because it is “more unique”, and cannot be changed anyway?

Correct: and that logic DOES allow External ID to be changed later if necessary.

BFN
CAD