PHP CSV导入数据库
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); ?>
