Wednesday, June 30, 2010

How to Export Tagged Songs from Shazam

I have over 700 songs tagged in Shazam, the scary thing is that this information is only stored on my phone (I rarely backup my phone). That means if anything happens to my phone, the data is gone. In fact, I've already lost my Shazam tags twice: once when I upgraded Shazam and it would crash when opening it; and another time when my phone got bricked. Storing this much information in Shazam is a bad idea. As I've said before, I like cloud services. Had this data been stored in the cloud I wouldn't have this problem. Unfortunately, Shazam doesn't provide that option. The only way to export data in Shazam is the "share" link. Tapping the "share" link and sending an email 700 times didn't sound too appealing... so I tried finding a way to export all the data with minimal effort. If you follow these steps, you will get the data as text (artist, title, version, and date/time) for each of your tagged songs.


These instructions are for Windows, however, you can probably make this work on a Mac too with simple modifications. For some of the steps, I'll include information for the Mac. This guide is for non-jailbroken phones. If you have a jailbroken phone, there are probably easier tutorials you can follow online.

1. Backup the phone with iTunes
Backing up your iPhone is the only way to get Shazam's information on the computer. Make sure that you create an unencrypted backup.

2. Find the *.mddata file where Shazam stores your information
First, locate the location where the backup is stored. From Apple's iPhone and iPod touch: About backups:
Mac: ~/Library/Application Support/MobileSync/Backup/
Windows XP: \Documents and Settings\(username)\Application Data\Apple Computer\MobileSync\Backup\ 
Windows Vista and Windows 7: \Users\(username)\AppData\Roaming\Apple Computer\MobileSync\Backup\
Inside of that folder you should see a file with lot's of numbers and letters. That's your backup folder, open it up. You will need this path in the next step.

