SqlServer--读取Excel
如何使用Sql读取Excel2003?
具体例子如下:
如何读取下面这个Excel?
此表的路径为:d:\zl\student.xls
其中的工作簿为info
表格式如下:
使用Sql读取如下:
select * from openrowset ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\zl\student.xls', 'select * from [info$]' )--注意项:--1.excel处于关闭状态,即不能处于被打开状态--2.excel文件所处路径及文件名、工作簿的名称不要出现汉字,尽量以英文命名--3.注意'select * from [info$]',工作簿名后的$是必须的.结果:
为什么为出现一行null值呢?还出现了列f5,列f6?
原因是出现了合并单元格,不仅有列方面的合并单元格,还有行方面的合并单元格.
但是,我们仍然可以通过where子句的判断,读取到需要的数据.
如下:
select 姓名,年龄,班级,成绩 as 语文成绩,F5 as 数学成绩,F6 as 英语成绩 from openrowset ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\zl\student.xls', 'select * from [info$]' ) where 姓名 is not null结果:
这正是我们期待的结果.但此方法只能针对这样一个表,如果其它表的格式和这个表类似,但是却略有不同时,该如何处理呢?再写一套sql脚本?
不太现实,因为我们并不仅仅是读取excel,还有其他操作,例如,将excel中的数据进行转换后,再导入sql.
比较好的方法是准备一个excel模版,将数据尽量复制至该模版中,我们只需按照这个模版,写一套可以操作该表的sql语句即可.
设置excel模版如下:
然后将表中数据,复制进该表.
再次读取该表:
select 姓名,年龄,班级,语文成绩,数学成绩,英语成绩 from openrowset ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\zl\student.xls', 'select * from [info$]' )--建议,尽量读取列名
结果:
时会如下错误:
服务器: 消息 7399,级别 16,状态 1,行 1
OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。
OLE DB 错误跟踪[OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: 提供程序未给出有关错误的任何信息。]。
请检查文章开始处提到的注意项.
补充:
读取Excel时,可能会遇到以下问题:
当列中存在数字行和字符串行时,会遇到有一种读出为null的情况,该如何处理呢?
有两种解决方法:
第一种方法:在数字行的数据前加'
第二种方法:在连接字符串中加入:IMEX=1,如下:
select 姓名,年龄,班级,语文成绩,数学成绩,英语成绩 from openrowset ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\zl\student.xls;IMEX=1', 'select * from [info$]' )
但是,第二种方法不是万能的.当前八行数据均为数字或字符串时,后面再有其他类型数据时,仍然读不出来.
引用解释如下:
IMEX是用来告诉驱动程序使用Excel文件的模式,其值有0、1、2三种,分别代表导出、导入、混合模式。当我们设置IMEX=1时将强制混合数据转换为文本,但仅仅这种设置并不可靠,IMEX=1只确保在某列前8行数据至少有一个是文本项的时候才起作用,它只是把查找前8行数据中数据类型占优选择的行为作了略微的改变。例如某列前8行数据全为纯数字,那么它仍然以数字类型作为该列的数据类型,随后行里的含有文本的数据仍然变空。另一个改进的措施是IMEX=1与注册表值TypeGuessRows配合使用,TypeGuessRows 值决定了ISAM 驱动程序从前几条数据采样确定数据类型,默认为“8”。可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的该注册表值来更改采样行数。但是这种改进还是没有根本上解决问题,即使我们把IMEX设为“1”, TypeGuessRows设得再大,例如1000,假设数据表有1001行,某列前1000行全为纯数字,该列的第1001行又是一个文本,ISAM驱动的这种机制还是让这列的数据变成空。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器