splitdump.pl

#!/usr/bin/env perl

use warnings;
use strict;
use 5.010;

use Text::CSV_XS;

my $csv = Text::CSV_XS->new;


say create_sql();
open( my $fh, '<', 'dump.csv' ) or die $!;
my $headings = $csv->getline($fh);
while ( my $row = $csv->getline($fh) ) {
    my @row = @{$row};
    s/^\s+// for @row;
    s/\s+$// for @row;

    my $id       = $row[0];
    my @response = @row[1..3, 9..14];
    my @reasons  = grep { defined($_) && ($_ ne '') } @row[4..8];

    insert_into( 'response', $id, @response );
    insert_into( 'reasons',  $id, $_ ) for @reasons;
    say '';
}
close $fh;


sub insert_into {
    my $table  = shift;
    my @values = @_;

    my $id = shift @values;

    my @quoted = ( $id, map { "'$_'" } @values );
    say "INSERT INTO $table VALUES ( " . join( ', ', @quoted ) . ' );';

    return;
}

sub create_sql {
    return <<'SQL';
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)
);
SQL
}