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 쿼리를 실행하는 것입니다. 이 접근 방식은 더 빠르고 흐름의 성능을 향상시킵니다.
스프레드시트의 모든 내용을 검색하려면 Execute SQL 실행 문 작업에서 다음 SQL 쿼리를 사용하세요.
SELECT * FROM [SHEET$]
노트
흐름에 이 SQL 쿼리를 적용하려면 SHEET 자리 표시자를 액세스하려는 스프레드시트의 이름으로 바꿉니다.
특정 열의 특정 값을 포함하는 행을 검색하려면 다음 SQL 쿼리를 사용합니다.
SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'
노트
흐름에 이 SQL 쿼리를 적용하려면 다음을 바꿉니다.
- 시트를 액세스하려는 스프레드시트의 이름으로 대체합니다.
- 열 이름을 찾고자 하는 값이 들어 있는 열로 대체. Excel 워크시트의 첫 번째 행에 있는 열은 테이블의 열 이름으로 식별됩니다.
- 값을 찾고자 하는 값으로 대체합니다.
Excel 행에서 데이터 삭제
Excel은 DELETE SQL 쿼리를 지원하지 않지만 UPDATE 쿼리를 사용하여 특정 행의 모든 셀을 null로 설정할 수 있습니다.
보다 정확하게는 다음 SQL 쿼리를 사용할 수 있습니다.
UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'
흐름을 개발하는 동안 SHEET 자리 표시자를 액세스하려는 스프레드시트의 이름으로 바꿔야 합니다.
COLUMN1 및 COLUMN2 자리 표시자는 처리할 열의 이름을 나타냅니다. 이 예제에는 두 개의 열이 있지만 실제 시나리오에서는 열 수가 다를 수 있습니다. Excel 워크시트의 첫 번째 행에 있는 열은 테이블의 열 이름으로 식별됩니다.
쿼리의 [COLUMN1]='VALUE' 부분은 업데이트하려는 행을 정의합니다. 흐름에서 열 이름과 행을 고유하게 설명하는 조합을 기반으로 하는 값을 사용합니다.
특정 행을 제외한 Excel 데이터 검색
일부 시나리오에서는 특정 행을 제외한 Excel 스프레드시트의 모든 내용을 검색해야 할 수 있습니다.
이를 달성하는 편리한 방법은 원하지 않는 행의 값을 null로 설정한 다음 null 값을 제외한 모든 값을 검색하는 것입니다.
스프레드시트에서 특정 행의 값을 변경하려면 Excel 행에서 데이터 삭제에 표시된 대로 UPDATE SQL 쿼리를 사용합니다.
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 워크시트의 첫 번째 행에 있는 모든 열은 테이블의 열 이름으로 식별됩니다.