Mysql – What’s the easiest way to re-format CSV exports from MySQL into Excel/ flat-file fields

csvexportMySQL

I've just had some database backup problems on a website I run containing some important lead data. Unfortunately, I've now lost one of the key MySQL tables, which means my data has lost its relationship and is now hard to read and manipulate in Excel. Luckily, I did manage to keep 1 table which contains the important values. I now just need to re-format it for Excel to make it easier to work with.

Currently when I export from the DB to CSV the data looks like this:

id,lead_id,field_number,value
1,1,1,Mickey
2,1,2,Mouse
3,2,1,Minnie
4,2,2,Mouse

I would like to re-format this data into the following format:

id,lead_id,field_1,field_2
1,1,Mickey,Mouse
2,2,Minnie Mouse

I hope that makes sense, I wasn't sure how else to explain this question.

Best Answer

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
Related Question