Skip to content

Instantly share code, notes, and snippets.

@vividsnow
Last active Aug 29, 2015
Embed
What would you like to do?
XLSX to MySQL converter
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)' } } }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment