Data export from QUIK via ODBC. How to convert VARCHAR fields to correct ones (DATE, DECIMAL, ENUM) on the fly?

Question

MySQL

I export data from QUIK 8 via ODBC. The problem is that QUIK exports the fields as strings (except for numbers, everything is ok with them), and I need to put them into a compact table with the correct compact fields. How can this be done in the most correct way?
While there is an idea to pour data into the primary table with VARCHAR fields, and put a trigger on it on each INSERT. With each INSERT into the primary table, the trigger will call a procedure that will convert the fields to the required ones and INSERT into the target compact table.
How smart is this approach?

Answer 1

The approach is completely illiterate. MySQL, when inserting into a table (as with most operations), automatically converts the data to the desired type.
And the DATA and ENUM fields are always passed as strings, only DATA must be in the correct format, 'YYYY-MM-DD'.

Answer 2

In the query text, ANY data is represented as its string representation. Why, the SQL query itself is a string literal.
Therefore, you should take care of only the correct representation. Format. If the data is a number, then the decimal separator must be a period. If this is a date or a string, there must be framing quotes, and the date must be in a format understandable by the current DBMS (preferably YYYY-MM-DD - this format is understood by all DBMS), and all characters that are service characters must be escaped in the string. Etc.
If the uploaded data does not correspond to this format, then in the request to add, you can not use the value directly (and then fool around in the procedure / trigger), but, using built-in functions, convert the data to the correct type. For example, if you need to insert a number into the field, and the upload uses a comma as a separator, this will be

INSERT ... VALUES ( ... , CAST(REPLACE(@value, ',', '.') AS DOUBLE), ...