How to Export dB to a Excel file

[pwrmac7100]

Long story short - I lost my entire music library (all the mp3 files). I am trying to rebuild my library from various backups and iPods. However, I am trying to get an export from the dB to give me a list of all the music I had before I lost it. Is there an easy to way to get a list out of the database? Any help would be appreciated.

[Jägs]

Do you have Subsonic still running? If so, you can sign in and change "index.view" to "db.view" in your URL:

Code: Select all

http://localhost:4040/db.view

You should see a page called "Database query" with a text field. Here, you can input SQL to query your Subsonic database. For example, if you want to get a list of all of your songs, you can run the following query:

Code: Select all

select path from media_file

IMPORTANT: if your collection is large, this may time out or generate an "out of memory" error. Others may have suggestions on how to get around the first issue—I've tried to use limit/offset and Subsonic seems to ignore this—but I found that with the memory issue, if you up your MAX MEMORY value to a GB (1024) or higher, it may work (it did with my largish >250,000 song collection).

[krauthead]

Depending on how big your music collection is, you could limit the result you do get back like follows:

Code: Select all

select top 1000 id,path,type from media_file order by id

You can increase/decrease "top 1000" as you like, so you can try and check what would be the maximum until your subsonic website times out.

After you found the sweet-spot you are confident with using and the server still works properly, you may use the highest ID from the result you got back to get the next set of result: (replace 123456 with the highest ID from your last result)

Code: Select all

select top 1000 id,path,type from media_file where id > 123456 order by id

Repeat this until you got everything back from your Subsonic database.

Once that is done you could order everything by Path in Excel to probably make it easier to read.

Best of Luck you are able to retrieve everything necessary