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

NewJune 發表於 2022-01-26
Excel SQL

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

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

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

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

 

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

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

 

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

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

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

 

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

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

 

 

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

 

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

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

 

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

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

 

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

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

 

 

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

我們可以先從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$] ,等待你的永遠是錯誤提示。

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

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

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