How to import Excel data to a MySQL database

Recent work involves importing Excel data into the MySQL database. It seems that some mysqlimport and phpMyAdmin commands are not easy to use. I decided to write my own scripts.

First, save the Excel data file as a CSV file, and then write the following simple Perl script into the MySQL database.

Note that the structure of your MySQL database table should be replaced. Run the script and use the CSV format file as the parameter.

#! /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 files. No error found. \ n ";
Exit (0 );