Home >Backend Development >PHP Tutorial >PHP implements text data import into SQL SERVER, _PHP tutorial

PHP implements text data import into SQL SERVER, _PHP tutorial

WBOY
WBOYOriginal
2016-07-13 09:53:28861browse

php implements text data import into SQL SERVER,

I tested that it supports CSV, SQL, TXT

Actually, other formats are also available, but I don’t need them

The speed is almost 10,000 per second

<&#63;php
set_time_limit(0);
ignore_user_abort(true);
include('sgk.php');
echo "<br><br>";
&#63;>
<&#63;php
function array_iconv($data,$output = 'GBK') {
 $encode_arr = array('UTF-8','ASCII','GBK','GB2312','BIG5','JIS','eucjp-win','sjis-win','EUC-JP');
 $encoded = mb_detect_encoding($data, $encode_arr);//自动判断编码
 if (!is_array($data)) {
 return mb_convert_encoding($data, $output, $encoded);
 }
 else {
 foreach ($data as $key=>$val) {
  if(is_array($val)) {
  $data[$key] = array_iconv($val, $input, $output);
  } else {
  $data[$key] = mb_convert_encoding($data, $output, $encoded);
  }
 }
 return $data;
 }
}
function microtime_float(){ 
  return microtime(true); 
} 
if ($_SESSION['id']<>''){
if (@$_POST["submit"]<>""){
if ((($_FILES["file"]["type"] == "text/plain")
|| ($_FILES["file"]["type"] == "application/vnd.ms-excel") || ($_FILES["file"]["type"] == "application/octet-stream")
|| ($_FILES["file"]["type"] == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
&& ($_FILES["file"]["size"] < 198102805))
 {
 if ($_FILES["file"]["error"] > 0)
  {
  echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
  }
 else
  {
  $name=$_FILES["file"]["name"];
  $name=iconv('utf-8', 'GB2312//IGNORE', $_FILES['file']['name']); 
  echo "Upload: " . $_FILES["file"]["name"] . "<br />";
  echo "Size: " . ($_FILES["file"]["size"] / 1048576) . " Mb<br />";
  $exit= pathinfo($_FILES["file"]["name"], PATHINFO_EXTENSION);
  $exit='.' .$exit;
 
    if ($exit==".asp" or $exit==".php" or $exit==".jsp" OR $exit==".aspx"){
  echo "我擦,你想做什么?";
  exit;
  }
  $size=filesize($_FILES["file"]["tmp_name"]);
   $_FILES["file"]["name"]=md5($_FILES["file"]["name"].$size).$exit;
  if (file_exists("uploadhehe/" . $_FILES["file"]["name"]))
   {
   echo $_FILES["file"]["name"] . " 已经存在 ";
   }
  else
   {
   move_uploaded_file($_FILES["file"]["tmp_name"],
   "uploadhehe/" . $_FILES["file"]["name"]);
   echo "<br>开始自动处理文件<br>";
   $begin = microtime_float();
$file="uploadhehe/" . $_FILES["file"]["name"];
$handle = @fopen($file, "r");
//$handle=array_iconv('utf-8','gbk',$handle);
$total=0;
$over=0;
if ($handle) {
  $sql="INSERT INTO [dbo].[own_user_data1] ([Name],[Context],[md5],[data]) VALUES";
  $num=0;
  $_FILES['file']['name']=iconv('utf-8', 'GB2312//IGNORE', $_FILES['file']['name']); 
  while (!feof($handle)) {
    $buffer = fgets($handle, 2000);
    $buffer=str_replace("\n", "", $buffer);
    $line_array=explode("\r\n", $buffer);
    $Context=htmlspecialchars(addslashes($line_array[0]));
    $Context=str_replace("'", ',', $Context);
    $Context=str_replace('"', ',', $Context);
    $Context=array_iconv($Context);
    $date=date('y-m-d h:i:s',time());
    if($num<999){
    $sql.="('{$name}','{$Context}','{$_FILES['file']['name']}','{$date}'),"; 
    }else{
    $sql.="('{$name}','{$Context}','{$_FILES['file']['name']}','{$date}')"; 
    $a=sqlsrv_query($conn,$sql);
    // if( $a === false ) {
   // die( print_r( sqlsrv_errors(), true));
// }
    $num=0;
    $sql="INSERT INTO [dbo].[own_user_data1] ([Name],[Context],[md5],[data]) VALUES";
 
    }   
    $num++; 
    $total++;
 
   }
 } 
fclose($handle);
$sql1="INSERT INTO [dbo].[own_user_info] ([uid],[md5],[date],[name],[size],[gold]) VALUES ('{$_SESSION["id"]}','{$_FILES["file"]["name"]}','{$date}','{$name}','{$total}','1')";
sqlsrv_query($conn,$sql1);
if( $a === false ) {
   die( print_r( sqlsrv_errors(), true));
}
//处理没有插入到数据库的记录
if(substr($sql, strlen($sql)-1,1)==","){
  $sql=substr($sql, 0,strlen($sql)-1);
  sqlsrv_query($conn,$sql);
}
$end = microtime_float();
$time = $end-$begin;
echo "共导入{$total}条记录,耗时{$time}秒";
 
   }
  }
 }
else
 {
 echo "上传失败!";
 
 }
}
}else{
 header("Location: login.php"); 
}
&#63;>

The above is the entire content of this article, I hope you all like it.

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1000263.htmlTechArticlephp implements text data import into SQL SERVER. I tested that it supports CSV, SQL, and TXT. In fact, other formats are also available. It’s just that I don’t use it. The speed is almost 10,000 per second phpset_...
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn