I was surprised to discover how upset I was recently when I went into Shazam on my mobile phone and discovered that for some reason all of my Tags had been wiped. I had been travelling and heard some interesting new artists that I’d been meaning to note down, and also I had found going through my Tags was surprisingly evocative (I could remember where I was sitting when I’d heard that Caro Emerald track, etc). Of course I’d been meaning to find some way of noting them all down – I’d even investigated to see if I could find a way of exporting the list of Shazam tags – but I hadn’t quite got around to it… Anyway this calamity forced the issue and spurred me into action and figure out how to export the Shazam tags using iTunes.
Fortunately I had recently backed up my phone to my PC using iTunes, so all was not lost. I was looking for something that I could run on my Windows 7 system, and I wanted to minimise the amount of software I needed to install to do it (especially if it was of dubious provenance). I did some Googling and once I’d ruled out the items relating to Android or jail-broken phones I still found some useful hits – the most promising being http://www.sensefulsolutions.com/2010/06/how-to-export-tagged-songs-from-shazam.html. This was very well documented and I’m sure that it worked perfectly well in 2010, but sadly it no longer worked, so I set about trying to make it work now (in 2015).
Therefore, with grateful thanks to the writer of the original Senseful Solutions blog, here is a version of that posting which I have updated to work with iOS 9/iTunes 12. (This posting is written for Windows 7 – I’m sure that users of other operating systems will be able to apply similar principles.)
1. Back up the phone using iTunes
By backing up your iPhone using iTunes you get the Shazam database onto your computer. This needs to be an unencrypted backup.
2. Find the file where Shazam stores your information
First, find the directory where the backups are stored. From Apple’s How to back up your devices using iCloud or iTunes:
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 that folder you should see another folder whose name comprises lots of numbers and letters. That’s your backup folder, open it up – you will need this path in the next step. (If there is more than one backup folder then you’re probably best to take the latest.)
Now you need to open up a command prompt to that folder:
- Start > Run
- Launch: cmd
- 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]" - This directory will contain a large number of files with unhelpful filenames and with no extensions – some will probably contain photographs, some will contain voice recordings and some will contain databases from various applications – and we want to find the file which contains the Shazam database. In order to find the right file run the following command:
findstr /m shazamid *
This will list the filenames of the file(s) which contain(s) the word ‘shazamid’ – it may take a while to run but will hopefully give you one result, or possibly just a few results. (If it lists more than one filename it will be necessary to try them all one by one until the next steps succeed. I dare say that there is some way to find the right file by examining one of the Manifest files but I haven’t figured that out.)
(Alternatively you can runfindstr /m /s shazamid *from within the Backup directory – this does a subdirectory search for files containing ‘shazamid’.)
- Download DB Browser for SQLite from http://sqlitebrowser.org/ – there are compiled binaries for various operating systems, and there is a PortableApp which can be run without needing to be installed. I used Version 3.7.0 (SQLiteDatabaseBrowserPortable_3.7.0_English.paf.exe).
- Launch DB Browser for SQLite, click Open Database and open the data file(s) discovered in step 2. Note that when opening the file, you need to specify ‘All files(*)’ in the dropdown on the bottom right of the window rather than ‘SQLite database files…’.
(If step 2 yielded multiple filenames then DB Browser for SQLite may display an error message ‘Invalid file format’ if you pick the wrong file.) - Go to the Browse Data tab.
- From the Table dropdown select ZSHTAGRESULTMO. You should see all your Tags listed here.
- Go to File > Export > Table(s) as CSV file… , ensure that the table ZSHTAGRESULTMO is selected, ensure that the box Column names in first line is checked and click OK.
- In the save dialog, be sure to include the
.csvat the end of the file’s name (e.g.Shazam Songs.csv). - Use your favorite spreadsheet application (e.g. Excel) to open the file and extract the data you want. The artist names are in the column headed ZCACHEDARTISTSTRING and the track names in the column headed ZTRACKNAME.
At this point the job of exporting track names and artists is done. And it’s fascinating to see what other data Shazam are kindly storing for us. For example, as well as viewing the date/time that the Tags were made you can see where you were when you made the Tags.
(The Excel formulae here are not perfect – they don’t take into account daylight savings time, and the formula for Time doesn’t take into account time zone either. I ran out of time at this point, and I’m sure that there are many people much better at Excel formulae than I am…)
- Create a new column immediately to the right of ZDATE.
- Supposing ZDATE is in column D. In cell E2 enter the formula:
=D2/60/60/24This converts the seconds into the amount of days they represent. Extend this formula to the rest of the cells downwards.
- Add another column to this column’s right (i.e. the new column should be column F), and enter the formula:
=DATE(YEAR(E2)+101, MONTH(E2), DAY(E2)+2) + MOD(E2, 1) - TIME(7, 0, 0)
This shows the date. The last element is the time zone offset from UTC (e.g.TIME(7, 0, 0), i.e. MST = -7 hours) – this can be changed to whatever time zone you’re in. (You can check that the formula is correct by comparing it to Tags in the Shazam app.) Extend this formula downwards for the rest of the cells. - Create another column to the right (e.g. column G) and enter the formula:
=MOD(INT(D2),86400)
This will contain the time of day in seconds. - Create another column to the right (e.g. column H) and enter the formula:
=G2/86400
This will contain the time of day in displayable format. - If you want to plot on Google Maps the location of where you heard the track, then suppose ZLATITUDE and ZLONGITUDE are in columns I and J, in cell K2 enter the formula:
=HYPERLINK("https://www.google.co.uk/maps?q=loc:" & I2 & "," & J2)
This will then contain a hyperlink to the location. - Obviously there is lots more scope here for working with the contents of this table and exploring the other tables within the Shazam database…
As always, I hope that this is of use to someone!
Vaughan
Thanks for writing this up!
When I just tried it, I couldn’t see a ZCACHEDARTISTSTRING column.
But there was a different table ZSHARTISTMO with artist names.
I could get a 2-column table with track name and artist name by using this SQL command:
select ZSHTAGRESULTMO.ZTRACKNAME, ZSHARTISTMO.ZNAME
from ZSHARTISTMO
inner join ZSHTAGRESULTMO
on ZSHARTISTMO.ZTAGRESULT=ZSHTAGRESULTMO.Z_PK;
and then copy the whole table and paste it into a text file.
Perhaps this is helpful for someone.
Hi Koen – great – thanks very much for sharing this. Vaughan
Many thanks to Vaughan and Koen for the method.
The initial extract worked but I couldn’t easily find the relevant data.
The SQL provided by Koen has given me a lovely list that would have taken me hours to transcribe by hand otherwise.
Great job! Thanks again.
Dave
Hi,
Although I created unencrypted backup, when opening the file with Sqlite it asks for the password used to encrypt the database…
Solutions?
Thanks
solution:
search for a title you know it’t in your list instead of shazamid and the resulted file will be different and will not ask for a password when opening.
Great article! Anyone know where the Shazam tag data file is now located within the backup folder? In some earlier versions of iOS it was called backup.ShazamDataModel.sqlite. Presently trying to track it down within a Win-10 backup. Thanks!