在 Excel 檔案上執行 SQL 查詢
雖然 Excel 動作可以處理大部分的 Excel 自動化案例,但 SQL 查詢可以更有效率地擷取和處理大量的 Excel 資料。
假設流程必須只修改包含特定值的 Excel 登錄。 若要在不進行 SQL 查詢的情況下實現此功能,您需要迴圈、條件和多個 Excel 動作。
取而代之,您只需使用兩個動作即可透過 SQL 查詢執行此功能:開啟 SQL 連線和執行 SQL 陳述式。
開啟 Excel 檔案的 SQL 連線
執行 SQL 查詢之前,必須先開啟要取之 Excel 檔案的連線。
若要建立連線,請建立名為 %Excel_File_Path% 的新變數,並使用 Excel 檔案路徑來初始化該變數。 或者,您也可以跳過此步驟,並在稍後的流程中使用該檔案的硬式編碼路徑。
現在,請部署開啟 SQL 連線動作,並在其屬性中填入以下連線字串。
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";
注意
若要順利使用提供的連線字串,您必須下載並安裝 Microsoft Access 資料庫引擎 2010 可轉散發套件。
開啟受密碼保護之 Excel 檔案的 SQL 連線
當您在受密碼保護 Excel 檔案上執行 SQL 查詢時,需要採用不同的方法。 開啟 SQL 連線動作無法連線至受密碼保護的 Excel 檔案,因此您必須移除保護。
若要達成此目標,請使用啟動 Excel 動作來啟動 Excel 檔案 。 檔案受密碼保護,因此請在密碼欄位輸入正確的密碼。
接著,部署適當的 UI 自動化動作,並瀏覽至檔案>資訊>保護活頁簿>使用密碼加密。 您可以在自動化桌面應用程式中找到有關 UI 自動化,以及如何使用相應動作的詳細資訊。
選取使用密碼加密之後,使用 填入視窗中的文字欄位動作,在快顯對話方塊中的填入空白字串。 若要填入空白字串,請使用以下運算式:%""%。
若要在對話方塊中的按下確定按鈕並套用變更,請部署按下視窗中的按鈕動作。
最後,部署關閉 Excel 動作,將不受保護的活頁簿另存為新的 Excel 檔案。
儲存檔案之後,依照開啟 Excel 檔案的 SQL 連線中的指示,開啟連線。
當 Excel 檔案的操作完成時,請使用刪除檔案動作來刪除 Excel 檔案的不受保護複本。
讀取 Excel 試算表的內容
雖然讀取 Excel 工作表動作可讀取 Excel 工作表的內容,但迴圈可能需要大量的時間來逐一查看擷取的資料。
有個方式可更有效率地從試算表擷取特定值,那就是將 Excel 檔案視為資料庫,並針對它們執行 SQL 查詢。 這種方法更快,並提高了流程的效能。
若要擷取試算表中的所有內容,您可以在執行 SQL 陳述式動作中使用以下 SQL 查詢。
SELECT * FROM [SHEET$]
注意
若要在您的流程中套用此 SQL 查詢,請用要存取的試算表名稱取代工作表預留位置。
若要擷取特定資料行中包含特定值的資料列,請使用以下 SQL 查詢:
SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'
注意
若要在您的流程中套用此 SQL 查詢:
- 將工作表取代為您要存取的試算表名稱。
- 將資料行名稱取代為包含所要尋找之值的資料列。 Excel 工作表第一列中的資料行會標示為資料表的資料行名稱。
- 將值取代為您要尋找的值。
從 Excel 資料列中刪除資料
雖然 Excel 不支援刪除 SQL 查詢,但您可以使用更新查詢,將特定資料列的所有儲存格設定為 Null。
具體來說,您可以使用以下 SQL 查詢:
UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'
在開發流程時,您必須用要存取的試算表名稱取代工作表預留位置。
COLUMN1 與 COLUMN2 預留位置代表要處理的資料行名稱。 在此範例中,有二個資料行,但是在實際的案例中,資料行的數目可能不同。 Excel 工作表第一列中的資料行會標示為資料表的資料行名稱。
查詢的 [COLUMN1]='VALUE' 部分定義了要更新的資料列。 在您的流程中,根據哪個組合唯一地描述資料列來使用資料行名稱和值。
擷取除特定資料列以外的 Excel 資料
在某些情況下,您可能需要擷取 Excel 試算表的所有內容 (特定資料列除外)。
有個方便的方法可讓您達成此目標,那就是將不想要的資料列值設為 Null,然後擷取除 Null 以外的所有值。
若要變更試算表中特定資料列的值,您可以使用更新 SQL 查詢,如從 Excel 資料列中刪除資料中所示:
UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'
接著,執行以下 SQL 查詢,以擷取不包含 Null 值的所有試算表資料列:
SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL
COLUMN1 與 COLUMN2 預留位置代表要處理的資料行名稱。 在此範例中,有二個資料行,但是在實際的資料表中,資料行的數目可能不同。 Excel 工作表第一列中的所有資料行會標示為資料表的資料行名稱。
意見反應
https://aka.ms/ContentUserFeedback。
即將登場:在 2024 年,我們將逐步淘汰 GitHub 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應