还有一个文本文件是英文的数据字典这样的:
po_ca_show[0]='Technology/Internet';
po_ca_value[0]='0100';
po_detail_show[0]=new Array();
po_detail_value[0]=new Array();
po_detail_show[0][0]='(All)Technology/Internet';
po_detail_value[0][0]='0100';
po_detail_show[0][1]='CTO/VP Engineering';
po_detail_value[0][1]='0101';
po_detail_show[0][2]='Technical Director/Manager';
po_detail_value[0][2]='0102';
po_detail_show[0][3]='IT Manager';
po_detail_value[0][3]='0103';
po_detail_show[0][4]='IT Supervisor';
po_ca_show[1]='Sales';
po_ca_value[1]='0200';
po_detail_show[1]=new Array();
po_detail_value[1]=new Array();
po_detail_show[1][0]='(All)Sales';
po_detail_value[1][0]='0200';
po_detail_show[1][1]='Sales Director';
po_detail_value[1][1]='0201';
po_detail_show[1][2]='Sales Manager';
po_detail_value[1][2]='0202';
po_detail_show[1][3]='Regional Sales Manager';
po_detail_value[1][3]='0226';
po_detail_show[1][4]='Sales Account Manager';
po_detail_value[1][4]='0208';
po_ca_show[0]='Technology/Internet';
po_ca_value[0]='0100';
po_detail_show[0]=new Array();
po_detail_value[0]=new Array();
po_detail_show[0][0]='(All)Technology/Internet';
po_detail_value[0][0]='0100';
po_detail_show[0][1]='CTO/VP Engineering';
po_detail_value[0][1]='0101';
po_detail_show[0][2]='Technical Director/Manager';
po_detail_value[0][2]='0102';
po_detail_show[0][3]='IT Manager';
po_detail_value[0][3]='0103';
po_detail_show[0][4]='IT Supervisor';
po_ca_show[1]='Sales';
po_ca_value[1]='0200';
po_detail_show[1]=new Array();
po_detail_value[1]=new Array();
po_detail_show[1][0]='(All)Sales';
po_detail_value[1][0]='0200';
po_detail_show[1][1]='Sales Director';
po_detail_value[1][1]='0201';
po_detail_show[1][2]='Sales Manager';
po_detail_value[1][2]='0202';
po_detail_show[1][3]='Regional Sales Manager';
po_detail_value[1][3]='0226';
po_detail_show[1][4]='Sales Account Manager';
po_detail_value[1][4]='0208';
//读取并规格化数据
$s = preg_replace("/(^|[\r\n]+)/","\\1\\$",trim($s));
$s = preg_replace("/new Array\b/","array",$s);
if($s[strlen($s)-1] == "$")
$s = substr($s,0,-2);
//生成工作数组
eval($s);
$sql = "";//构造sql串
foreach($po_ca_show as $k=>$v) {
$n = $po_ca_value[$k];
$sql .= (empty($sql)?"":",")."('$n','$v')";
foreach($po_detail_show[$k] as $k1=>$v1) {
$n = $po_detail_value[$k][$k1];
$sql .= ",('$n','$v1')";
}
}
$sql = "insert into tbl_name (jobid,chinesename) values $sql";
echo $sql; //测试入库后,再处理英文的,方法与上面的类似
只是sql语句改做形如
update tbl_name set englishname='Sales' where jobid='0200'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[newcategory_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[newcategory_temp]
GOCREATE TABLE [dbo].[newcategory_temp] (
[categoryName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[categoryCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[newcategoryName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[newcategoryCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[isfinish] [smallint] NULL ,
[flags] [smallint] NULL
) ON [PRIMARY]
GOdeclare @filename varchar(300)
set @filename = 'd:\www.in2english.com\category.txt';
exec('BULK INSERT melli.[dbo].[newcategory_temp]
FROM '''+@filename+'''
WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR=''\n'',
FIRE_TRIGGERS
)'
)
GOupdate melli.[dbo].[cmsunits]
set melli.[dbo].[cmsunits].categoryid = '-lost-'
where melli.[dbo].[cmsunits].[categoryid] not in
(select melli.[dbo].[newcategory_temp].[categorycode] from melli.[dbo].[newcategory_temp])
GO
就成了