Chạy truy vấn SQL trên tệp Excel

Mặc dù các hành động Excel có thể xử lý hầu hết các tình huống tự động hóa Excel, nhưng truy vấn SQL có thể truy xuất và thao tác lượng dữ liệu Excel đáng kể hiệu quả hơn.

Giả sử một luồng chỉ phải sửa đổi các sổ đăng ký Excel có chứa một giá trị cụ thể. Để đạt được chức năng này mà không cần truy vấn SQL, bạn cần có vòng lặp, điều kiện và nhiều hành động Excel.

Ngoài ra, bạn có thể triển khai chức năng này bằng các truy vấn SQL chỉ bằng hai hành động, Mở kết nối SQL và Thực thi các câu lệnh SQL.

Mở kết nối SQL tới tệp Excel

Trước khi chạy truy vấn SQL, bạn phải mở kết nối với tệp Excel mà bạn muốn truy cập.

Để thiết lập kết nối, hãy tạo một biến mới có tên %Excel_File_Path% và khởi tạo nó bằng đường dẫn tệp Excel. Theo tùy chọn, bạn có thể bỏ qua bước này và sử dụng đường dẫn được mã hóa cứng của tệp ở phần sau của quy trình.

Bây giờ, hãy triển khai hành động Mở kết nối SQL và điền chuỗi kết nối sau vào thuộc tính của nó.

Nhà cung cấp=Microsoft.ACE.OLEDB.12.0;nguồn dữ liệu=%Excel_File_Path%;Thuộc tính mở rộng="Excel 12.0 Xml;HDR=YES";

Lưu ý

Để sử dụng thành công chuỗi kết nối đã trình bày, bạn phải tải xuống và cài đặt Microsoft Access Database Engine 2010 Redistributable.

Mở kết nối SQL tới tệp Excel được bảo vệ bằng mật khẩu

Cần có một cách tiếp cận khác trong trường hợp bạn chạy truy vấn SQL trên các tệp Excel được bảo vệ bằng mật khẩu. Hành động Kết nối SQL mở không thể kết nối với các tệp Excel được bảo vệ bằng mật khẩu, do đó bạn phải xóa bảo vệ.

Để đạt được điều đó, hãy khởi chạy tệp Excel bằng hành động Khởi chạy Excel . Tệp được bảo vệ bằng mật khẩu, vì vậy hãy nhập mật khẩu thích hợp vào trường Mật khẩu .

Tiếp theo, triển khai các hành động tự động hóa giao diện người dùng thích hợp và điều hướng đến Tệp>Thông tin>Bảo vệ sổ làm việc>Mã hóa bằng mật khẩu. Bạn có thể tìm thêm thông tin về tự động hóa giao diện người dùng và cách sử dụng các hành động tương ứng trong Tự động hóa các ứng dụng dành cho máy tính để bàn.

Sau khi chọn Mã hóa bằng mật khẩu, hãy điền một chuỗi trống vào hộp thoại bật lên bằng cách sử dụng Trường văn bản điền trong cửa sổ hành động. Để điền một chuỗi trống, hãy sử dụng biểu thức sau: %""%.

Để nhấn nút OK trong hộp thoại và áp dụng các thay đổi, hãy triển khai hành động Nhấn nút trong cửa sổ .

Cuối cùng, triển khai hành động Đóng Excel để lưu sổ làm việc không được bảo vệ dưới dạng tệp Excel mới.

Sau khi lưu tệp, hãy làm theo hướng dẫn trong Mở kết nối SQL tới tệp Excel để mở kết nối tới tệp đó.

Khi thao tác với tệp Excel hoàn tất, hãy sử dụng tác vụ Xóa tệp để xóa bản sao không được bảo vệ của tệp Excel.

Đọc nội dung của bảng tính Excel

Mặc dù hành động Đọc từ trang tính Excel có thể đọc nội dung của trang tính Excel, nhưng các vòng lặp có thể mất nhiều thời gian để lặp qua dữ liệu được truy xuất.

Một cách hiệu quả hơn để truy xuất các giá trị cụ thể từ bảng tính là coi các tệp Excel dưới dạng cơ sở dữ liệu và thực hiện các truy vấn SQL trên chúng. Cách tiếp cận này nhanh hơn và tăng hiệu suất của luồng.

Để truy xuất tất cả nội dung của bảng tính, bạn có thể sử dụng truy vấn SQL sau trong tác vụ Thực thi câu lệnh SQL .

SELECT * FROM [SHEET$]

Lưu ý

Để áp dụng truy vấn SQL này trong luồng của bạn, hãy thay thế phần giữ chỗ SHEET bằng tên của bảng tính mà bạn muốn truy cập.

Để truy xuất các hàng chứa một giá trị cụ thể trong một cột cụ thể, hãy sử dụng truy vấn SQL sau:

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

Lưu ý

Để áp dụng truy vấn SQL này trong luồng của bạn, hãy thay thế:

• SHEET với tên bảng tính bạn muốn truy cập.
• COLUMN NAME với cột chứa giá trị bạn muốn tìm. Các cột ở hàng đầu tiên của bảng tính Excel được xác định là tên cột của bảng.
• VALUE bằng giá trị bạn muốn tìm.

Xóa dữ liệu khỏi một hàng Excel

Mặc dù Excel không hỗ trợ truy vấn SQL DELETE SQL, nhưng bạn có thể sử dụng truy vấn UPDATE để đặt tất cả các ô của một hàng cụ thể thành null.

Chính xác hơn, bạn có thể sử dụng truy vấn SQL sau:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Trong khi phát triển quy trình của mình, bạn phải thay thế phần giữ chỗ SHEET bằng tên của bảng tính mà bạn muốn truy cập.

Phần giữ chỗ COLUMN1 và COLUMN2 đại diện cho tên của các cột cần xử lý. Ví dụ này có hai cột, nhưng trong trường hợp thực tế, số lượng cột có thể khác nhau. Các cột ở hàng đầu tiên của bảng tính Excel được xác định là tên cột của bảng.

Phần [COLUMN1]='VALUE' của truy vấn xác định hàng bạn muốn cập nhật. Trong quy trình của bạn, hãy sử dụng tên cột và giá trị dựa trên sự kết hợp nào mô tả duy nhất các hàng.

Truy xuất dữ liệu Excel ngoại trừ một hàng cụ thể

Trong một số trường hợp, bạn có thể cần truy xuất tất cả nội dung của bảng tính Excel ngoại trừ một hàng cụ thể.

Một cách thuận tiện để đạt được điều này là đặt các giá trị của hàng không mong muốn thành null và sau đó truy xuất tất cả các giá trị ngoại trừ các giá trị null.

Để thay đổi giá trị của một hàng cụ thể trong bảng tính, bạn có thể sử dụng UPDATE truy vấn SQL, như được trình bày trong Xóa dữ liệu khỏi một hàng Excel:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Tiếp theo, chạy truy vấn SQL sau để truy xuất tất cả các hàng của bảng tính không chứa giá trị null:

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

Các phần giữ chỗ COLUMN1 và COLUMN2 thể hiện tên của các cột cần xử lý. Ví dụ này có hai cột, nhưng trong một bảng thực, số cột có thể khác nhau. Tất cả các cột ở hàng đầu tiên của bảng tính Excel được xác định là tên cột của bảng.