ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用SQL语句导入excel数据

用SQL语句导入excel数据

原创 Linux操作系统 作者:iSQlServer 时间:2009-10-22 15:24:38 0 删除 编辑
今天,我的Team Leader让load一些数据到数据库中去,之前这样的事情我也做过。没有遇到过什么错误,但是今天这个excel让我吃了不少苦头。经过我不懈努力,最终解决了所有问题,顺利完成任务。下面我把我遇到的问题写下来和大家探讨一下。
一、问题提出
这个excel大概1W条数据,数据量不是很大,开始导入也很顺利。不到一分钟就完成了,结果我发现有一列数据全部变成了null,并且其他列的数据格式也不正确。然后我就更改了每个列的数据类型,结果导致数据无法导入。郁闷!
二、问题深化
于是我想到用SQL语句去试一下,用下面的语句执行了一下。
SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;imex=1;Database=\\surrey-test\GS\GS_UNpaid.xls',
'SELECT * FROM [Sheet1$]')
结果出现,
OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 不包含表 'Sheet1$'。该表可能不存在,或当前用户没有使用该表的权限。
OLE DB 错误跟踪[Non-interface error: OLE DB provider does not contain the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Sheet1$']。
于是,详细思考了一下。哦,原来我的excel没有放到Server上,放上去之后在此运行,数据查出来了。
三、设法解决
数据查出来之后格式依然不正确,不符合我们的要求,于是开始进行数据格式的转换。开始的时候使用convert和cast把数据转换为float类型,不行。于是再次转换
convert(float,convert(varchar(50),isnull(gs_guid,0))),这次格式对了,但是数据却由于float类型的精度问题而发生了改变,不能满足要求。于是使用
left(cast(cast(convert(float,convert(varchar(20),confirmation_no)) as decimal(20,7)) as varchar(20)),9),结果还是不能让人满意,数据失真了。苦思冥想,终于想到这条cast(cast(confirmation_no as decimal) as varchar),Ok。问题解决,欣喜若狂。
四、检查问题
就在我要Submit的时候,却发现一个致命的问题,所有数据格式正确的同时,竟然有一列数据发生了很大变化,于是认真查找,发现了问题的存在,对于这一列使用
cast(convert(bigint,convert(float,convert(varchar(50),isnull(gs_guid,0))))as varchar),问题终于搞定。
五、问题解决
最后使用
  INSERT INTO temp4
select convert(char(4),car_no) as car_no,convert(datetime,[column name]) as pu_date,
cast(cast([column name] as decimal) as varchar),
--left(cast(cast(convert(float(5),convert(varchar(50),[column name])) as decimal(20,7)) as varchar(20)),10),
convert(decimal(12,2),[column name]),convert(char(4),dr_no),
cast(cast([column name]as decimal) as varchar),
cast(cast([column name] as decimal) as varchar),
--convert(float,convert(varchar(50),isnull([column name],0))),
cast(convert(bigint,convert(float,convert(varchar(50),isnull([column name],0))))as varchar),
cast(cast([column name]as decimal) as varchar),
--left(cast(cast(convert(float,convert(varchar(20),[column name])) as decimal(20,7)) as varchar(20)),10),
--left(cast(cast(convert(float,convert(varchar(20),[column name])) as decimal(20,7)) as varchar(20)),9),
--left(cast(cast( convert(float,convert(varchar(50),isnull([column name],0))) as decimal(20,7)) as varchar(20)),9),
--left(cast(convert(float,convert(char(20),[column name])) as varchar(20)),6),
convert(datetime,[column name]) ,isnull([column name],'')
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;imex=1;Database=\\surrey-test\GS\GS_UNpaid.xls',
'SELECT * FROM [gs_voucher_notpaid$]')
将数据load到Database中去。
六、总结
导入数据虽然是件很简单的事情,但是这里面还是包含了很多知识。比如数据的存储类型,数据库中的一些常用函数,等等。希望,这些经验能够使我在项目中受益,同时也希望各位多多指点。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-617167/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    1319
  • 访问量
    2077627