Excel VBA中寫SQL，這些問題你一定為此頭痛過

NewJune發表於2022-01-26
ExcelSQL

　　上篇博文【在Excel VBA中寫SQL，是一種什麼體驗】中，小爬簡單講了下如何在VBA中把excel檔案當資料來源，去寫熟悉的SQL語句，進行快速的資料分析。

本篇裡，小爬會重點講講這其中有哪些常見的坑，我們又該如何快速解決。

一、資料來源excel檔案中有大量合併單元格，非一般的二維表

這類資料來源由於格式不規範，不適合使用SQL來處理，建議通過VBA cells(rowIndex,columnIndex).value的方式去訪問單元格。

 

二、資料來源對應表的抬頭不在excel首行

 

 　　此時，我們可以指定資料區域行列的範圍。由於我們無法提前知道表格的行數，我們不妨假定一個很大的整數作為表格資料的行邊界，最後在sql where子句中剔除空行的資料。

比如上圖中，資料分佈在B至I列，第四行才是真正的欄位名，程式碼示例如下：

SELECT *  FROM [Sheet1$B4:$I1000] AS a WHERE a.物料號 IS NOT NULL

 

三、資料來源表部分欄位名為空，如何取數

 

 

　　上圖中，如何取到J列的值呢？SQL中，如果預設某列無欄位名，卻存在具體資料，那麼當我麼Select某張表時，會自動包含這些列並後臺分配列名（Fn),比如J列（第10列），其列名可以用F10來指代，F代表Field（欄位）。

 

四、部分資料來源欄位名中有特殊符號，該如何處理

 

　　我們可以用"[  ]"將對應欄位名括起來，避免特殊符號對SQL產生影響,如下所示：

Sql = "SELECT 公司程式碼,總帳帳目,取數人,交易日期,'',[收/付方名稱],文字,本位幣金額,未達型別 FROM [Sheet1$]"

 

五、方法四不奏效時，該如何處理

 

 

　　有的時候，對於一些特殊欄位名，我們加上"[  ]" 來引用，發現後臺報錯，提示無效欄位名？這時候又該怎麼辦？

我們可以先從recordset中讀出所有欄位名，看sql 引擎後臺是如何解析欄位名的，再來引用它。

Set RS = CreateObject("ADODB.RecordSet")
RS.Open Sql, CONN, 1, 1 'CONN ,指的是某個connection物件
For i = 0 To RS.Fields.Count - 1
    Debug.Print  RS(i).Name
Next

比如上圖中，F列（Tot.rpt.pr)，在sql後臺的欄位名是[Tot#rpt#pr]，您要是一根筋地，非得 Select [Tot.rpt.pr] From [Sheet1$] ,等待你的永遠是錯誤提示。

　　以上這五點，便是小爬在實際工作中接觸各類不規範的資料來源表格檔案時，經常碰到的高頻問題。如果您也遇到併為此頭疼過，不妨試試對應的解決方法，一定能讓你有種相見恨晚的感覺！

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

 

相關文章