I need to import a spreadsheet that was being viewed with the expand/collapse feature into mysql. As shown in the screenshot, when an item (WMIMCART1 in this case) is expanded, it shows more info related to that item. Naturally, when this spreadsheet was exported as a csv file and then imported into mysql with
LOAD DATA INFILE 'carts.csv' INTO TABLE Carts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
it of course simply imports row for row. I would like to only load the main items (all of which start with either the substring "WM" or "VM") into a table called Carts, and then the rows that follow each main items should be imported in to a table Maintenance.
Each cart row would have the columns (Name,Location,Serial). Each maintenance entry would have the columns (Serial,Action,Date).
Basically when the substring "WM" or "VM" is found, load that row into Carts, and the following rows into Maintenance (combining the action and date into one row) until the substring "WM" or "VM" is found again.
I have a pretty good idea of how to do this in PHP, but would like to know if there is a good way to do this just in sql with string functions, etc.