最近做的工作涉及到把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);