Home > Article > Backend Development > Twenty lines of statements to convert from Excel to mysql_PHP tutorial
Twenty lines of statements to convert from Excel to mysql_PHP tutorial
- WBOYOriginal
- 2016-07-21 16:02:13880browse
Since I have a certain understanding of PHP and MySQL, I have been eyeing the huge Excel database in the company and want to secretly "share" this confidential business data. While thinking about how to get it, I also considered how to convert it into a MySQL database. , I went to many PHP site forums to ask experts for advice, but there was no result. One day, I suddenly imagined, why not try this method? The result is really good, everything is OK. Now I will fully share my experience with everyone (maybe you have a better method)
1. First introduce the structure of this Excel database, and name it E.xls, the fields are 4 (do the same for 40), the field names are a, b, c, d, and then decompose jm.xls:
1) Select all records in field a and select "Copy"
2) Create a new Excel database file, paste the copied data in a blank field (usually A), make sure other fields are blank, and save it as a.xls
3) Save a.xls as a.txt( The format is "Text file (tab delimited)")
4) The data of the other three fields in E.xls continues to generate b.txt, c.txt and d.txt according to steps 1 to 3.
2. After completing the above steps, you have completed most of the work. The following is done by php and mysql. It is very simple:
1) To put the data in mysql, you must create a mysql database file Named dbname (must be consistent with dbname in PHP code), including table tbname (must be consistent with tbname in PHP code), 4 fields inc char(100), adds char(100), pri char(100), tel char(100), the size must be adjusted according to the field size in Excel, otherwise some values may be lost.
2) The most important thing is to write the php code. The code is as follows:
------txt2mysql.php---------
$inc =file("a.txt");
$adds=file("b.txt");
$pri=file("c.txt");
$tel=file("d .txt");
$i=0;
mysql_connect();
while (strlen($inc[$i])>0)
{
$sql="insert into tbname values ('$inc[$i]','$adds[$i]','$pri[$i]','$tel[$i]')";
$do=mysql_db_query( "dbname",$sql);
$i=$i+1;
echo '
';
}
$s="select * from tbname";
$gg=mysql_db_query("dbname",$s);
$n=mysql_num_rows($gg);
mysql_close();
echo '
';
echo "Total added ".$n." records";
?>
Note: a.txt, b.txt, c.txt, d.txt and txt2mysql.php must be in the same directory