最近做的工作涉及到把excel数据导入到mysql数据库中, 看来一些mysqlimport, phpmyadmin命令, 但是不怎么会用. 就决定自己写脚本解决.

先把excel数据文件保存成csv格式的文件, 然后写了下面的简单perl脚本倒入mysql数据库中.

需要注意用你的mysql数据库表的结构替换掉. 运行脚本并且把csv格式的文件做为参数即可.

#!/usr/bin/perl 

# perl program: insert_mysql_data.pl
# function: insert mysql data from csv file
# usage : insert_mysql_data.pl csv_file
# Author: shenxiangfeng@360.cn
# Date: 2012-9-20

use strict;
use warnings;
use DBI;

# define the global vars
my $CHECK_DEBUG=1;
my $CSV_FILE;
my $lines = 0;
# ------------------------
#  signal handle function
# ------------------------
local $SIG{'INT'} = \&__int_handler;
sub __int_handler
{
    my @int = @_;
    if ($int[0]) {
        # Called on a user interrupt
        die "\nInterrupted by \"ctrl+c\"\n";
    }
}


# get the parameter
my $parameter = shift;
if (! -e $parameter) {
    die("program parameter is not right. \nUsage: md5_check.pl package_name_or_directory\n");
}
elsif (-f $parameter) {
# connect mysql database
my $dbh = DBI->connect("DBI:mysql:database=tuiguang;host=localhost", "root", "", {'RaiseError' => 1});
$dbh->do("SET NAMES 'utf8'");
    # parameter is csv file
    open($CSV_FILE,"<","$parameter") or die "Can't open csv file $parameter.\n";
while (<$CSV_FILE>) {
        my $line=$_;
        chomp($line);
my @parts=split(/,/,$line);
print "@parts will insert.\n" if ($CHECK_DEBUG);
my $rows = $dbh->do("INSERT INTO AccountInfo (ID, Name, PassWord, Property, ChanType, Status, Discount, ChanBelong, DownloadUrl) 
                    values ('$parts[0]', '$parts[1]', '$parts[2]', $parts[3], $parts[4], $parts[5], $parts[6], $parts[7], '$parts[8]')");
print "$rows row(s) affected.\n" if ($CHECK_DEBUG);
$lines ++;
}
# clean up
    close($CSV_FILE);
$dbh->disconnect();
}
# post process 
print "Successfully process the $lines lines files. NO error found.\n";
exit(0);