[Power Automate Desktop] SQL을 이용하여 엑셀 데이터 읽기/쓰기 속도 개선

이전 글에서 엑셀을 데이터베이스로 활용하는 방법을 설정했습니다.

2021.06.22 - [RPA/Power Automate Desktop] - [Power Automate Desktop] Excel을 데이터베이스처럼 사용하기

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

 

이를 이용하여 엑셀에 데이터를 추가하고 조건에 맞게 읽어 새로운 탭에 쓰는 작업을 해보도록 하겠습니다.

먼저 테스트에 이용할 엑셀 파일입니다. 첫번째 exam시트에는 학생번호, 과목별 점수 정보 및 평균 정보가 있고 두번째 student시트에는 학생번호, 이름 정보가 있습니다. exam의 번호와 student 번호는 서로 매칭되는 정보입니다.

 

테스트 내용은

1. exam 시트에 3개의 행을 추가하고

2. exam 시트와 student 시트를 매칭하여 데이터를 읽어

3. result 시트를 생성하여 복사하기

 

전체 플로어는 아래와 같습니다.

 

먼저 +새 흐름으로 새로운 플로어를 생성한 후 좌측 메뉴 Excel > Excel 시작을 끌어와 미리 생성한 엑셀 파일을 경로로 입력한 후 저장합니다.

 

좌측 메뉴 Excel > 활성 Excel 워크시트 설정을 끌어와 exam 시트를 활성화 합니다.

 

3개의 추가 행을 입력하기 위해 데이터가 없는 첫 번째 행 번호를 가져와야 합니다. 좌측 메뉴 Excel > Excel 워크시트에서 첫 번째 빈 열/행 가져오기를 끌어와 저장합니다. 이렇게 되면 FirstFreeRow 변수에 데이터가 없는 첫 번째 행 번호, 즉 3개의 행을 입력할 첫 번째 행 번호가 저장됩니다.

 

다음으로 데이터베이스를 연결합니다. 좌측 메뉴 데이터베이스 > SQL 연결 활성화를 끌어와 연결 관련 옵션 입력 후 저장합니다. (자세한 옵션은 이전 글 참조)

 

3개의 행을 추가하는 반복문의 마지막 값을 변수로 저장합니다. 좌측 메뉴 변수 > 변수 설정을 끌어와 첫 번째 데이터가 없는 행 값 + 3을 합니다. 이렇게 되면 반복문은 총 3번 반복하게 됩니다.

 

좌측 메뉴 반복 > 반복 조건을 끌어와 아래와 같이 값을 설정하고 저장합니다. 현재 값 기준으로 12 < 15이므로 3번 반복입니다.

 

이제 데이터를 입력하는 insert문을 작성해야 합니다. 좌측 메뉴 데이터베이스 > SQL문 실행을 끌어와 아래와 같이 작성하고 저장합니다.

FirstFreeRow는 엑셀 데이터 내 번호 값으로 행 번호보다 -1이 작기 때문에 이를 반영한 것이고 나머지 점수는 테스트 개념으로 임의의 데이터를 입력했습니다. 따라서 추가되는 3개의 행은 번호 값을 제외하고 모두 동일한 값이 들어갑니다.

엑셀 데이터의 패턴처럼 서로 다른 데이터를 넣으려면 좌측 메뉴 변수 > 임의의 숫자 생성을 이용하여 각 과목별 변수를 생성해 임의의 숫자를 넣고 처리하면 됩니다.

 

반복문을 위해 좌측 메뉴 변수 > 변수 증가를 끌어와 반복문 조건 변수를 +1 증가시킵니다.

 

3개의 추가 행을 입력하는 부분은 끝났습니다. 결과 확인은 아래에서 한번에 보겠습니다.

다음으로 exam 시트에서 평균 점수 50점 이상인 학생을 student 시트의 이름정보를 포함하여 조회하는 내용입니다. 좌측 메뉴 데이터베이스 > SQL문 실행을 끌어와 아래와 같이 SQL문을 작성하고 저장합니다.

SQL문에서는 exam 시트와 student 시트를 번호 값 기준으로 Outer Join하여 평균 점수 50점 이상인 데이터만 가져옵니다. 가져온 데이터는 QueryResult에 데이터 테이블 형태로 저장됩니다.

 

다음으로 엑셀 파일에 새로운 시트를 추가해야 합니다. 좌측 메뉴 Excel > 새 워크시트 추가를 끌어와 워크시트 이름 및 위치를 선택하고 저장합니다.

 

좌측 메뉴 Excel > 활성 Excel 워크시트 설정을 끌어와 위에서 추가한 데이터를 입력할 시트를 선택하고 저장합니다.

 

위 SQL문을 통해 조회한 데이터 테이블 형태를 보면 타이틀 정보가 없습니다. 따라서 타이틀 정보를 별도로 입력해줘야 합니다. 기존 글에서는 반복문을 통해 타이틀을 넣는데 이번에는 다른 방법으로 입력해 보겠습니다.

엑셀에 한번에 쓰기 위해서는 데이터 테이블 형태로 되어야 합니다. 따라서 변수 설정 시 해당 형태에 맞게 넣어줘야 합니다.

좌측 메뉴 변수 > 변수 설정을 끌어와 변수의 값을 아래와 같이 입력하고 저장합니다.

이를 좀 더 자세히 설명하면 변수 설정을 통해 데이터 테이블 형태로 만들기 위해서는

{[A1, B1, C1, D1], [A2, B2, C2, D2], [A3, B3, C3, D3]}

의 형태가 되어야 합니다. {} 안의 [] 값이 행 값이며 [] 안의 값이 열 값입니다. 이전에 설명했던 2차원 배열 형태입니다.

 

이제 데이터를 쓰겠습니다. 먼저 타이틀 정보는 좌측 메뉴 Excel > Excel 워크시트에 쓰기를 끌어와 위 변수 설정을 통해 만든 변수를 1열, 1행에 넣습니다.

 

다음으로 데이터 부분을 넣습니다. 좌측 메뉴 Excel > Excel 워크시트에 쓰기를 끌어와 쓸 값으로는 SQL문을 통해 저장된 QueryResult를 설정하고 타이틀 다음 행부터 설정하고 저장합니다.

 

데이터 처리는 모두 끝났습니다. 좌측 데이터베이스 > SQL연결 종료를 끌어와 처음 생성했던 SQL 연결을 종료합니다.

 

다음으로 엑셀을 저장하고 종료합니다. 좌측 메뉴 Excel > Excel 닫기를 끌어와 저장합니다.

 

이제 상단 실행 버튼을 클릭하여 실행하고 종료 후 엑셀 파일을 보면 exam 시트에는 번호 11~13까지의 새로운 행이 추가되었고 result 시트에는 평균점수 50점인 학생의 이름정보와 함께 입력되어 있습니다.

 

 

마지막으로 테스트를 해보면 이전 글에서 엑셀 쓰기/읽기 시 반복문을 통해 작업할 때와는 비교 불가능할 정도로 빠르게 이루어 집니다. 또한 위에서 테스트 한 insert, select말고 update(수정), delete(삭제)도 가능합니다.

SQL문 작성을 처음 접하시는 분들은 아래 링크에서 기초 내용 확인할 수 있습니다.

https://www.w3schools.com/sql/default.asp

SQL Tutorial