3

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.

2
  • Your output id columns don't make sense based on the matching input columns? And how many fields do you expect per grouping id? Just 2 based on field_id?
    – gbn
    Oct 4, 2011 at 0:11
  • There are more than 2 fields in the data set, and some leads have different amounts of overall fields.
    – jnthnclrk
    Oct 4, 2011 at 10:01

3 Answers 3

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
3
  • Did you mean to reference lead_id twice in the first line?
    – jnthnclrk
    Oct 7, 2011 at 14:08
  • Also, I don't think ORDER BY field_number is working, because the concatenated values have seemingly random field orders across records.
    – jnthnclrk
    Oct 7, 2011 at 14:10
  • 1
    I edited your answer to show how I fixed the ordering as the code didn't render very well in this comment box.
    – jnthnclrk
    Oct 7, 2011 at 14:27
1

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

1
  • Sorry, I should have said that there are more than 2 fields to deal with and that the overall amount of fields does vary.
    – jnthnclrk
    Oct 4, 2011 at 10:03
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.