今天将没怎么用过的SQL SERVER的数据导入功能摸了下,写个日志,留个纪念。
问题描述:某客户单位员工需要在3天左右录入将近3000条员工档案,手头有EXCEL表格一份,但是该EXCEL恶心之处在于,只有一列数据,员工编号、姓名、身份证号等全都在一个单元格里,并且所有数据是用空格分开的,更恶心的是有两个数据的列之间的空格数量并不是太一致。
解决过程:一开始的想法是先导入数据库里,然后用语句将这些数据以空格为划分拆成几列插入到新表中,但是这么做有几个问题,一是作为员工编码的数字是流水码,也就造成各个空格的起始位置并不一样,于是想使用某个能返回空格的位置的函数,利用它来确定想要的数据的起始位置,但是一来怎么也想不起来那个函数谁哪个,谷歌百度了半天也没找着该函数,而且,我还不确定有没有能返回第二个乃至第三个空格的位置的函数,即使有,我还得处理那些个空格数量与其他列不一致的数据,想了半天没想出好的方法来……求赐教
于是,想还是通过文本数据导入的方法来进行数据的导入,但是这时候我做了件比较2的事情,我先把excel中列导入到了数据库中,然后再利用replace函数将一些空格替换成逗号再行导出,这样生成的文本形成无比多的逗号……于是跟这些逗号做了无比纠缠的斗争……其实如果想做的话,直接在excel查找替换就可以了……真是人不犯二枉少年啊。当然,逗号最终还是被打败了,但是在SQL SERVER2008中导入数据时,老是提示说,截取的数据长度超过我创建的表格列定义的数据长度,我一气之下把所有的列数据类型全都定义成text,依然不行……多次试验无解,此时,使用的还是逗号做为分隔符。
折腾了许久,于是又换SQL SERVER2000来搞,此时,使用的是直接从EXCEL复制出来的带空格的数据,发现,2000是支持用空格做分隔符的,并且,在使用空格做分隔符时,不过多了几个值为null的列而已,wtf……
总之很顺利就把数据导入了……
又编了几个语句,顺利导入了数据,如下
select '000000000000000'+convert(varchar,convert(int,infor)+10000) as ccode,
'130'+convert(varchar,(convert(int,infor)+1000)) as cno,
convert(nchar(10),[2]) as cname ,
case len(convert(nchar(30),[3])) when 18 then
(case substring(convert(nchar(30),[3]),17,1)%2
when 0 then 1
else 2
end)
else
(case substring(convert(nchar(30),[3]),14,1)%2
when 0 then 1
else 2
end)
end as sexno,
13 as cboo,'13.09' as dept,
convert(nchar(30),[3]) as cardno,'02' as cardtype,
'01' as empstatus,
'130'+convert(varchar,(convert(int,infor)+1000)) as empno,
1 as c_status
from infor --合同信息取数据
select '000000000000000'+convert(varchar,convert(int,infor)+10000) as ccode,
'130'+convert(varchar,(convert(int,infor)+1000)) as cno,
convert(nchar(10),[2]) as cname ,
case len(convert(nchar(30),[3])) when 18 then
(case substring(convert(nchar(30),[3]),17,1)%2
when 0 then 1
else 2
end)
else
(case substring(convert(nchar(30),[3]),14,1)%2
when 0 then 1
else 2
end)
end as sexno,
13 as cboo,'13.09' as dept,
convert(nchar(30),[3]) as cardno,'02' as cardtype,
'01' as empstatus,
'130'+convert(varchar,(convert(int,infor)+1000)) as empno,
1 as c_status
from infor into tmp from pubs.dbo.infor--临时表数据插入
insert into hr_epm_main(ccode,cno,cname,sexno,cboo,dept,cardno,cardtype,empstatus,empno,c_status)
select ccode,cno,cname,sexno,cboo,dept,cardno,cardtype,empstatus,empno,c_status from tmp where cardno not in (select cardno from hr_epm_main)--数据插入主表
drop table tmp--删除临时表
方法和语句都比较初级,要是有高手有更加简单可靠的方法,还望不吝赐教~