30万xml数据导入SQL server 数据库的项目总结

因为客户的需求需要将将近30万的xml数据（90M）导入到SQL server数据库，开始我们决定使用ASP技术来实现，因为客户的网站是asp的，没有安装PHP，但是用asp试了一天发现，asp在处理大文件时时力不从心啊，在大文件读写时，会挂掉，而且速度特别慢，所以在耽误了一天时间。

第二天决定使用php来实现，在做php实现时主要运用到了以下几种技术：

  一、文件的预处理（特殊字符处理，str_replace() 字符替换）

  二、文件的读写（file_exists()、file_get_contents()、file_put_contents()）

  三、PHP xml 操作（new DOMDocument() 对象的创建）

  四、php SQLserver数据库连接

  五、字符编码转换（iconv() ）

  六、程序执行日志制作。

  七、windows 计划任务 程序脚本定时执行

<?phpset_time_limit(0);  //不限脚本执行时间if(!file_exists('../xml/amount.xml')) //检查文件是否存在{echo "bu cun zai wemj";exit;}$xmlchar=file_get_contents('../xml/amount.xml');$xmlgood = str_replace('&', '', $xmlchar);$xmlgood = str_replace("<?xml version='1.0' encoding='utf-8'?>\n", '', $xmlgood);$xmlgood ="<?xml version='1.0' encoding='utf-8'?>\n".$xmlgood ;file_put_contents('../xml/amount.xml', $xmlgood);$doc = new DOMDocument(); $doc->load('../xml/amount.xml'); //读取xml文件 $humans = $doc->getElementsByTagName( "vip" ); //取得vip标签的对象数组 $count_num = $humans->length ;echo "yi gong".$count_num."tiao ji lu";//连接数据库$conn = mssql_connect('127.0.0.1,3433','sa','sq_matro') or die("no connect");//mssql_query("set names GBK", $conn);mssql_select_db('sq_matro') or die("no db");$card = mssql_query("SELECT CardNo FROM Qiandu_VipAmount");$cardarr =array();while($row=mssql_fetch_row($card)){$cardarr[]=$row[0];}//对于插入语句，程序合并执行一个字符串执行，减少直接执行数据库查询次数$insql="INSERT INTO Qiandu_VipAmount (CardNo, UserName, TotalAmount, TotalIntergal, ValidIntergal, PreTotalAmount, PreTotalIntergal, Birth) VALUES  ";$upsql="";$num=1;//每次执行的条数  SQL SERVER 2005 每次只能执行1条$j=0;//循环计数器$i=1;//字符计数器$hang=0;          //判断是否是最后一行$gx = 0;        //记录当下循环中更新的条数$start = date('Y:m:d H:s:i',time())."";foreach( $humans as $human ) { $CardNo = $human->getElementsByTagName( "CardNo" ); $CardNo = $CardNo->item(0)->nodeValue; $Name = $human->getElementsByTagName( "Name" ); $Name = $Name->item(0)->nodeValue;$Name = iconv("utf-8","gbk",$Name);//中文字符转码  $TotalAmount = $human->getElementsByTagName( "TotalAmount" ); $TotalAmount = $TotalAmount->item(0)->nodeValue; $TotalIntergal = $human->getElementsByTagName( "TotalIntergal" ); $TotalIntergal = $TotalIntergal->item(0)->nodeValue; $ValidIntergal = $human->getElementsByTagName( "ValidIntergal" ); $ValidIntergal = $ValidIntergal->item(0)->nodeValue; $PreTotalAmount = $human->getElementsByTagName( "PreTotalAmount" ); $PreTotalAmount = $PreTotalAmount->item(0)->nodeValue; $PreTotalIntergal = $human->getElementsByTagName( "PreTotalIntergal" ); $PreTotalIntergal = $PreTotalIntergal->item(0)->nodeValue; $Birthday = $human->getElementsByTagName( "Birthday" ); $Birthday = $Birthday->item(0)->nodeValue; $here_num = $j*$num;$shenyu =$count_num -$here_num ;//获取剩余记录//判断记录是否存在 ，也可以用字符串查找判断if(in_array($CardNo, $cardarr)){   $gx++;echo "update\n";mssql_query("UPDATE Qiandu_VipAmount SET UserName = '$Name',TotalAmount = '$TotalAmount',TotalIntergal = '$TotalIntergal',ValidIntergal = '$ValidIntergal',PreTotalAmount = '$PreTotalAmount',PreTotalIntergal = '$PreTotalIntergal',Birth = '$Birthday' WHERE CardNo = '$CardNo'");}else {if($i==$num || $shenyu==$i){$hang=1;$insql.=" ('$CardNo', '$Name', '$TotalAmount', '$TotalIntergal','$ValidIntergal', '$PreTotalAmount', '$PreTotalIntergal', '$Birthday');";}else{$insql.=" ('$CardNo', '$Name', '$TotalAmount', '$TotalIntergal','$ValidIntergal', '$PreTotalAmount', '$PreTotalIntergal', '$Birthday'),";}}if($shenyu<=$num && $i==$shenyu){if($hang==0){$insql=trim($insql,',').";";}mssql_query($insql);}elseif($i==$num){echo "insert\n".$insql;if($hang==0){$insql=trim($insql,',').";";}if($gx < $num){mssql_query($insql);}$insql="INSERT INTO Qiandu_VipAmount (CardNo, UserName, TotalAmount, TotalIntergal, ValidIntergal, PreTotalAmount, PreTotalIntergal, Birth) VALUES  ";$j++;}if($i==$num){$gx=0;$i=1;$hang=0;}else {$i++;}}$end = date('Y:m:d H:s:i',time())."";$amountxmllog = "start ".$start."~~~~end ".$end."\r\n";file_put_contents('../phpbat/amountxmllog.txt', $amountxmllog, FILE_APPEND);unlink('../xml/amount.xml');?>

 window是计划任务定时执行php

建  .bat   处理文件，然后用计划任务定时执行.bat文件

D:\php\php.exe D:\php\amountxml.php