Disclaimer, this not looking for HOW (yet), but asking which method is safer and/or more efficient. This is possibly subjective.
I can see 2 processes when reading an excel file and feeding it into a MySQL database. Either:
- Convert whole file into one Insert statement
- Insert one row at a time
I thought about #1, and can see it working, but I also have a step after this: I need to normalize (separate into multiple connected tables) the content.
If I insert one row at a time, I can weave the normalization within the insertion as a stored procedure.
If I insert as a giant sql file, I just need to clean up afterwards cells of the same values and columns.
While my tables aren’t millions of rows, I still am just one person, so any way to make it easier for me would be appreciated.
So, which sounds better? Clean up a giant file? or Loop the file’s rows one at a time?