Tuesday, September 11, 2012

Export playlists from Google Play Music


After some brief googling, I was unable to find out a way to export playlists from Google Play Music, so I just figured it out for myself.

In order to do this, your Android device must be rooted, otherwise you won't have access to the music database file where the playlists are stored.

If you don't have an Android device, there's a chance you could use the android emulator from the Android SDK. You'd have to find an .apk file for the Google Play Music app, which shouldn't be too hard.

  1. First, make sure all of your tracks and playlists are synced with Google's servers:
    1. Install the Google Play Music app on the Android device. If it's already installed, open up the Google Play Store app, go to Menu --> My apps, and if it's in the list of updates, update it.

    2. Connect your Android device to the internet (data or wifi)

    3. Open the Google Play Music app

    4. This step will probably change as Google continues to update the app, but essentially you want it to show all of your music in the cloud and refresh it. The current way to do this is:

      At the top left of the app, make sure it says All music. If it says On device, tap it and change it to All music. Click Menu --> Refresh

    5. Wait for all of the tracks and playlists to sync (you should be able to see all of your playlists and music in the app)

    6. Exit the Google Play Music app by pressing the back button

  2. Next, you need to get the music database file from the Android device. The file is located at /data/data/com.google.android.music/databases/music.db. There are several ways to do this:

    • You can use a file manager app, browse to the file, copy it to your SD card, then plug the phone into your computer and copy it from there. Whichever file manager app you use, it needs root access. I'm currently using File Explorer by NextApp with the Root Add-On.

    • Using a file manager app, browse to the file and send it to yourself using whatever method is easiest for you (email, Dropbox, etc.).

    • As an Android developer, because I already have the Android SDK installed, my preferred method is to transfer the file using the Android SDK Platform Tools over the command line.

  3. Open the music.db file with your favorite SQLite database client (I prefer Sqliteman) to get the playlist data, using this query:

  4. select name as "Playlist Name", artist, title from listitems join music on listitems.musicid = music.id join lists on listitems.listid = lists.id order by "Playlist Name";

    From there you can export the data to a friendly format, such as a CSV file. To do a quick dump of the data from a Linux/Mac terminal, you can run this command:

    sqlite3 music.db 'select name as "Playlist Name", artist, title from listitems join music on listitems.musicid = music.id join lists on listitems.listid = lists.id order by "Playlist Name"' > playlists.txt

There are a lot of other columns available, such as album. to get the full list, just replace everything in the query from select to from with *, i.e. select * from listitems...

Enjoy!

18 comments:

  1. Thanks for this. I gave it a try, but when I run the adb command (I ran without ./ as I got an error), I get an error saying Permission denied. Any ideas?

    ReplyDelete
  2. I've updated the instructions with a couple of things that might work. if they don't work, and you're using a windows machine, you may additionally need to install USB drivers for your device. they should be available from the manufacturer.

    ReplyDelete
  3. Thanks for this, it worked for me. Any idea how to get this playlist to import to iTunes?

    ReplyDelete
  4. That'll probably involve a couple things. The first thing is getting it into a format iTunes will import. One of the most common playlist formats is m3u, which I think iTunes will import. You'll have to research how m3u files are formatted. This might help: http://superuser.com/q/88033/93066

    The trickier part is putting the paths of the files that correspond to each track into the playlist. You could probably write something in Python that might do the trick, but it would be a lot of work...

    ReplyDelete
  5. Hi there and thank you!
    Is there any way to copy the play list out of google play and transform it into a excel file? Or making a screenshot of the play list and transform it than into an excel with the help of an ocr program. Next step would be to have the assistance of an music data program, which reads the excel and scans your music lib for corrosponding files and their paths. In the end, that program could generate an m3u. Wouldnt that work?
    I mean it sounds a liitle bit detour, but once you know the scheme, it should go easyly and flowing.
    Greetings

    ReplyDelete
  6. If you wanted to use this data for the purposes of scanning your filesystem with another tool, an Excel spreadsheet probably wouldn't be the best format. Really what you would need to do first is find a tool that will scan your filesystem, and then see what kind of file format it expects.

    As far as converting the data into a specific format, there's lots of information online on how to do that. You could use Python to convert the data pretty easily. While you're at it, you could probably use Python to scan your filesystem and create the M3U file, in which case something as simple as a CSV or even sqlite3's default format (one record per line, fields separated by |) would be fine. XML and JSON are also great formats for data, but that might be overkill in this situation.

    If you don't know Python, it's fairly easy to learn, especially if you have any programming experience:

    http://bmaupin.wordpress.com/2012/02/10/free-resources-for-learning-python/

    ReplyDelete
  7. I had this problem on Windows 7 with a rooted Motorola Droid RAZR, and what worked for me was the following:

    1) adb shell
    2) su
    3) cp /data/data/com.google.android.music/databases/music.db /mnt/sdcard-ext/music.poop
    4) exit

    Then, in Windows My Computer, navigate to your phone's SD card, and copy over music.poop to your desktop.

    Then, rename music.poop to music.db, and you're set to use sqliteman.

    ReplyDelete
  8. Easier:
    "Table Capture" ansehen: https://chrome.google.com/webstore/detail/table-capture/iebpjdmgckacbodjpijphcplhebcmeop?utm_source=gmail

    ReplyDelete
  9. Beautiful. Been looking for this for ages. FYI, you don't really have to use ADB or the developer tools; you can just browse to the music.db file with an Android file manager and email or Dropbox the file to yourself.

    ReplyDelete
  10. That's a great point. As an Android developer, I already had ADB installed, and generally prefer the command line, so this was my preferred method. But it's probably not the best for everybody. I'll try to update my instructions when I get a chance to reflect this. Thanks!

    ReplyDelete
  11. a kind of work around without root i suppose would be to create a last fm account, set scrobbling time to like a second, and then just flick through all the songs in the playlist. It would then list them at last fm, where you can copy the list. I use this great tool which scans your entire music library and then repairs the playlist links. Its called 'list fix' http://sourceforge.net/projects/listfix/

    ReplyDelete
  12. I am using the following query, as my music DB seems to have a different naming scheme. If someone finds that they are getting errors on the query, try this:

    select name as "Playlist Name", artist, title from listitems join music on listitems.musicid = music.id join lists on listitems.listid = lists.id;

    ReplyDelete
    Replies
    1. Yeah, looks like they updated the database schema. I've updated the article. Thanks, Sean!

      Delete
  13. I'm moving my music from an Apps account (at work) to a normal Google account. Only way I found to do this is to download all the music and then upload to the new account. But when I do this, I loose the playlists. Is there a way to use the old music.db file and then trick my Play Music app into thinking it's for the new account, and then have all that sync up with Google's servers so it show's up nicely on the website too? Sort of want to strangle Google right now.

    ReplyDelete
  14. I got my playlist data out without rooting my device, using a file manager, or SQL, with Python code I found at eclectide.com/blog/2014/04/21/google-play-music-playlist-backup which uses the unofficial Google Music API and writes an XML file.

    ReplyDelete
    Replies
    1. Thanks! Looks like someone's made scripts using that API to make it even easier. I would imagine it's probably the easiest way to go about importing/exporting playlists these days: https://github.com/soulfx/gmusic-playlist

      Delete