Reshaping csv data

Say I have a csv file with following data format:

ID, Name, Gender, Q1
1, ABC, Male, "A1;A2"
2, ACB, Male, "A2;A3"
3, BAC, Female, "A1"

I would like to transform it into following format so that my data analysis tool can process it properly:

ID, Name, Gender, Questions, Responses
1, ABC, Male, Q1, A1
1, ABC, Male, Q1, A2
2, ACB, Male, Q1, A2
2, ACB, Male, Q1, A3
3, BAC, Female, Q1, A1

Using Text-to-Columns feature I can easily separate Q1 column into different columns, but I am stuck after that. Anyone had experience reshaping such data?

(data is collected via google form, unfortunately google store multiple choice question responses in such format, so not much I can do on data collection.)

EDITED: I have posted a question with more details on [StackOverflow].(http://stackoverflow.com/questions/16139313/transforming-csv-data-for-analysis-and-visualization)

Intriguing problem: is it better asked on StackOverflow? It looks like a Perl problem, or maybe one of those Vi(m) guys will say “14 keystrokes to solve”. But it doesn’t look especially like a LibreOffice issue. IMO, of course. YMMV, and someone may come up with genius solution. [Also, would be interested to know: how many responses could Q1 (etc.) have? more than two?]

I have posted on superuser, so far no response, probably should have cross-post to SO instead… I cannot guarantee a question will have only N<3 responses (but likely N<10).

@ROSt53, for unknown reason I cannot comment directly under your answer, I will post it here for now: regarding breaking “A1;A2”, as it may have more than 2 responses, there is no easy way to use left/mid/right function to break down responses then repeat row. as for LimeSurvey, I will check it out.

@bitinn - If I understand your problem correct. I would open the CSV file in Calc, save it as an ods-file and then work on it. At the end save it again as a CSV if needed.

Q1 position - looks in your example like a no-brainer. just add column and file it with Q1

Breaking the “A1:A2” string pattern, I would use =left(…), = mid(…) and =right(…) formulas and combine it with a macro to repeat the lines you need.

You also can consider to look at LimeSurvey (FOSSW) and see if your data set can be imported as it is and export it to Excel.

@bitinn - No problem with your answer higher up.

If LimeSurvey isn’t the solution of your problem, than it would be helpful to provide access to a sample file. This enables a better assessment of you data structure an hopefully leads quickly to solution.

I gave you some karma thus you should be able to upload a file. If ask for more karma and someone else will help.

Not exactly a LibreOffice solution, but I manage to do it with google spreadsheet and google app script. Since that’s my data collection platform, I am ok with it.

See this SO post for solution: http://stackoverflow.com/questions/16139313/transforming-csv-data-for-analysis-and-visualization, basically it’s a simple script that iterate and produce a new sheet that met my requirement.

Maybe someone can find a way to do the same scriping in LibreOffice?

(Also thanks, @ROSt53 for suggesting alternatives ^_^)