如何把excel數據導入到mysql數據庫中
最近做的工作涉及到把excel數據導入到mysql數據庫中, 看來一些mysqlimport, phpmyadmin命令, 但是不怎麼會用. 就決定自己寫腳本解決.
先把excel數據文件保存成csv格式的文件, 然後寫了下面的簡單perl腳本倒入mysql數據庫中.
需要注意用你的mysql數據庫表的結構替換掉. 運行腳本並且把csv格式的文件做為參數即可.
#!/usr/bin/perl
www.2cto.com
# perl program: insert_mysql_data.pl
# function: insert mysql data from csv file
# usage : insert_mysql_data.pl csv_file
# Author: [email protected]
# 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
{ www.2cto.com
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>) { www.2cto.com
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);
