Excel VBA中寫SQL，這些問題的方法你一定要牢記

　　小爬之前的文章 【Excel VBA中寫SQL，這些問題你一定為此頭痛過】中詳細講訴了一些常見的VBA 中使用SQL遇到的問題，這裡再補充兩個常見的問題場景及對應的解決方案，希望你們看了後能夠思路開闊些，少走些彎路。

一、數據源Excel文件的工作表有些列的列名相同

　　我們知道，在商業資料庫中創建表時，同一個表名下是不允許創建相同的欄位的，且欄位名要遵從一定規範。但在Excel文件中則無此限制，同一個工作表下，支援多列的列名相同。那麼當我們用SQL來操作這類頭疼的Excel文件時，該如何區分不同欄位呢？

　　假設小爬有這樣一個Excel表（數據樣本通過python的第三方庫faker來生成），【源數據】表中，A列和C列的列名都為【公司】，但其實一個指代【公司名稱】，另一個指代【公司程式碼】，這樣不規範的excel模板例子在現實工作中很常見。我們該如何區分它倆呢？

 

 　　小爬試著先輸出recordSet中所有欄位名，看VBA的sql引擎是如何貼心處理這個問題的，示例程式碼如下：

 1 Sub myQuery()
 2   Dim conn As Object, rs As Object, rs1 As Object, sht1 As Worksheet, sht2 As Worksheet, sql As String
 3   Set conn = CreateObject("ADODB.Connection")
 4   Set rs = CreateObject("ADODB.recordset")
 5   Set sht1 = ThisWorkbook.Sheets("源數據")
 6   Set sht2 = ThisWorkbook.Sheets("結果")
 7   conn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12.0;data source=" & ThisWorkbook.FullName
 8     sql = "SELECT * FROM [源數據$]"
 9   Set rs = conn.Execute(sql)
10   For i = 0 To rs.Fields.Count - 1 '輸出recordset欄位名到【結果】表
11     sht2.Cells(1, i + 1) = rs.Fields(i).Name
12   Next
13   sht2.Cells(2, 1).CopyFromRecordset rs '輸出recordset結果到【結果】表
14   conn.Close
15   Set 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 String

  Set 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).Value

    Debug.Print rs.Fields(2).Value      '表名，對於Excel中的表或（工作表名）後面會自動加一個$
    rs.MoveNext
Loop

　　至於上面的例子中，為啥不每次默認用VBA語法打開某個工作簿，再保存為xls文件，再用CONN來連接，自然是為了改善腳本的性能，畢竟workbooks.open相比較於CONN來連接表格，速度太慢了。

　　

歡迎掃碼關注我的公眾號 獲取更多爬蟲、數據分析的知識！

 

Tags:

VirMach 便宜 VPS

QNews

QNews