We often encounter using php to import text files to mysql, but how should we import them to sqlserver? I will share with you the operation method below, and I feel that the efficiency is not bad, we recommend it to you here. CSV, SQL, and TXT supported in my tests
In fact, other formats are acceptable, but I cannot use them.
The speed is almost 10 thousand per second.
<? Phpset_time_limit (0); ignore_user_abort (true); include ('sgk. php'); echo"
";?> <? Phpfunction 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 determines the 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" I wipe, what do you want to do? "; 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"]. "already exists";} else {move_uploaded_file ($ _ FILES ["file"] ["tmp_name"], "uploadhehe /". $ _ FILES ["file"] ["name"]); echo"
Start Automatic file processing
"; $ 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);} // process records not inserted into the database 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 are imported, time consumed {$ time} seconds ";}} else {ec Ho "Upload failed! ";}} Else {header (" Location: login. php ") ;}?>
The above is all the content of this article. I hope you will like it.