Friday, January 3, 2025

Backup and restore VLC playlists on Android

I had to look all over the internet to cobble together the following information about how VLC stores playlists on Android and how to extract a copy of that information.

VLC stores playlists in a SQLite database (including any .m3u files that you copy to your device). Any edits  that you make on Android are to the database, not to the original .m3u file (which remains unchanged). That database is stored in a folder that only the VLC application can access (unless you "root" your phone).

So, when you replace your phone, you need to move your playlists to your new device. Unfortunately, it's not as simple as it should be.

Through an "advanced" tool inside VLC you can copy your current media database, vlc_media.db, to a place that you can access, the root of your internal memory. Then you can use SQLite to read that database and extract the playlists. Then you can save that data into new .m3u files and upload those to your new phone (along with copying all the data or moving your SD card to the new device).

Here are the details of each step:

1. Backing Up vlc_media.db

Backup playlists and everything else in the file vlc_media.db
  • Launch VLC on your Android device
  • Go to … More
  • Tap on Settings
  • Scroll to Extra settings and tap on Advanced
  • Tap on Dump media database
This will create a backup of the vlc_media.db file in the root of your internal storage. 

2. Transfer the database

  • Connect your Android device to your PC via a USB cable
    • Select "USB for File Transfer" if asked on your Android device
  • Browse to your Android device using Windows file explorer (it will appear on the left as one of the devices)
  • Look in the root of the "phone" (not the "card") device under your phone in explorer
  • Copy the vld_media.db file to your windows download folder

3. Download SQLite

Now you need a copy of the free SQLite database tool. Visit the SQLite download page and grab the "command-line tools" bundle. It should be a zip file named something like sqlite-tools-win-x64-nnnnnnn.zip where the nnnnnnn is a number representing the version.

You'll need to unzip this in your download directory. You actually only need to extract one file out of the zip, sqlite3.exe.

4. Export the playlist data

Now you can do the following in a windows command prompt.

> cd %userprofile%\Downloads

> sqlite3 vlc_media.db
SQLite version 3.47.2 2024-12-07 20:39:59
Enter ".help" for usage hints.
sqlite> .once playlist-raw.txt
sqlite> select pl.name, path||filename from playlist pl inner join playlistmediarelation plr on playlist_id=id_playlist inner join media on id_media=media_id join folder on folder_id=id_folder order by id_playlist, position;

This will generate a list of all the playlists stored in the database and all the paths and filenames of the songs in each playlist. The output will be written to a file named playlist-raw.txt in the current directory.

The format of the output is the playlist_name|path/song.mp3. You will then need to copy each group of songs into a separate playlist (see 4.5 below for a way to export them to individual playlist files).

The playlist-raw.txt file might look something like this:
Handel playlist|Music/CC0Music/handel/handel-largo-in-g-from-the-opera-xerxes-remix-111004.mp3
Bach playlist|Music/CC0Music/Bach/chamber-music-bach-flute-sonata-8-4-114161.mp3
Bach playlist|Music/CC0Music/Bach/chamber-music-bach-viola-da-gamba-sonata-no1-in-g-major-1-114166.mp3
Bach playlist|Music/CC0Music/Bach/johan-sebastian-bach-sonata-largo-e-dolce-remix-111997.mp3
Music of Beethoven|Music/CC0Music/beethoven-symphony-3-eroica-funeral-march-2nd-mov-remix-7401.mp3
Music of Beethoven|Music/CC0Music/chamber-music-beethoven-clarinet-trio-ef-major-op38-1-114126.mp3
Music of Beethoven|Music/CC0Music/ludwig-van-beethoven-moonlight-sonata-classical-remix-8097.mp3

This represents 3 different playlists named: Handel playlist, Bach playlist, and Music of Beethoven. The Handel playlist only has one song. The Bach and Beethoven playlists each have 3 songs.

Handel's music is in the folder Music/CC0Music/handel/
Bach's is in Music/CC0Music/Bach
And Beethoven's music is in the Music/CC0Music folder.

4.5 Export the playlist to multiple files

If you want a single line script that will generate a separate .m3u file for each playlist you can use the following in the windows command prompt.

for /F "usebackq delims=| tokens=1,*" %i in (`sqlite3 -noheader vlc_media.db "select id_playlist,replace(name,'''','_') as newname from playlist order by id_playlist"`) do sqlite3 vlc_media.db ".once '%i-%j.m3u'" "select path||filename from playlistmediarelation plr inner join media on id_media=media_id join folder on folder_id=id_folder where playlist_id=%i order by position;"

This will create an .m3u file in the current directory for each playlist in the database. The file names will be the internal playlist id (a number) followed by a dash followed by the playlist name from the database. It's done this way because it's possible to have multiple playlists with the same name in the database (e.g. "playlist.m3u"). Note that playlists that already have a .m3u on the end will have this repeated in the created filename (e.g. "31-playlist.m3u.m3u").

5. Create new .m3u files

The paths you store in the .m3u file are relative to the directory where the .m3u is stored. If you created a file named "Music of Bach.m3u" and put it in the Music/CC0Music/Bach folder with the following content it would show up as a playlist in VLC named "Music of Bach.m3u":

chamber-music-bach-flute-sonata-8-4-114161.mp3
chamber-music-bach-viola-da-gamba-sonata-no1-in-g-major-1-114166.mp3
johan-sebastian-bach-sonata-largo-e-dolce-remix-111997.mp3
      If you wanted to store the same playlist in the Music folder you would need to include the CC0Music/Bach path on the front of each line like this:

      CC0Music/Bach/chamber-music-bach-flute-sonata-8-4-114161.mp3
      CC0Music/Bach/chamber-music-bach-viola-da-gamba-sonata-no1-in-g-major-1-114166.mp3
      CC0Music/Bach/johan-sebastian-bach-sonata-largo-e-dolce-remix-111997.mp3

      Note that FORWARD SLASHES ('/') are required. NOT!!! backslashes as path separators.

      6. Check your work

      Look at each of the generated .m3u files and make sure there aren't any odd path's in the list. If you had a file that was on a different device it might show up with a file:///storage/emulated/... prefix. Make sure you either store the song in that directory or that you edit the path if you moved the song on your new phone.

      7. Upload and test

      Once you store these .m3u files on the new Android device, rescan your media in VLC and it should find them. Dragging down on the VLC playlist view will refresh the view as will clicking on the hamburger menu (top right) and selecting "refresh".

      Hopefully this helps someone down the road figure out how to export the database and turn it into .m3u files that you can import on another device.

      Resources

      Here are some of the resources I used to compile this info:

      Posted by at
      Labels: , , ,
      Subscribe to: Posts (Atom)