贊贊小屋

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價

Excel是大部份商業分析所使用軟體,公司系統可能使用像SQL Server這樣的資料庫,兩者如果能互相交流會很方便,本文以VBA網路爬蟲所取得台積電股價為例介紹。

一、台積電近三個股價資料

已經利用VBA程式取得台積電近三年股價資料,這麼寶貴的資料想將它匯入到SQL Server資料庫中,一方面加強安全保護,另一方面也是為了上傳到Power BI Service所準備。想知道如何設計相關VBA程式和如何執行Power BI商業應用,歡迎參加贊贊小屋課程。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第1張

二、Microsoft SQL Server Management Studio

開啟SQL Server管理應用,已經先新增了「股票」資料庫,既然是微軟產品,和熟悉的Excel操作一樣,在「股票」物件上滑鼠右鍵,快捷選單依序執行「工作>匯入資料」。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第2張

三、SQL Server匯入和匯出精靈

開啟匯入出精靈畫面,有用Excel古老樞紐精靈的讀者,對於這個歡迎語應該會覺得備感親切呀,果然是微軟!注意到這裡所謂的「各種常見資料格式」中,有一項就是「試算表」,以微軟角度而言,試算表當然就是Excel囉。

沒問題按下「Next」。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第3張

四、Excel檔案路徑

在「選擇資料來源」視窗中,「資料來源」下拉選擇「Microsoft Excel」,「Excel檔案路徑」右邊按「瀏覽」,Windows資料夾中選好Excel檔案,注意到這裡上傳是一般Excel檔案,沒有特別改為csv、也沒有降級改成舊版的97-2003類型。

不過在「Excel版本」這裡預設的即為「97-2003」,而如果選擇目前一般的Excel檔案,會自動改成「2007-2010」,下拉選擇會發現還有「2016」,但這裡都不要動,保留「2007-2010」就好。雖然贊贊小屋現在使用的Excel有2019也有365,SQL Server也是用最新的2019,但現在不是要Excel,也不是要SQL,而是要Excel to SQL Server,為了避免兩位高手打架(資料衝突),儘量用基本的老一代版本就好(當然也不要太老),因為Excel和SQL Serve一直在改版,著力點都是放在各自擅長的領域,但是對於彼此之間的相容性,很有可能還停留前一兩代的版本,這個本來就不是他們各自開發的重點。

此步驟說明較多,其實是因為贊贊小屋曾經在這失敗了幾次,終於試出一個可行方案,並且從中有些體會和讀者分享。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第4張

五、目標資料庫

在「選擇目的地」這裡,很顯然SQL Server保留了彈性,所以它沒有自動預設為目前開啟的資料庫,因此要手動下拉選擇「SQL Server Native Client 11.0」,而由於贊贊小屋是直接將SQL Server安裝在電腦上,伺服器便是我的電腦,當然「驗證」方式就請Windows系統那邊檢查即可。

目標「資料庫」下拉可以看到SQL Server現有的資料庫清單,在此選擇第二步驟看到的「股票」,沒問題按「Next」。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第5張

六、過濾篩選資料

既然是SQL,最有名是那個查詢SELECT語句,所以這裡提供了兩個選項,其一是整批倒進去,其二是「寫入SQL查詢」,這裡雖然看起來要準備寫程式了,其實作用也就相當於Excel的篩選指令。

如同第一步驟所看到的,贊贊小屋VBA網路爬蟲時已經用程式把資料漂亮清理過了,所以這裡用預設直接灌入資料即可。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第6張

七、匯入確認

這裡會顯示先前設定好的資料來源和匯入目的地,沒問題直接按「Next」。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第7張

八、儲存SSIS封裝

保留預設的「立即執行」即可。這裡看到「SSIS」封裝有點類似Excel建立樞紐時,有很多進階特別的選項,但一開始可以單純一點,純粹匯入資料即可。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第8張

九、完成匯入精靈

