Excel에서 반복적으로 데이터를 가져오는 작업은 생각보다 번거롭습니다. 특히 여러 파일을 정기적으로 처리해야 할 때 더욱 그렇죠. 다행히 Power Query와 간단한 폴더 체계만 활용하면, 복잡한 VBA 코드나 매크로 없이도 드래그 앤 드롭 방식의 파일 업로드를 구현하고 데이터 모델을 자동으로 새로 고칠 수 있습니다.
이 튜토리얼에서는 Excel과 Power Query를 사용해 드래그 앤 드롭 방식의 파일 업로드 시스템을 구축하는 방법을 단계별로 살펴봅니다.
예를 들어 현장 요원들에게서 매월 받는 판매 보고서를 관리한다고 가정해 보세요. 데이터를 일일이 복사해 붙여넣는 대신, 요원들에게 공유 폴더에 CSV 파일을 끌어다 놓기만 하면 되는 체계를 만들면 됩니다.
1단계: 드래그 앤 드롭용 폴더 준비
- 파일 탐색기를 엽니다.
- 새 폴더를 생성합니다(예: 'Drag and Drop Files').
C:\Users\YourName\Documents\Drag and Drop Files
- 폴더에 샘플 파일을 한 개 이상 넣어 둡니다. 예시: Sales June.csv, Sales July.csv
- 사용자들이 이 폴더로 파일을 드래그 앤 드롭하도록 안내합니다. 폴더를 공유하거나 빠른 실행 영역에 고정해 두면 더욱 편리합니다.
각 파일에는 판매 데이터(제품, 지역, 수량, 매출)가 행 단위로 들어 있습니다. 모든 파일이 동일한 구조(같은 열, 같은 순서, 같은 CSV 형식)를 따르도록 하세요.
2단계: Power Query로 폴더에 연결
- Excel을 실행합니다.
- 데이터 탭 >> 데이터 가져오기 >> 파일에서 >> 폴더에서를 차례로 선택합니다.
- 'Drag and Drop Files' 폴더로 이동한 후 열기를 클릭합니다.
- Power Query가 해당 폴더의 파일 목록을 표시합니다.
- 미리 보기 창에서 데이터 결합 및 변환 또는 데이터 변환을 클릭합니다.
3단계: Power Query에서 파일 결합
Power Query 창에 폴더 내 파일 목록이 나타납니다.
- 결합을 클릭한 후 데이터 결합 및 변환을 선택합니다.
- 파일 결합 미리 보기 창이 열립니다.
- 필요에 맞는 시트·테이블·구분 기호를 선택합니다(Power Query가 헤더를 자동으로 감지합니다).
- 확인을 클릭합니다.
이 과정에서 Power Query는 다음 항목들을 자동으로 생성합니다.
- 모든 파일을 불러오는 함수
- 모든 파일의 데이터를 하나로 쌓아 올리는 변환 쿼리
- 데이터를 확장하고 결합하는 처리 단계
4단계: 필요한 경우 데이터 변환
쿼리 편집기에서 다양한 변환 작업을 적용할 수 있습니다.
- 열 이름 바꾸기
- 데이터 형식 변경
- 행 필터링
- 쿼리 병합 등
쿼리 편집기에서 다음을 진행합니다.
- 필요 없는 열(Extension, Source.Name 등)이 있다면 삭제합니다.
- 해당 열을 마우스 오른쪽 버튼으로 클릭 >> 제거를 선택합니다.
- 쿼리 이름을 알아보기 쉽게 변경합니다(예: CombinedFiles).
- 작업이 완료되면 닫기 및 로드를 클릭합니다.
- 이제 Excel 시트에 폴더 내 모든 파일의 데이터가 한꺼번에 불러와집니다.
5단계: 드래그 앤 드롭 업로드 시뮬레이션
실제 드래그 앤 드롭 업로드를 테스트해 보겠습니다.
- 새 .csv 파일을 'Drag and Drop Files' 폴더에 끌어다 놓습니다. 예를 들어 Sales August.csv 파일을 추가해 봅니다.
- Excel로 돌아갑니다.
- 테이블을 마우스 오른쪽 버튼으로 클릭 >> 새로 고침을 선택합니다.
- 방금 추가한 파일의 새로운 데이터 행이 즉시 나타납니다.
6단계: 새로 고침 자동화
- 데이터 탭 >> 쿼리 및 연결을 선택합니다.
- 쿼리를 마우스 오른쪽 버튼으로 클릭 >> 속성을 선택합니다.
- 10분마다 새로 고침 옵션을 활성화합니다.
- 확인을 클릭합니다.
특정 파일이나 형식만 처리하기
.csv 파일만 필터링하려면 다음 수식을 사용하세요.
= Table.SelectRows(Source, each Text.EndsWith([Extension], ".csv"))
임시 파일이나 시스템 파일(~$로 시작하는 파일)을 제외하려면 다음을 사용합니다.
= Table.SelectRows(Source, each not Text.StartsWith([Name], "~$"))
문제 해결 팁
- 파일 사용 중 오류: 새로 고침할 때 해당 파일이 다른 프로그램에서 열려 있지 않은지 확인하세요.
- 헤더 인식 실패: Power Query의 '파일 결합' 단계에서 첫 행을 헤더로 처리하도록 설정을 조정하세요.
- 성능 저하: 대용량 파일을 다룰 때는 워크시트 대신 데이터 모델에 로드하는 방식을 고려하세요.
마치며
위 단계를 따라 하면 Excel에서 드래그 앤 드롭 방식의 파일 업로드를 손쉽게 구현할 수 있습니다. Power Query와 감시 폴더만 있으면 VBA나 매크로 없이도 가볍고 사용자 친화적인 파일 업로드 시스템을 만들 수 있죠. 폴더를 계속 감시하며 새로 추가된 파일을 즉시 가져와 처리하는 Power Query의 강력한 기능은 반복적인 수작업에 쏟던 시간을 크게 아껴줍니다. 정기적인 파일 업데이트를 빠르고 안정적으로 통합해야 하는 소규모 비즈니스, 관리 부서, 현장 운영팀에 특히 적합한 방법입니다.