有时写程序时后台要求把大量数据导入数据库中,比如考试
成绩、电话簿等一般都是存放在excel中的数据,这时
我们可把excel导出成csv文件,然后通过以下程序
即可批量导入数据到数据库中
上传cvs并导入到数据库中,测试成功(部分代码不规范
,如PHP_SELF那里要改写成
上传cvs并导入到数据库中,测试成功(部分代码不规范
$_SERVER
[
"
PHP_SELF
"
] )
PHP代码
<? php
$fname = $_FILES [ ' MyFile ' ][ ' name ' ];
$do = copy ( $_FILES [ ' MyFile ' ][ ' tmp_name ' ] , $fname );
if ( $do )
{
echo " 导入数据成功
" ;
} else {
echo "" ;
}
?>
< form ENCTYPE = " multipart/form-data " ACTION = " <?php echo "" .$PHP_SELF. "" ; ?> " METHOD = " POST " >
导入CVS数据 < input NAME = " MyFile " TYPE = " file " > < input VALUE = " 提交 " TYPE = " submit " >
</ form >
<?
error_reporting ( 0 );
// 导入CSV格式的文件
$connect = mysql_connect ( " localhost " , " a0530093319 " , " 123456 " ) or die ( " could not connect to database " );
mysql_select_db ( " a0530093319 " , $connect ) or die ( mysql_error ());
$fname = $_FILES [ ' MyFile ' ][ ' name ' ];
$handle = fopen ( " $fname " , " r " );
while ( $data = fgetcsv ( $handle , 10000 , " , " ))
{
$q = " insert into test (code,name,date) values ('$data[0]','$data[1]','$data[2]') " ;
mysql_query ( $q ) or die ( mysql_error ());
}
fclose ( $handle );
?>
PHP代码
<? php
$fname = $_FILES [ ' MyFile ' ][ ' name ' ];
$do = copy ( $_FILES [ ' MyFile ' ][ ' tmp_name ' ] , $fname );
if ( $do )
{
echo " 导入数据成功
" ;
} else {
echo "" ;
}
?>
< form ENCTYPE = " multipart/form-data " ACTION = " <?php echo "" .$PHP_SELF. "" ; ?> " METHOD = " POST " >
导入CVS数据 < input NAME = " MyFile " TYPE = " file " > < input VALUE = " 提交 " TYPE = " submit " >
</ form >
<?
error_reporting ( 0 );
// 导入CSV格式的文件
$connect = mysql_connect ( " localhost " , " a0530093319 " , " 123456 " ) or die ( " could not connect to database " );
mysql_select_db ( " a0530093319 " , $connect ) or die ( mysql_error ());
$fname = $_FILES [ ' MyFile ' ][ ' name ' ];
$handle = fopen ( " $fname " , " r " );
while ( $data = fgetcsv ( $handle , 10000 , " , " ))
{
$q = " insert into test (code,name,date) values ('$data[0]','$data[1]','$data[2]') " ;
mysql_query ( $q ) or die ( mysql_error ());
}
fclose ( $handle );
?>
用php将数据库导出成excel,测试完全成功
PHP代码
<?
php
$DB_Server = " localhost " ;
$DB_Username = " a0530093319 " ;
$DB_Password = " 123456 " ;
$DB_DBName = " a0530093319 " ;
$DB_TBLName = " member " ;
$savename = date ( " YmjHis " );
$Connect = @ mysql_connect ( $DB_Server , $DB_Username , $DB_Password )
or die ( " Couldn't connect. " );
mysql_query ( " Set Names 'gb2312' " );
$file_type = " vnd.ms-excel " ;
$file_ending = " xls " ;
header ( " Content-Type: application/$file_type " );
header ( " Content-Disposition: attachment; filename= " . $savename . " .$file_ending " );
header ( " Pragma: no-cache " );
header ( " Expires: 0 " );
$now_date = date ( " Y-m-j H:i:s " );
$title = " 数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date " ;
$sql = " Select * from $DB_TBLName " ;
$ALT_Db = @ mysql_select_db ( $DB_DBName , $Connect )
or die ( " Couldn't select database " );
$result = @ mysql_query ( $sql , $Connect )
or die ( mysql_error ());
echo ( " $title " );
$sep = " " ;
for ( $i = 0 ; $i < mysql_num_fields ( $result ); $i ++ ) {
echo mysql_field_name ( $result , $i ) . " " ;
}
print ( " " );
$i = 0 ;
while ( $row = mysql_fetch_row ( $result )) {
$schema_insert = "" ;
for ( $j = 0 ; $j < mysql_num_fields ( $result ); $j ++ ) {
if ( ! isset ( $row [ $j ]))
$schema_insert .= " NULL " . $sep ;
elseif ( $row [ $j ] != "" )
$schema_insert .= " $row[$j] " . $sep ;
else
$schema_insert .= "" . $sep ;
}
$schema_insert = str_replace ( $sep . " $ " , "" , $schema_insert );
$schema_insert .= " " ;
print ( trim ( $schema_insert ));
print " " ;
$i ++ ;
}
return ( true );
?>
$DB_Server = " localhost " ;
$DB_Username = " a0530093319 " ;
$DB_Password = " 123456 " ;
$DB_DBName = " a0530093319 " ;
$DB_TBLName = " member " ;
$savename = date ( " YmjHis " );
$Connect = @ mysql_connect ( $DB_Server , $DB_Username , $DB_Password )
or die ( " Couldn't connect. " );
mysql_query ( " Set Names 'gb2312' " );
$file_type = " vnd.ms-excel " ;
$file_ending = " xls " ;
header ( " Content-Type: application/$file_type " );
header ( " Content-Disposition: attachment; filename= " . $savename . " .$file_ending " );
header ( " Pragma: no-cache " );
header ( " Expires: 0 " );
$now_date = date ( " Y-m-j H:i:s " );
$title = " 数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date " ;
$sql = " Select * from $DB_TBLName " ;
$ALT_Db = @ mysql_select_db ( $DB_DBName , $Connect )
or die ( " Couldn't select database " );
$result = @ mysql_query ( $sql , $Connect )
or die ( mysql_error ());
echo ( " $title " );
$sep = " " ;
for ( $i = 0 ; $i < mysql_num_fields ( $result ); $i ++ ) {
echo mysql_field_name ( $result , $i ) . " " ;
}
print ( " " );
$i = 0 ;
while ( $row = mysql_fetch_row ( $result )) {
$schema_insert = "" ;
for ( $j = 0 ; $j < mysql_num_fields ( $result ); $j ++ ) {
if ( ! isset ( $row [ $j ]))
$schema_insert .= " NULL " . $sep ;
elseif ( $row [ $j ] != "" )
$schema_insert .= " $row[$j] " . $sep ;
else
$schema_insert .= "" . $sep ;
}
$schema_insert = str_replace ( $sep . " $ " , "" , $schema_insert );
$schema_insert .= " " ;
print ( trim ( $schema_insert ));
print " " ;
$i ++ ;
}
return ( true );
?>