What's the easiest way to re-format CSV exports from MySQL into Excel/ flat-file fields?

Question

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.

Answer 1

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

Answer 2

I would do this in Excel - much easier!

Give the following assumptions...

1. ALWAYS two items within the "field_number" field
2. information sorted by "id,lead_id,field_number"
3. Rename cells D1 and E1 as "field_1" and "field_2"

Then use the following formulae;

1. In cell E2; "=D2 & ' ' & D3"
2. In cell E3; "" (leave blank)

Then copy cells "E2:E3", and fill down through the rest of your data.

You should be left with something like this...

id,lead_id,field_number,field_1,field_2
1,1,1,Mickey,Mickey Mouse
2,1,2,Mouse,
3,2,1,Minnie,Minnie Mouse
4,2,2,Mouse

You then need to "copy and paste-special-values" the entire sheet.

Then filter the data and delete all those with a "field_number" = 2.

Then delete the column "field_number".

Hey-presto, all done, and should take no more than the length of time it took me to write up this answer!!! :)

Dave

Answer 3

I'm guessing that your question is about output format???

If so, the answer is to properly format your SELECT syntax and use the 'INTO OUTFILE' modifier.

CSV Example from page:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;