Mysql – Best way to export excel table into MySQL db

This will generate a CSV per lead_id in order of field_number.

SELECT lead_id, lead_id, GROUP_CONCAT(value)
FROM test_table
GROUP BY lead_id
ORDER BY field_number
INTO OUTFILE '/tmp/result.txt'
  FELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

There are 2 lead_id columns to match your output. MySQL doesn't have ROW_NUMBER() to generate an arbitrary sequence.

I have found that the following works better to order the fields properly:

SELECT lead_id,
    GROUP_CONCAT(value
        ORDER BY field_number)
    FROM exp_wp_rg_lead_detail
    GROUP BY lead_id

I did not look in detail at the data since I belief you can normalize it yourself, but from what I've grasped out of your posting I would say you are doing everything right.

One warning though: Likely you will encounter a lot of existing inconsistencies in the excel sheets. You must fix those in order to import and normalize the data correctly. This constitutes a high initial effort.

The alternative (keep the data in the form it is) instead will rise the maintenance costs of the application.

In the long run I claim that the additional initial costs will be less than the regular addition in maintenance cost.