Automating DB management
Posted by:
DAVID HALL
Date: November 07, 2022 01:28PM
I don't know much about MySQL but I'm a tech guy so I'm trying to help a friend out.
He has a DB that he updates daily via CSV imports, right now it's mostly a manual effort. The CSV looks like the example below just 100,000s of rows of data.
customerID,State,Class
1,TN,A 2.76
2,AL,C 3.14
I need to split that Class column into 2 values
customerID,State,ClassLetter,ClassNumber
1,TN,A,2.76
2,AL,C,3.14
I got the code worked out to split the column
SELECT left(CLASS, locate(' ', CLASS)) as classLetter,
RIGHT(CLASS, LENGTH(Class) - locate(' ', CLASS)) as classNumber,
What I'm not sure of is how we can make this somewhat automated. The CSV is updated and downloaded daily. It's a terrible process but he has no effect on changing it in the short term so we're trying to turn lemons into lemonade here. :)
I have searched through this forum and haven't come across anything that seemed to make sense to me.
Any thought to help a guy out would be greatly appreciated.