It's a great tool... helped me find a ton of missing / incorrect metadata too.
Hi Moviefan... Many thanks for putting in the export tables function. Have just used it and wrote some vba to import all the csv's into one spreadhsheet.
There are some tables missing from the exported csv files:
cloudsync_files
library_section_permissions
library_timeline_entries
media_item_settings
media_metadata_mappings
plugin_permissions
schema_migrations
stream_types
synced_ancestor_items
synced_library_sections
synced_metadata_items
synced_play_queue_generators
synchronization_files
view_settings
Do you have to know what the table names are - or can you import whatever tables exist in the database?
Also some files are zero length. They should have the header line
These were zero length files for me
media_part_settings.csv
metadata_item_accounts.csv
metadata_relations.csv
I was using Winamp and exporting the playlist to the same music folder so I didn't get absolute paths (therefore it was creating empty lists). Just saving the list to another drive made the m3u with absolute paths and now everything works perfect! Thanks for your hard work!!!
@sa2000 - I left those out on purpose. Except for the schema migration table, the rest are not normally used and are blank. Or at least they are blank for me. i do not sync stuff so maybe some of the sync tables might hold data. But those are all advanced stuff that I do not think people really need. I can make the script run on all tables, but I thought it was not needed. I cannot think of what use having that data would be. Let me know if you disagree.
I use a query to generate the data for the tables. If the query returns 0 data, the file comes out as 0 too. No headers. I will need to look into how I can generate a file with just the headers.
@victhorb - I forgot about that trick to force absolute paths. I am glad you figured it out.
I left those out on purpose. Except for the schema migration table, the rest are not normally used and are blank. Or at least they are blank for me. i do not sync stuff so maybe some of the sync tables might hold data. But those are all advanced stuff that I do not think people really need. I can make the script run on all tables, but I thought it was not needed. I cannot think of what use having that data would be. Let me know if you disagree.
I would expect it to export all tables as there would be a risk of missing out one that suddenly becomes important and would need then to wait for a change to the tool. I would rather the decision as to what table's contents are looked into is left to the user rather than the tool. So would prefer an autoamatic export all tables
I use a query to generate the data for the tables. If the query returns 0 data, the file comes out as 0 too. No headers. I will need to look into how I can generate a file with just the headers.
That is not as important - happy to leave it as is. I had to code round it in vba as the QueryTables.Add gave runtime errors (memory) when processing the zero length files.
PS: Managed to get all tables into excel - at the moment I have a problem importing the metadata_items table. Getting wrapround on the long text fields - i think to do with the linefeed characters. Will experiment with different delimiter characters. I am using Data / Get_External_Data / From Text. Need to use this interface to make sure UTF8 65001 is preserved and foreign characters are not lost.
PS: Managed to get all tables into excel - at the moment I have a problem importing the metadata_items table. Getting wrapround on the long text fields - i think to do with the linefeed characters. Will experiment with different delimiter characters. I am using Data / Get_External_Data / From Text. Need to use this interface to make sure UTF8 65001 is preserved and foreign characters are not lost.
With regards to my problem with the linefeed characters issue (in for example the 'Summary' field text in metadata_items as documented on this web page, is this something that would be easier to resolve at time of export or do i need to look into how to resolve after your export. At this moment the linefeeds in the text is causing a new record to be created. What would be needed is that any field that has linefeeds within it to be edited before export so that the linefeed characters are replaced with some other text (eg ) which would, be obvious to the reader in excel
Ok. I’m see about adding all the tables. As far as the line feed, the output is straight from sqlite. I do not capture the output before it is written to the file. I could capture the results first but the script has a memory limit so large tables might run into this and end up not creating an output at all. I can try to bypass this limit by splitting the output into chunks then combining them back. Let me see what i can do.
Thank you - The linefeeds issue at this moment is much more important than the other - although exporting all tables makes your code easier !!
Would you make a copy of the table or just each column one at a time looking for linefeeds ? So buffer would be for a whole column unless you split up in a loop - testing carefully for the oddment at the end
Edit 1:
When processing columns be careful with this one - which appears to be binary sequence of bytes rather than number or text. Must not edit it.
Table: play_queues
Column: generator_ids
Edit 2:
You could make the linefeed substitution sequence a user configurable option - defaulting to say
Do we need to worry about carriage return or just linefeed.
Edit 3:
Examining the metadata_items.csv file, the characters are hex(0a) ie just newline characters
A thought on folder name for export
At the moment it is db_export_yyyymmdd_hhmmss where the date and time is current date and time
Could i suggest that folder name has the last modified date and time of the database being exported (and to avoid overwriting previous exports - also have current date and time)
So something like
db_yyyymmdd-hhmm_export_yyyymmdd-hhmmss
where yyyymmdd-hhmm relates to the database file and yyyymmdd-hhmmss is current date and time
What do you think?
And any news on the linefeeds issue in the long text fields?
Thankis
I can make the folder you want but that is a long name. I’ll give since choices so you can pick his you like it. I have not looked into the lf issue. Can you provide me a sample csv that has this?
I can make the folder you want but that is a long name. I'll give since choices so you can pick his you like it. I have not looked into the lf issue. Can you provide me a sample csv that has this?
yes it is long. but if you preset it to be a value based on the modified date and time of the db file and give me an option to change it that would be good. That would allow me to add a suffix eg a, b, c etc if i export the same database more than once. But you would need to allow for overwriting existing csv's / folder or prompt yes/no / yes to all / no to all
I think the db date and time is more appropriate than current date and time but would need to then manage repeated export of the db file
Re linefeed problem I have uploaded csv file to dropbox and sent you link
Great. I will work on it later tonight. I already got it to include all tables. The lf should be easy. Not sure how to handle that blob issue. Cant get binary data through sql so that csv is not going to be useful as an exported table.
yes it is long. but if you preset it to be a value based on the modified date and time of the db file and give me an option to change it that would be good. That would allow me to add a suffix eg a, b, c etc if i export the same database more than once. But you would need to allow for overwriting existing csv's / folder or prompt yes/no / yes to all / no to all
I think the db date and time is more appropriate than current date and time but would need to then manage repeated export of the db file
Re linefeed problem I have uploaded csv file to dropbox and sent you link
LF issue solved. Export folder, I now give you a few choices. I want to add the ability to manually enter a folder name, but your idea of using the autogenerated name then adding a suffix is not going to work. The folder name does not exist until the routine starts so I cannot provide a name for you to just add a suffix. The best I can do is allow you to change the name if the folder already exists so it does not overwrite the existing folder. Let me know if that will work and I can add that to the next version.
Blob issue, just ignored for now.
New version can be found in Post #1.
Thank you so much - it has made things for me so much easier now and coupled with simple vba routine to open all csvs in one spreadsheet - thank you.
I will try over the next few days
apologies if its already covered. but which file do I edit to aim the mortscript to the correct database folder as mine is not on my C drive?
cheers
Settings.ini
No need to modify the settings manually. Under the first option, Database Maintenance, there is an option to switch to a different database to manipulate. This gets put into the settings.ini file so it will default to it next time as well.
Modified settings.ini
Downloaded SQLITE3.exe
Aimed it at that.
Now got to a menu
Choose option 1. [database maintenance]
Nothing happens.
What do I do next?
You may have missed the step where you need right click on the mortscript.exe file and run as administrator to register the extension with windows.
You are indeed correct. After I posted I did this and it worked.
Sorry for being a bit slow. Long day and the clocks changing didn’t help!
cheers! Off for a play 
