bitsCN.com
如何把excel数据导入到mysql数据库中 最近做的工作涉及到把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 varsmy $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 parametermy $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 databasemy $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);
bitsCN.com

下载本文