Convert EXCEL sheets to MySql / MariaDB with links to music

Hi folks
I'm trying to create a decent Music Database

Basically rather than messing around with zillions of files / tracks / playlists etc I want to use one of the Free database systems (MySQL / MariaDB - both similar and work on Windows).

I have a load of spreadsheets -- what I want basically to start testing (can add other fields later) is for Album title, file / track name and path where the link will be able to link directly to the music track and application will play the file

Using this type of database I can then create my own queries if I want specific music etc

Once you get 1 TB or more of music data using traditional libraries / playlists etc doesn't really cut it any more

For the spreadsheets I used Mp3tag to save as csv, imported into excel where the ';' character is used as mp3tags delimiter and now I want to import these spreadsheets into MySQL -- not sure though how to handle the binary objects (the music files)

Here's a typical spreadsheet I want to import into the DB.



Any ideas anyone

From the sample spreadsheet shown above -- just a small test to start with say 3 or 4 fields in the DB would be fine -- I can expand later -- what I need is the best way of handling the binary files - MariaDB or MySQL are so similar - either will do.

I don't want proprietary systems - most music type libraries - especially for consumer stuff usually break quite often and have problems if they expand over several disks or you swap disks around. MySQL / MariaDB are open source and robust so no probs there.

Any DBA's out there -- probably simple - 90 secs work for you gurus. !!


Cheers
jimbo

I can help. I do this all the time.
But I'm away for the weekend attending a wedding in Texas.
I can try to get back to you on Monday.

How to import CSV file to MySQL table - Stack Overflow

This example calls out four columns.

Replace these tags with the proper values for your situation:

Code:

  <database>   name of the database  <table>      name of the table
  <file>       name of the data file
  <col1>       name of the first column in the table
  <col2>       name of the second column in the table
  <col3>       name of the third column in the table
  <col4>       name of the fourth column in the table

I like to use tab-delimited data files, and I use Linux-style line endings (line feed only).
So I use these lines in the script:

Code:

  FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
  LINES TERMINATED BY '\r\n' STARTING BY ''

If you use comma-delimited data files with fields enclosed in quotes you'll need to make changes.
If you use Windows-style line endings (carriage return and line feed) you'll need to make changes.
I think this will work:

Code:

  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n' STARTING BY ''

Here's the script to be used with the mysql.exe program:

Code:

  use <database>;
  --  LOAD DATA LOCAL INFILE '<file>'
  REPLACE  INTO TABLE <database>.<table>
  CHARACTER SET 'binary'
  FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
  LINES TERMINATED BY '\r\n' STARTING BY ''
  (
  <col1>,
  <col2>,
  <col3>,
  <col4>
  );

Hi Jimbo, you could also consider using SQLite, it is an Open Source one file database it is very powerful and prevents you all the "enterprise class" administration payload.

https://sqlite.org/index.html