What techniques do people use to upload data from a flat file into a normalised database? We are planning on using MySQL or MariaDB.
I have a number of large excel spreadsheets that I would like to import into a database. The files are in slightly different formats. We had thought of importing to a temporary table and preforming validation and data transformations on this temporary table. Then coding an sql script to insert the records in the the tables and child tables.
Is there anyway of speeding up or streamlining this process. This is a situation that I am going to have to repeat on a constant basis and I do not have any control over the format that the data is going to be provided in.
Any suggestions would be very much appreciated