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 website 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, there are 4 fields (Do the same for 40), the fields are named a, b, c, d, and then decompose jm.xls:
1) Select all the records in field a and select "Copy"
2) Create a new Excel database file, paste the copied data in a blank field (usually A), ensure that other fields are blank, and save it as a.xls
3) Save a.xls as a.txt (the format is "Text File (Tabulation)" character separated)")
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( It must be consistent with the dbname in the PHP code), including the table tbname (which must be consistent with the tbname in the PHP code), 4 fields inc char(100), adds char(100), pri char(100), tel char(100) , the size should 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 ".$n." records added";
?>
Note: a.txt, b.txt, c.txt, d.txt and txt2mysql.php must be in the same directory
The above has introduced the 20-line statement of excel2003 official download free full version to realize the conversion from Excel to mysql, including the content of excel2003 official download free full version. I hope it will be helpful to friends who are interested in PHP tutorials.