PHP CSV导入数据库

bei

　　duDaoRuConfig.php页面



<?php
$db_name="wordpress";//如需更改数据库配置在此更改即可
$conn = mysql_connect("localhost", "root", "root");
mysql_select_db($db_name, $conn);
mysql_query("set names 'UTF-8'");

//解析csv文件，返回二维数组，第一维是一共有多少行csv数据，第二维是键名为csv列名，值为当前行当前列的csv数据值
function input_csv($csv_file) {
$result_arr = array ();
$i = 0;
while ($data_line = fgetcsv($csv_file, 10000)) {
if($i == 0){
$GLOBALS['csv_key_name_arr'] = $data_line;
$i++;
continue;
}
foreach($GLOBALS['csv_key_name_arr'] as $csv_key_num=>$csv_key_name){
$result_arr[$i][$csv_key_name] = $data_line[$csv_key_num];
}
$i++;
}
return $result_arr;
}
?>
<script type="text/javascript" src="jquery-1.8.2.js"></script>
　　doDaoRu.php



<form action="doDaoRu2.php" method="post" enctype="multipart/form-data">
<input type="file" name="csv_file" size="50" maxlength="100000" /><br/>
<input type="submit" value="submit"/>
</form>
　　doDaoRu2.php



<?php
include_once("duDaoRuConfig.php");
$dir = "./upload/";
if (is_dir($dir) == false) {
mkdir($dir, 0777);//在页面目录下要新建upload文件夹用来保存上传csv文件
}
//1，存储csv文件
$csv_filename = $_FILES["csv_file"]["name"];
move_uploaded_file($_FILES["csv_file"]["tmp_name"], "./upload/" . $_FILES["csv_file"]["name"]);
//2，获取所有表名
$selAllTableName_str = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '$db_name'";
$allTableName_que = mysql_query($selAllTableName_str);
//3，获取csv文件数据的所有列名
$csv_key_name_arr = array();
//4，以csv列名为键名获取csv所有数据
$csv_file = fopen('upload/'.$csv_filename, 'r');
$result_arr = input_csv($csv_file);
fclose($csv_file);
?>


<form action="doDaoRu3.php" method="post">
列名:
<select class="table_name_sel" name="table_name_sel">
<option> </option>
<?php
while($tableName_row = mysql_fetch_array($allTableName_que)){//可选择所有表名
?>
<option><?php echo $tableName_row['table_name']?></option>
<?php
}
?>
</select>
<br/>
<br/>
<br/>
<?php
foreach($csv_key_name_arr as $csv_key_name)//罗列csv所有列名，并选择要导入到的对应表名，或不导入该csv列
    {
?>
<span>
<input type="hidden" name="<?php echo $csv_key_name;?>" class="csv_key_name_hid" value=""/><?php echo $csv_key_name;?>
<select class='table_column_name_sel'>
<option> </option>
</select>
</span>
<?php
}
?>
<input type="hidden" name="csv_filename_hid" value="<?php echo $csv_filename;?>"/>
<input type="submit" value="submit"/>
</form>

<script type="text/javascript">
$(".table_column_name_sel").change(function(){//当为csv列名选择对应表列名时，为该csv隐藏域值赋选择的表列名
$(this).parent().find("input").val($(this).val());
})
$(".table_name_sel").change(function(){
$(".csv_key_name_hid").val("");
var tableName = $(this).val();
var ajaxAddUrl = "doDaoRuAjax.php";
//window.location = ajaxAddUrl+"?tableName="+tableName;
$.post(ajaxAddUrl,{'tableName':tableName},function(jieShou){
$(".table_column_name_sel option").remove();
$(".table_column_name_sel").append("<option> </option>");
$.each(jieShou,function(i,n){
$(".table_column_name_sel").append("<option>"+n+"</option>");
})
},"json");
});
</script>
　　doDaoRu3.php



<?php
include_once("duDaoRuConfig.php");

$csv_filename = $_POST['csv_filename_hid'];
$tableName = $_POST['table_name_sel'];
//保存需要保存的csv数据的列与表列的关联
$table_real_column_name_arr = array();
//3，保存csv文件数据的所有列名
$csv_key_name_arr = array();
//获取csv所有列名及解析数据
$csv_file = fopen('upload/'.$csv_filename, 'r');
$result_arr = input_csv($csv_file);
//csv数据条数（已扣除第一行列名）
$result_arr_len = count($result_arr);
//将需要保存的csv列与表的列关联起来
foreach($csv_key_name_arr as $csv_key_name){
if($_POST[$csv_key_name]){//判断前页该csv列已赋表列名
$table_real_column_name_arr[$csv_key_name] = $_POST[$csv_key_name];
}
}
//使用批插入，拼凑所有需插入的数据到一个长字符串的sql语句中
$all_insert_data_value_str = "";
for ($i = 1; $i <= $result_arr_len; $i++) { //循环获取各字段值
$csv_line_data_value = "";
$j = 1;
foreach($table_real_column_name_arr as $csv_real_key_name=>$table_real_column_name){
if($j == count($table_real_column_name_arr)){
$csv_line_data_value .= " ' ".$result_arr[$i][$csv_real_key_name]."' ";
}else{
$csv_line_data_value .= " '".$result_arr[$i][$csv_real_key_name]."', ";
}
$j++;
}
$all_insert_data_value_str .= " ($csv_line_data_value) ,";
}
$all_insert_data_value_str = substr($all_insert_data_value_str,0,-1); //去掉最后一个逗号

//拼凑所有需插入值的字段名到一个长字符串的sql语句中
$all_insert_column_name_str = "";
$i = 1;
foreach($table_real_column_name_arr as $csv_real_key_name=>$table_real_column_name){
if($i == count($table_real_column_name_arr)){
$all_insert_column_name_str .= " $table_real_column_name ";
}else{
$all_insert_column_name_str .= " $table_real_column_name , ";
}
$i++;
}
//问题，1文本中如有回车换行会导致字符串中有转义字符导入失败，2中文乱码
//执行批插入，csv导入完成
$query = mysql_query("insert into $tableName ($all_insert_column_name_str) values $all_insert_data_value_str");//批量插入数据表中
fclose($csv_file);
if($query){
echo '导入成功！';
}else{
echo '导入失败！';
}
?>
　　doDaoRuAjax.php



<?php
include_once("duDaoRuConfig.php");

$talbeName = $_POST['tableName'];
//查询该表所有列
$selAllColumns_str = "SHOW COLUMNS FROM $talbeName";
$allColumnsName_que = mysql_query($selAllColumns_str);
$allColumnsName_arr = array();
$i = 0;
while($allColumnsName_row = mysql_fetch_array($allColumnsName_que)){
$allColumnsName_arr[$i] = $allColumnsName_row['Field'];
$i++;
}
ob_end_clean();
echo json_encode($allColumnsName_arr);
?>