Use php to import text data to SQLSERVER

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.

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']}', '{$ dat E} '), ";} 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 imported, time consumed {$ time} seconds ";}}} else {echo "Upload Failed! ";}} Else {header (" Location: login. php ") ;}?>

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