最後再看一下SQL Server即將執行程序,其實前面幾個步驟SQL Server已經同步寫好相關指令程序,這裡等於是把那些命令翻譯出來,看完沒有問題即按下「Finish」。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第9張

十、成功執行SQL命令

總共執行11道動作,每一道都是成功的:「已傳送767個資料列」,對照第一步驟的「項目個數:768」,也就是Excel不含標題有767筆台積電股價資料。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第10張

十一、重新整理

回到Microsoft SQL Server Management Studio,看起來在「股票」資料庫中的「資料表」仍然沒變,這其實是還沒有更新的緣故,點選上方工具列的「重新整理」。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第11張

十二、新匯入的資料表

重新整理後果然多了一個「dbo.’2330 台積電$’」資料表,滑鼠右鍵執行「選取前1000個資料列」。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第12張

十三、台積電股價SQL版

同樣是台積電近三年股價,在Excel看和在SQL Server看感覺就不太一樣,好像真的可以開始進行一些大數據分析了。注意到上面有「SELECT、TOP、FROM」等SQL程式語句,可想見跟Excel巨集及Power Query步驟一樣,SQL Server也具有操作流程翻譯包的功能,果然是微軟!

這裡還有一個小細節是上方工具列有一個「DAX」圖標,滑鼠移到它上方會浮現「Analysis Services DAX查詢」,表示SQL Server也能執行Excel和Power BI同樣的Power Pivot DAX函數,從這個脈絡來看,微軟目前開發Excel的路線就是將它SQL化,把一些資料庫的概念程序集成到Excel。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第13張

十四、微軟兼容性工具

Excel和SQL Server雖然都是微軟包山包海的產品之一,但畢竟一個是試算表應用,一個是資料庫應用,兩者資料結構想必有些差異,如同先前第四個步驟所言,不是真的那麼簡單就能匯入匯出。要想直接在SQL Server匯入Excel資料,依贊贊小屋經驗要先安裝微軟的「Microsoft Access Database Engine 2010可轉散發套件」。

下載頁面上寫得蠻清楚的:「方便傳輸 2010 Microsoft Office system 檔案和非 Microsoft Office 應用程式之間的資料。」,剛好就是這篇文章所需要的,這裡還有一個關鍵點,它有兩個下載檔案:「AccessDatabaseEngine.exe」、「AccessDatabaseEngine_X64.exe」,一個是32位元,一個是64位元,雖然是贊贊小屋和大部份人一樣,電腦Windows系統是64位元,安裝的Office Excel也是64位元,但經過實際測試,這個 「2010可轉散發套件」必須使用32位元才能成功匯入資料,親身經驗供讀者參考,原因也許如同本文第四步驟所述。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第14張

十五、Excel檔案類型及延伸屬性

「2010可轉散發套件」下載還有一段話蠻值得注意,如同截圖標籃色文字所述,在設計Excel VBA資料庫相關程式時,由於會用到跨應用連線技術,像這一類的定義語言就要非常講究,差一個字便無法連線,無法開啟大門取得資料。

微軟SQL Server匯入匯出精靈:取得Excel VBA網路爬蟲股價 程式 第15張

Excel、Power Query、Power Pivot、SQL Server

本文有提到微軟開發Excel的路線就是SQL化,所以最近較熱門的Power BI應用,包括Power Query和Power Pivot,如果熟悉SQL概念,會發現其實微軟就是把SQL的東西內化到Excel裡面去,而從這個角度也比較能夠掌握Excel進階學習路線,也許公司不一定會推行Power BI商業分析,但公司通常有SQL資料庫,這兩者殊途同歸,一個是從源頭把關,一個是處理終端所產出的資料。

會計界一直有所謂會計結合資訊科技的思惟,現在大學也有很多會計資訊系,像文章提到的這些東西應該都要有一些概念,贊贊小屋在執行企業專案時,也確實需要將Excel和SQL Server結合,因此也會規劃相關課程,敬請期待!