Excel|Excel VBA中写SQL,这些问题的方法你一定要牢记
小爬之前的文章 【Excel VBA中写SQL,这些问题你一定为此头痛过】中详细讲诉了一些常见的VBA 中使用SQL遇到的问题,这里再补充两个常见的问题场景及对应的解决方案,希望你们看了后能够思路开阔些,少走些弯路。
一、数据源Excel文件的工作表有些列的列名相同
我们知道,在商业数据库中创建表时,同一个表名下是不允许创建相同的字段的,且字段名要遵从一定规范。但在Excel文件中则无此限制,同一个工作表下,支持多列的列名相同。那么当我们用SQL来操作这类头疼的Excel文件时,该如何区分不同字段呢?
假设小爬有这样一个Excel表(数据样本通过python的第三方库faker来生成),【源数据】表中,A列和C列的列名都为【公司】,但其实一个指代【公司名称】,另一个指代【公司代码】,这样不规范的excel模板例子在现实工作中很常见。我们该如何区分它俩呢?
文章图片
小爬试着先输出recordSet中所有字段名,看VBA的sql引擎是如何贴心处理这个问题的,示例代码如下:
1 Sub myQuery() 2Dim conn As Object, rs As Object, rs1 As Object, sht1 As Worksheet, sht2 As Worksheet, sql As String 3Set conn = CreateObject("ADODB.Connection") 4Set rs = CreateObject("ADODB.recordset") 5Set sht1 = ThisWorkbook.Sheets("源数据") 6Set sht2 = ThisWorkbook.Sheets("结果") 7conn.Open "provider=Microsoft.ACE.OLEDB.12.0; extended properties=excel 12.0; data source=" & ThisWorkbook.FullName 8sql = "SELECT * FROM [源数据$]" 9Set rs = conn.Execute(sql) 10For i = 0 To rs.Fields.Count - 1 '输出recordset字段名到【结果】表 11sht2.Cells(1, i + 1) = rs.Fields(i).Name 12Next 13sht2.Cells(2, 1).CopyFromRecordset rs '输出recordset结果到【结果】表 14conn.Close 15Set conn = Nothing 16 17 End Sub
输出的结果如下图所示:
文章图片
可以看到,重名后的列名被sql解析成字段名后,默认跟上阿拉伯数字1,2,3……知道了sql 引擎的解析规则,我们就可以直接根据解析后的列名 如【公司】【公司1】来操作不同的字段了,没输出所有字段名前就可以做到心中有数。
二、Excel“表格”不是真正的表格文件格式
有的时候,我们从ERP系统导出的报表Excel文件,虽然是xls(xlsx)后缀,可出于种种原因,他们并非真正的Excel表格格式,可能底层依然是txt文件,小爬在工作中就没少遇到过这种奇葩问题。
解决思路如下:先利用sql的方法获取当前数据库的所有表名,如果表名是乱码,如("?????"),则该Excel文件可能底层是txt文件,至少不是规范格式的Excel文件。此时,我们可以用VBA原生的workbooks.open方法来显式打开该工作簿,自动保存,然后用SQL引擎来重新连接该工作簿即可。示例代码如下:
Const adSchemaTables = 20 '这句很重要,一定要提前定义adSchemaTables常量的值 Sub myQuery() Dim conn As Object, rs As Object, rs1 As Object, sht1 As Worksheet, sht2 As Worksheet, sql As String, sourceFileName As StringSet conn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.recordset") sourceFileName = ThisWorkbook.Path & "\数据源\" & "测试.xls" conn.Open "provider=Microsoft.ACE.OLEDB.12.0; extended properties=excel 12.0; data source=" & sourceFileName Set rs = conn.openschema(adSchemaTables) TableName = rs.Fields(2).Value If TableName <> "源数据" Then '假定当工作簿格式规范时,工作表名为【源数据】 conn.Close Set wb = Workbooks.Open(sourceFileName) wb.Save wb.Close conn.Open "provider=Microsoft.Ace.oledb.12.0; Extended Properties=Excel 12.0; data source=" & sourceFileName Set rs = conn.openschema(adSchemaTables) TableName = rs.Fields(2).Value End If End Sub
这里面包含两个技巧:
1、当小爬用wb.save时,Excel会自动将不规范的xls文件(本质是txt)保存为规范的xls文件;
2、利用conn.openschema(adSchemaTables)输出该数据库下所有的表名,代码如下:
CONN.Open "provider=Microsoft.Ace.oledb.12.0; Extended Properties=Excel 12.0; data source=" & sourceFullName Set rs = CONN.openschema(adSchemaTables) Do While Not rs.EOF tableName = rs.Fields(2).ValueDebug.Print rs.Fields(2).Value'表名,对于Excel中的表或(工作表名)后面会自动加一个$ rs.MoveNext Loop
至于上面的例子中,为啥不每次默认用VBA语法打开某个工作簿,再保存为xls文件,再用CONN来连接,自然是为了改善脚本的性能,毕竟workbooks.open相比较于CONN来连接表格,速度太慢了。
【Excel|Excel VBA中写SQL,这些问题的方法你一定要牢记】欢迎扫码关注我的公众号 获取更多爬虫、数据分析的知识!
文章图片
推荐阅读
- Java实现Excel转PDF的两种方法详解
- python|python selenium中Excel数据维护指南
- 如何利用Python处理excel表格中的数据
- python使用xlrd模块读取excel的方法实例
- 前端实现word、excel、pdf、ppt、mp4、图片、文本等文件的预览
- PHP实现读取Excel文件的记录(二)
- 演示(怎么批量把excel表格通讯录导入苹果iphone手机)
- 分析(如何将excel表格电话号码导入手机通讯录)
- springboot中Excel文件下载踩坑大全
- Python实现PDF扫描件生成DOCX或EXCEL功能