Convert EXCEL sheets to MySql / MariaDB with links to music

  1. jimbo45's Avatar
    Posts : 10,484
    Windows / Linux : Arch Linux
       #1

    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.

    Convert EXCEL sheets to MySql / MariaDB with links to music-mfile.png

    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
      My Computer


  2. Posts : 1,285
    X
       #2

    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.
      My Computer

  3.   My Computer


  4. Posts : 1,285
    X
       #4

    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>
      );
      My Computer


  5. Posts : 668
    Win 10 pro
       #5

    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
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd
All times are GMT -5. The time now is 07:41.
Find Us




Windows 10 Forums