Search code examples
mysqlregexexcellibreoffice-calc

Connect distinct id's to multiple names having same id

Heey all,

I'm having difficulties with a large set of data. I have one libreoffice calc sheet (excel sheet) with around 13k records. 2 Columns: Artist - Song

I want these into a mysql database so i've created 2 tables: Artists, Songs With a foreign key in Songs to Artists so a Song records needs to have a valid artist_id. I've inserted all the artists correctly being around 5k different artists. This table works for what i want to do with it so that's no problem.

The problem is how would i connect the id of the artists to the song which is in the 2nd column of my sheet. I want this so i'm able to create 12k insert queries and paste them right into phpmyadmin.

Any tips, regexes, other solutions?

EDIT

My excel sheet:

Artists     Songs              Artist (distinct)    Artist_id
Abba        mamma mia          abba                    1
Abba        waterloo           greenday                2
Greenday    american idiot

My output needs to be:

INSERT INTO Songs Values (1, "mamma mia");
INSERT INTO Songs Values (1, "waterloo");
INSERT INTO Songs Values (2, "american idiot");

The list of artists (distinct) and artist_id differs in length of Songs. That's the problem

Solution

  • So your songs table has fields artist_id | song. You could just use a formula to make your INSERT statement (Assuming your data starts in Row 1):

    ="INSERT INTO songs (artist_id, song) VALUES (" & Vlookup(A1, C:D, 2, false) & ",'" & B1 & "');"

    We use a vlookup() formula to grab the artist_id for the artist in A1 (Assuming that your Artist (distinct) | Artist_Id columns are C:D in this same sheet.

    Then just copy that down to row 12000 and you're set.

    In the event that your song in excel has a single quote in it, you could either escape those out, or replace them with nothing.

    Escape:

    ="INSERT INTO songs (artist_id, song) VALUES (" & Vlookup(A1, C:D, 2, false) & ",'" & SUBSTITUTE(B1, "'", "\'") & "');"

    Remove:

    ="INSERT INTO songs (artist_id, song) VALUES (" & Vlookup(A1, C:D, 2, false) & ",'" & SUBSTITUTE(B1, "'", "") & "');"