splitdump.py

#!/usr/bin/env python
# Python version of https://gist.github.com/petdance/a147f8da9d2afde593382321c07dd2d1

import csv
import string

def create_sql():
    return '''
DROP TABLE IF EXISTS response;
DROP TABLE IF EXISTS reasons;
CREATE TABLE response (
    responseid    INTEGER PRIMARY KEY,
    country_code  VARCHAR(2),
    country_name  VARCHAR(100),
    state         VARCHAR(100),
    age           VARCHAR(100),
    gender        VARCHAR(10),
    employment    VARCHAR(100),
    access_method VARCHAR(100),
    description   VARCHAR(100),
    seen_doctor   VARCHAR(100)
);
CREATE TABLE reasons (
    responseid    INTEGER NOT NULL,
    reason        VARCHAR(100)
);
'''.lstrip()

def insert_into( table, id, other ):
    quoted = [ "'{}'".format(i) for i in other ]
    values = [ id ]
    values.extend(quoted)
    joined = ', '.join(values)

    print( 'INSERT INTO {} VALUES ( {} );'.format(table,joined) )


print( create_sql() );

with open( 'dump.csv' ) as csvfile:
    reader = csv.reader( csvfile, delimiter=',' )
    headings = csvfile.readline()
    for row in reader:
        id = row[0]

        # Generate the parent rows.
        response = row[1:4] + row[9:15]
        insert_into( 'response', id, response )

        # Generate any child rows.
        for reason in row[4:9]:
            if len(reason) > 0:
                insert_into( 'reasons', id, [reason] )

        print('')