[Power Automate Desktop] 엑셀을 데이터베이스처럼 사용하기

이전 예제에서는 반복문을 이용하여 엑셀 데이터를 읽고 입력했습니다. 이렇게 하면 데이터가 많을 경우 속도가 너무 느려지는 단점이 있습니다. 이에 엑셀을 데이터베이스처럼 이용하면 좀 더 효율적이 플로어를 만들 수 있습니다.

이를 위해 OLEDB Provider를 통해 연결하면 데이터베이스처럼 사용할 수 있습니다.

 

먼저 +새 흐름으로 새로운 플로어를 생성하고 좌측 메뉴 데이터베이스 > SQL 연결 활성화를 끌어옵니다.

 

다음 우측 탐색기 모양 아이콘을 클릭하여 공급자 탭에 OLE DB 공급자 중 Microsoft Office 12.0 Access Database Engine OLE DB Provider를 선택하고 하단 다음을 클릭합니다.

현재 Office 2013이 설치되어 있어 12.0을 선택했습니다. 만약 상위버전 Office를 설치했다면 16.0이 표시되는 분도 계실겁니다.

 

그런데 만약 Microsoft Office 12.0 Access Database Engine OLE DB Provider 혹은 Microsoft Office 16.0 Access Database Engine OLE DB Provider가 리스트에 없는 경우가 있습니다.

설치 방법은 이 글 제일 하단에 따로 설명했습니다.

 

다음으로 연결 탭을 선택하고 데이터 원본에는 작업 대상 엑셀 파일 경로를 입력하고 이름에 있는 ADMIN 값은 삭제합니다.

 

고급 탭을 클릭하고 ReadWrite를 체크합니다.

 

모두 탭을 클릭하고 Extended Properties를 더블 클릭한 후 속성 값에 Excel 12.0 Xml;HRD=YES를 입력합니다. OLEDB 제공자를 16.0으로 선택했다면 이에 맞게 변경합니다.

 

하단 Persist Security Info를 더블클릭하여 속성 값을 False로 선택합니다.

 

설정이 끝났습니다. 이제 연결탭으로 다시 돌아와 하단 연결 테스트 버튼을 클릭하여 테스트 성공을 확인합니다.

 

확인을 클릭하여 설정창을 종료하면 설정한 내용이 연결 문자열에 입력되어 있고 저장 버튼을 클릭하여 설정을 종료합니다.

 

이제 SQL문을 만들어 데이터를 잘 가져오는지 확인해보겠습니다.

우선 테스트해 볼 엑셀 문서는 아래와 같이 구성하였습니다.

 

좌측 메뉴 데이터베이스 > SQL 문 실행을 끌어와 연결 가져오기는 SQL 연결 변수를 선택합니다.

 

SQL문은 아래 그림과 같이 작성하고 저장합니다. 여기서 테이블명은 엑셀 파일의 시트 이름입니다.

또한 이 구문의 뜻은 exam시트에 있는 모든 데이터를 가져오겠다라는 내용입니다.

만약 시트 내용 중 일부분만 가져오고 싶다면 WHERE 조건을 쓰는 방법과 [exam$A7:E11]과 같이 시트의 영역을 정의해서 가져올 수 있습니다. 이때 첫번째 행은 타이틀로 인식해 실제 데이터는 A8부터 가져오게 됩니다.

 

다음으로 좌측 메뉴 데이터베이스 > SQL 문 종료를 끌어와 배치하고 저장합니다.

모든 입출력 (파일, 데이터베이스 등)에서는 연결한 후 모든 작업이 끝나면 항상 종료를 시켜줘야 합니다.

 

이제 상단 실행 버튼을 클릭하여 플로어를 실행 한 후 우측 플로어 변수 QueryResult 우측 보기 버튼을 클릭하면 엑셀 데이터를 읽은 결과를 확인할 수 있습니다.

 

 

* Microsoft Office OLEDB Provider가 없을 경우 PC에 설치된 Microsoft Office에 Access가 없거나 혹은 운영체제는 64비트인데 Microsoft Office가 32비트로 설치된 경우 OLEDB Provider가 없을 수 있습니다. 이에 해결 방법은 아래와 같습니다. 1. OLEDB Provider 설치 내역 확인 당연한 이야기이겠지만 확인차원에서 PC에 OLEDB Provider 설치 내역을 확인해보겠습니다. PowerShell을 실행하고 (New-Object system.data.oledb.oledbenumerator).GetElements() | select SOURCES_NAME, SOURCES_DESCRIPTION 을 입력 해보면 아래 그림과 같이 설치된 내역이 없는 것을 확인할 수 있습니다. 2. Microsoft Access Database Engine 다운로드 아래 버전에 맞는 링크로 들어가 설치파일을 32비트용, 64비트용 모두 받습니다. Office 2010 https://www.microsoft.com/ko-kr/download/details.aspx?id=13255 Office 2013 https://www.microsoft.com/ko-KR/download/details.aspx?id=39358 Office 2016 이상 https://www.microsoft.com/ko-KR/download/details.aspx?id=54920 저의 경우 Office 2013이 설치되어 있으나 하위 호환이니 2010버전을 다운 받아 설치하였습니다. 3. Microsoft Access Database Engine 설치 다운 받은 실행파일 중 32비트용 먼저 설치합니다. 설치 완료 후 64비트 버전을 설치해야하는데 64비트 버전을 실행하면 설치된 Office가 32비트라고 설치가 안됩니다. (현재 PC에 설치된 Office가 32비트 버전입니다.) 이때 64비트를 강제로 설치해야합니다. PowerShell을 실행하여 해당 설치 파일이 있는 폴더로 이동한 다음 명령어를 > .\AccessDatabaseEngine_X64.exe /passive 즉, 패시브 모드로 설치하면 위와 같은 경고 없이 64비트 버전이 설치됩니다. (설치 파일 이름은 다를 수 있습니다.) 그리고 PC에 OLEDB Provider 설치 내역을 확인해 보면 처음에 안보이던 Microsoft.ACE.OLEDB.12.0이 추가 되었음을 확인할 수 있습니다.