XLSX to MySQL converter

xlsx2mysql.pl

use strict; use warnings;
use feature qw'say state';
use File::Basename 'basename';
use Spreadsheet::XLSX;
use DBI;
use IO::Prompt::Tiny 'prompt';
use Getopt::Std 'getopt';

getopt(my $arg = 'hPupfd', my $opts = {qw(h localhost u root)}); # host user pass file ?db_name
my ($host, $port, $user, $pass, $file, $db_name) = @$opts{split'',$arg};

say <<DOC and exit if !$file || ! -e $file;
XLSX to MySQL converter
Usage: perl xlsx2db.pl -f file.xlsx
Parameters:
-h db_host (default: localhost)
-P db_port
-u db_user (default: root)
-p db_pass
-f xlsx filepath
-d db_name (default from xlsx filename)
DOC

$db_name ||= lc(basename($file)) =~ s/\.\w+$//r ;
warn('db name should be alphanumeric - cleaning..') and $db_name =~ s/\W//g if $db_name !~ /^\w+$/;
die "db name cannot be 'mysql' or '*_schema'" if $db_name =~ /^mysql|(information|performance)_schema$/;

my @dbs = map s/^DBI:mysql://r, DBI->data_sources(mysql => { host => $host, port => $port, user => $user, password => $pass });
my $db = DBI->connect("DBI:mysql:database=;host=$host".($port?";port=$port":''), $user, $pass, {qw(RaiseError 1 mysql_enable_utf8 1)}) || die $!;

say "loading $file..";
my $book = Spreadsheet::XLSX->new($file) || die "cant read xlsx file $file";

if (grep $_ eq $db_name, @dbs) {
    say "db $db_name already exists";
    exit 0 if prompt('continue? [y/n]', 'y') ne 'y' }
else {
    say 'creating database '.$db_name;
    $db->do('create database '.$db_name) }
$db->do('use '.$db_name);

say 'creating tables from sheets..';
for my $sh (@{$book->{Worksheet}}) {
    (state $i)++;
    my $table = $sh->{Name};
    map { $sh->{"Max$_"} ||= $sh->{"Min$_"} } qw'Row Col';
    say "skipping empty sheet '$table'" and next if $sh->{MaxRow} <= 1;
    say("bad sheet name '$table', so using 'sheet$i' as table name") and $table = "sheet$i" if $table !~ /^\w+$/;
    printf "processing sheet %d: %s [%d %d %d %d]\n", $i, @{$sh}{qw(Name MinCol MinRow MaxCol MaxRow)};
    my @col_range = $sh->{MinCol} .. $sh->{MaxCol};
    say 'columns: ' . join ', ', my @col_names = map {
        my $n = $sh->{Cells}[0][$_];
        ($n ? $n->Value : '') =~ s/\W+//gr || "column$_" } @col_range;
    my %col2i = map { $col_names[$_], $_ } @col_range;
    say '(re)creating table..';
    $db->do('drop table if exists '.$table);
    my @data = map [map $_ && $_ eq 'NULL' ? undef : $_, map $_ ? $_->{Val} : undef, @{$sh->{Cells}[$_]}], $sh->{MinRow} + 1 .. $sh->{MaxRow};
    $db->do('create table '.$table.' ('.(join ',', map $col_names[$_].' '.guess_type(\@data, $_), @col_range).') engine=myisam charset=utf8');
    my $sth = $db->prepare(sprintf("insert into %s(%s) values(%s)", $table, (join',',@col_names), (join',',('?')x@col_names)));
    say 'filling table..';
    map $sth->execute(@{$_}[0..$#col_range]), @data }
say 'done';

sub guess_type {
    my ($arr, $i) = @_;
    my %t;
    for (grep defined, map $_->[$i], @$arr) {
        if (!exists$t{varchar}) {
            if (!exists$t{float}) {
                if (/^[\d+-\.]+$/) {
                    if (!exists$t{int}) {
                        if (/^[\d+-]+$/) { $t{int} = 1 } else { $t{float} = 1 } } }
                else { $t{varchar} = 1 } } }
        $t{text} = 1 if length > 255;
        last if exists$t{text} }
    return do { if ($t{text}) { 'text' }
    elsif ($t{varchar}) { 'varchar(255)' }
    elsif ($t{float}) { 'float' }
    elsif ($t{int}) { 'int(4)' }
    else { 'varchar(255)' } } }