PHP implements text data import into SQL SERVER_PHP tutorial-PHP Tutorial-php.cn

PHP implements text data import into SQL SERVER_PHP tutorial

WBOY

Release： 2016-07-13 09:53:54

Original

936 people have browsed it

php implements text data import into SQL SERVER

We often encounter php importing text files into mysql, but how to operate it if it is imported into sqlserver? Let me share with you below After taking a look at my operation method, I feel that the efficiency is pretty good, so I recommend it to everyone.

I tested that it supports CSV, SQL, TXT

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

The speed is almost 10,000 per second

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

set_time_limit(0);

ignore_user_abort(true);

include('sgk.php');

echo "

";

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);//Automatically determine encoding

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"] . "
";

}

else

{

$name=$_FILES["file"]["name"];

$name=iconv('utf-8', 'GB2312//IGNORE', $_FILES['file']['name']);

echo "Upload: " . $_FILES["file"]["name"] . "
";

echo "Size: " . ($_FILES["file"]["size"] / 1048576) . " Mb
";

$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 "
开始自动处理文件
";

$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("rn", $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 "A total of {$total} records were imported, which took {$time} seconds";