Now you need to open up a command prompt to that folder:

  1. Start > Run
  2. Launch: cmd 
  3. Navigate to the location of your backup (from the previous step) using the cd command. For example:
    cd C:\Documents and Settings\[user]\Application Data\Apple Computer\MobileSync\Backup\[backup-id]
  4. Now search for Shazam using findstr:
    findstr "shazam" *.mdinfo
  5. You should see it output something such with white bold letters in front with the unique ID of shazam. If you see more than one file, then you can open up the folder and examine the size of the corresponding *.mddata file and see which one actually has data in it. If this is too complicated, you can use a search query such as findstr "madonna" *.mddata where "madonna" is a single word which should only be in Shazam (e.g. you don't have notes that contain "madonna"), otherwise you will get more than one result again.
  6. Now that you know the name of Shazam's unique ID, you should also see a corresponding *.mddata file which has the same name except that it ends with .mddata instead of .mdinfo.
3. Open the *.mddata in SQLite
  1. Download SQLite Database Explorer. You can get a binary from the SOURCEFORGE PROJECT PAGES link on the top right. I am using the beta version (sqlitebrowser_200_b1_win.zip). Note that there is also a Mac version available if you select View All Files.
  2. Launch SQLite Database Explorer and open the *.mddata from step 2.
  3. Go to the Browse Data tab on top.
  4. Where it says Table, select recresult. You should see all of your songs here.
  5. Go to File > Export > Table as CSV file choose recresult then Export.
  6. In the save dialog, be sure to include the .csv at the end of the file's name (e.g. Shazam Songs.csv).
  7. Use your favorite spreadsheet application (e.g. Excel) to open the file and extract only the data you need. The most important columns are previewArtist, previewTitle, and previewSubtitle.
4. Getting the Date/Time for each song (Optional)
For me I like to also know the date/time a song was tagged. If you noticed, this information is stored as a number in the date field. I'm going to use Excel to convert this to a date time I can understand. The date is stored as the amount of seconds since 1/1/2001 UTC.
  1. Create a new column immediately to the right of date (i.e. it should be column C).
  2. Enter the formula: =B2/60/60/24 for the second row. This converts the seconds into the amount of days they represent. Extend this formula to the rest of the cells downwards.
  3. Add another column to this column's right (i.e. new one should be D), Enter the formula:
    =DATE(YEAR(C19)+101, MONTH(C19), DAY(C19)+2) + MOD(C19, 1) - TIME(7, 0, 0)
    The important part here is the time zone offset from UTC (e.g. TIME(7, 0, 0), MST = -7 hours), you should change this to whatever time zone you want. Change the column's format to show the time. You can verify that this is the correct formula by comparing it to your iPhone's tagged song's date. Extend this formula downwards for the rest of the cells.
  4. You can now copy/paste this column along with the other three important columns and save them anywhere you like.
That's it. I hope it helps.

Note: if you have a really old backup and the files are stored as *.mdbackup, simply follow the above steps. The only thing you need to change is before you can open up the file in SQLite Browser, you need to modify the file a bit. Open it up in a hex editor such as HEdit. Create a backup of the file (just in case something goes wrong), then remove the first few lines, so that it starts with SQLite format 3, and then save it and you should be able to open it up in SQLite Browser.

Personally I'm saving the data in Evernote since it is much better suited to store this data. If you do end up pasting this information into Evernote, note that it will preserve the tab characters, you just won't see them. I'll probably end up processing the song names sometime in the future since it isn't terribly useful as a single note. At least I now have access to data that used to be locked up in Shazam though.

If you want more information about iTunes backup files, I would recommend reading this article.

15 comments:

  1. Hope you can help me.
    How can I export my Shazam tagged songs list created on my now not working anymore HTC td2 wm 6.5 device?
    How do I export that list?
    Here you can find my shazam database.
    http://forum.xda-developers.com/showthread.php?p=14493115#post14493115

    ReplyDelete
  2. Hi there, is there any way I can use the command findstr on mac os x? can't seem to get it working...

    Thanks

    ReplyDelete
  3. You can use ShaDown:
    shadown.inforall.net

    ReplyDelete
  4. I've been wanting to sock away my tags in a safe place for ages, so thanks for writing this.

    I didn't even get through the first stage of your instrux. I've searched without success for *.mmdata, MobileSync, Mobile…and even followed the ~/Library/Application Support/MobileSync/Backup/ path you recommend. I've been having some problems with my new iMac, so maybe it's me. Any ideas? I'm on Mac OS X 10.7.3.

    ReplyDelete
  5. hey there, what if you now backup your iphone to the cloud? how could we access the backup files that used to be stored on a PC in the directory you mentioned? C:\Documents and Settings\[user]\Application Data\Apple Computer\MobileSync\Backup\

    thanks in advance!

    ReplyDelete
  6. don't work, the findstr "shazam" *.mdinfo command don't work

    ReplyDelete
    Replies
    1. Ditto.

      C:\Users\EML\AppData\Roaming\Apple Computer\MobileSync\Backup\2b3a5030ff8bbdbdd5
      f4562dcf556039004d6aa5>findstr "shazam" *.mdinfo
      FINDSTR: Cannot open *.mdinfo

      Delete
  7. Is there an option for Android devices?

    ReplyDelete
  8. SELECT t.ZNAME as titre, a.zname as artiste, t.ZSUBTITLE as s_titre, d.zvalue as album
    FROM ZSHTAGRESULTMO as t, ZSHARTISTMO as a, ZSHMETADATAMO as d, zshaudiotagmo as z
    where t.z_pk = a.ztagresult
    and d.ztagresult=t.z_pk
    and z.z_pk= t.zaudiotag
    and d.zkey = 'Album'
    ORDER BY z.zdate desc;

    ReplyDelete
  9. shadown.inforall.net needs a Jailbroken device...?

    ReplyDelete
  10. Hi,

    Any idea of how to do this on the windows 8 app?

    ReplyDelete
  11. https://gist.github.com/xavhan/87717da0217b9b8299df

    ReplyDelete
  12. Take your time to read this article about how to backup iPhone.

    ReplyDelete
  13. In case it's of use for anyone, I have updated these instructions to work with iOS 9/iTunes 12 (November 2015) - see https://vaughanharper.com/2015/11/16/how-to-export-list-of-list-of-tagged-songs-from-shazam/ . Vaughan

    ReplyDelete