在Excel 2013利用Power Query以動態SQL句式來查詢MSSQL資料
大家好, 近來有個需求是想利用儲存在Microsoft SQL資料庫的資料來製作一些報表
所以想到用MS Excel 2013裡的Power Query來連接SQL 資料庫抓取所需資料
把冷冰冰的資料以圖表顯示出來, 確實可以讓人對收集了的資料有更深入的了解.
我在研究Excel 這個功能時忍不住要讚嘆它能夠發揮的無窮可能性.
這篇文章只分兩個部份
1. 以固定的SQL Statement來匯入資料
2. 以擁有變量的SQL Statement來匯入資料
1. 以固定的SQL Statement來匯入資料
Power Query其實早在Excel 2010就已經存在, 甚至在Excel 2003或更早的時間都已經能夠接入SQL資料庫
但時至今日, 配合Excel 2013的Data Model概念又讓它更進一步,
最重要的是, 我們可以用強大的原生SQL Query Statement來查詢資料,
而且查詢的SQL Statement更可以根據Excel來的Cell value來的值動態地變更
只要是Excel 2010/2013 Professional Plus版本 (差不多是最貴的那一個版本) 就能夠安裝這個插件
由於有版本的限制, 所以在投產環境購買就特別要分銷商注意了.
http://www.microsoft.com/en-hk/download/details.aspx?id=39379
除了MSSQL, Power Query基本上能夠實現在大部份來源抓取表單式資訊導入到Excel裡面
就資料庫來源的種類就已經稱得上是包羅萬有了, 但這次我以Microsoft SQL Server Database來說明
按下From SQL Server Database, 會出現資料庫連結表單
在這裡需要把資料庫機名, 資料庫名稱輸入, 而擴展SQL Statement後
可以在空白地方以SQL的Select句式來查詢資料
只要在MS SQL Management Studio裡建入已經成功查詢到的, 都可以直接在這裡貼上使用
甚麼join table, convert變量, 各種運算子, if else句式通通無問題可以利用到這裡
有個小心得是每個Query出來的SQL Field都盡量以 as xxx 的方式重新在Excel表達出來
因為Excel最後會以這個名義來定義每個資料欄的名稱
及後根據資料庫的登入方式, 以Windows 帳號或Database帳號儲存登入資訊即可
抓到參考資料後就可以在右邊給這套資料一個名稱, 方便日後辨識,
這個Query Editor只會匯入首百多筆的資料, 如果資料很多, 必須先匯入才能全部顯示出來
按左上角的匯入, 就可以放入Excel利用了
匯入到新的試算表, 或已存在的試算表作為Excel的資料分析來源
導入後可以見到Excel完整地查詢到(例子有2萬多筆)的訊息, 左上角見到我們命名的資料名字
這樣我們就可以利用各重Excel的資料分析手段, 例如圖表, Pivot Table, 甚至新的Bing Map, Power Pivot功能來整理資訊了
這個Power Query使用方式很直覺簡單, 其實拿上手大概已經懂得使用了,
所以我們之後要來點複雜一點的
2. 以擁有變量的SQL Statement來匯入資料
如果我們不想抓取資料庫所有的筆數資料, 我們會以SQL
Select 一些東西 from 資料庫 where 一些條件 的方式來表達
想像這份報表是月結形式去提供給老闆看公司表現的, 所以 Where的部份可能就是限制為抓取某個時間內的資訊
我們可不能簡單的說, 啊老闆, 你就修改一下那條SQL Statement, 把1月改成2月就可以更新這個月的資料了
這樣在IT人的感覺好像很專業, 但在資料使用者的感覺就太複雜
但假如我們能夠製作一張表格, 只要老闆填入想查看的日期範圍, 再按更新
Excel就能夠依他輸入的資料重新向資料庫動態地抓取想要的資料, 那真的是帥得很
要Power Query內的SQL Statement隨著Excel裡某些資料改變已改變, 我們可以這樣做
先要製作一個新的表格, 用來給老闆填入想要的日期,
這個表格麻, 你可以自行發揮你的藝術天份來美化它…讓它看起來專業一點
我的SQL Statement例子要求有日期的範圍, 所以老闆要填入兩個日期
我們要製作兩個表格, 一個標題叫FromTheDate, 另一個叫ToTheDate
寫好後像下圖一樣先圈起A1:A2的方格, 按Table
把它變成一個Table, 而且定義第一行為標題
按OK後, Excel會跳轉到這個Table的設計頁面, 我們在左上紅圈位置命名這個表格, 例子叫FromTheDate
這個值我們日後會用在抓取日期數值時使用
同樣的把ToTheDate也變成Table, 給它命命名做ToTheDate
先以SQL Statement能接納的日期格式填入一個參考數值,
一會在建立日期選擇器時就方便測試有沒有建立正確
現在我們又要選擇Power Query, 這次我們的資料來源是From Table
建立這個Query的名稱叫FromTheDateQuery, 選擇Load To…
這次我們選Load To… 的時候, 我們只建立一個資料連結, 而且要勾選”Add this data to the Data Model”
因為我們不需要再在某個新的試算表裡表達一次這筆資料, 只要這個資料有一個對映的變量(FromTheDate)儲存起來就可以了
按Load後, 我們會見到Excel多了一個只有一筆查詢, 資料來源叫FromTheDate
我們可以試試改變FromTheDate的值, Workbook Queries裡的FromTheDateQuery不即會立即跟隨改變
需要在Query裡按Refresh
它才會重新載入新的值
同樣的方式我們把ToTheDate也變成一個資料來源
兩個日期變量已經準備好了, 現在我們來看看怎樣把它放入SQL Statement裡
在已查詢到的資料Sales Data叢集上右擊, 選Edit
打開這個叢集的Query Editor, 這次我們用進階的Query編寫器, 選Advanced Editor
我們會見到類似程式碼的東西, 在let的部份是宣告這個Query變量的地方,
下面會見到source 這個variable已經儲存了我們提供的SQL Statement
這裡其實是以M expression這種編程語言來編寫的, 不懂沒緊要, 我們只學需要的部份
這個編寫器還貼心地即時查看語法有沒有Syntex error 給予提示,
在Let的部份我們加入以下4行程式碼
vFromDate = Excel.CurrentWorkbook(){[Name="FromTheDate"]}[Content], (把FromTheDate的內容放入vFromDate這個陣列變量中)
FromDate = vFromDate{0}[FromTheDate], (把vFromDate裡第一順位vFromDate{0}的值放入FromDate這個變量中)
vToDate = Excel.CurrentWorkbook(){[Name="ToTheDate"]}[Content],
ToDate = vToDate{0}[ToTheDate],
大家可能會想像得到, 如果FromTheDate這個表單不只有兩個行數,
我們可以用vFromDate{1}, vFromDate{2} 來表達第2,3…等等其他在同表單裡的值然後使用
現在我們要把FromDate和ToDate這兩個變量放入Source這個SQL Statement裡面, 我們找到有關日期那一個部份
我們首先要看清楚標點符號, 假如SQL Statement是這樣寫的話
在Advanced Editor裡會變成這個樣子, 如果數值是一個常數, 只要用’xxx’括起來就可以了,
我通常#(if)#(tab), 即換行符號的分隔讓自己看得方便一點
如果我們要再這個句子裡放入一個變量, 就要變成以下的樣子
where #(lf)#(tab)saledate.date>= '"&Text.From(FromDate)&"' #(lf)#(tab)and saledate.date < '"&Text.From(ToDate)&"'#(lf)#(tab)
解拆出來就是以 ' 先把SQLStatement暫時切段, 以 " 開始定義變量, 以&&包起變量的名稱, 完成後又以' 來宣佈繼續寫SQL Statement
而因為我們想把FromDate的值以文字方式表達, 所以又以Text.From()這個函數來抓取當中的文字出來 (因為這句SQL Statement最後的屬性是一串String)
假如你的變量儲存的是一個數字, 那自然就不用加入Text.From(), 直接以&Var&使用就可以了
按OK後Excel會提示由於安全性, 這樣需要更改權限才可以進行查詢
現在你會見到Excel用以查詢的SQL Statement是甚麼樣子的,
正確的話, 從FromTheDate和ToTheDate來的值會被放置到適當的位置
第一次查詢時Excel會提示這樣有可能會把組織的重要資訊外洩出去,
你可以把資料適用範圍調整到Organization或Public的層面, 我選Organization, 按儲存, 以後就不會再問了
現在我們見到查詢出來的資料已經成功按照表格內容而改變了
日後, 只要按DATA頁中的Refresh All,
Excel就會把FromTheDate, ToTheDate的值插進SQL Statement裡, 再把匯入的筆數更新其他圖表及Pivot Table
這樣資料使用者就可以以Excel動態地拿到資料庫中的資訊, 實現了很多BI 分析情境了
