파워쿼리로 외부 데이터 가져오기

엑셀을 이용하여 업무 처리를 하다 보면 엑셀로 만든 자료만 이용하는 것이 아니라 다른 형식으로 만들어진 외부 데이터를 활용해야 하는 경우가 있습니다.

외부 데이터 형식은 CSV, 텍스트, PDF , 웹 등 다양한 형태가 있는데 엑셀은 이런 다양한 형태의 외부 데이터를 엑셀로 가져오는 기능을 제공하고 있습니다.

지금부터 설명하는 외부데이터 가져오기 기능은 엑셀 2016이상 버전부터 제공되는 파워쿼리 기능을 이용합니다.  엑셀 2010, 2013버전은 파워쿼리가 기본으로 제공되지 않으므로 추가기능을 설치해야 합니다. 파워쿼리 소개는 다음 링크를 참고하세요.
[파워쿼리 소개]

 

1.  CSV파일 가져오기

CVS는 Comma-Separated Values의 약자입니다. '콤마로 분리된 값'이라는 뜻입니다.

텍스트형식으로 저장된 파일이므로 윈도우의 메모장 프로그램으로 열어보면 다음과 같이 콤마로 값이 나누어져 있는 것을 알 수 있습니다.

 

엑셀에서 '새 통합 문서'를 하나 만든 후, 파워쿼리를 이용하여 위의 CSV파일을 다음과 같이 엑셀로 가져옵니다.

리본 메뉴에서 [데이터] 탭 → [데이터 가져오기 및 변환] 그룹 → ① [데이터 가져오기]→ ② [파일에서]→ ③ [텍스트/CSV]를 누르면

 

[데이터 가져오기] 대화상자가 나타납니다.

④ 가져올 CSV파일을 선택하고 ⑤ <가져오기> 버튼을 누릅니다.

 

다음과 같은 대화상자가 나타나면 ⑥ <로드> 버튼을 누릅니다.

 

다음과 같이 CSV파일을 엑셀에 '표' 형식으로 가져왔습니다.

 

만약, 표 형식이 아니라  일반적으로 많이 사용하는 '범위' 형식으로 변환하려면 다음과 같이

① 표의 아무 셀이나 선택하고 ② 메뉴에서 [표]를 선택 ③ [범위로 변환]을 선택합니다.

 

 

2. PDF파일 가져오기

아래와 같이 PDF파일에 표가 있을 때 영역을 복사해서 엑셀에 붙여넣으면 가로로 된 데이터가 세로로 붙여넣어지는 등 제대로 붙여넣어지지 않는 경우가 많습니다.

파워쿼리에서 제공되는 PDF가져오기 기능을 이용하면 아주 간단하게 표 데이터를 엑셀로 가져올 수 있습니다.

[참고!] 2022년 10월 현재 PDF가져오기는 Office 365에서만 제공됩니다.

 

다음과 같이 위의 PDF파일을 엑셀로 가져옵니다.

리본 메뉴에서 [데이터] 탭 → [데이터 가져오기 및 변환] 그룹 → ① [데이터 가져오기]→ ② [파일에서]→ ③ [PDF에서]를 누르면

 

[데이터 가져오기] 대화상자가 나타납니다.

④ 가져올 PDF파일을 선택하고 ⑤ <가져오기> 버튼을 누르면

 

다음과 같은 대화상자가 나타납니다.

왼쪽에는 PDF파일에 있는 표로 된 자료목록이 나타납니다. 그 중에 하나를 선택해 보면 오른쪽에서 미리보기로 가져올 데이터를 확인할 수 있습니다.

이번 예에서는 ⑥ Table004(Page 3)을 선택하고 ⑦ <로드> 버튼을 누릅니다.

 

다음과 같이 PDF파일의 표를 엑셀로 가져왔습니다.

 

그런데 표에 약간의 문제가 있습니다. 빨간 박스로 표시된 범위의 숫자가 텍스트 형식으로 되어 있습니다.

파워쿼리의 데이터 변환 기능을 이용할 수도 있지만 다음과 같이 처리할 수 도 있습니다.

표의 아무 셀이나 선택하고 메뉴에서 [표]를 선택 [범위로 변환]을 선택합니다. 범위로 변환하고 나니 숫자가 입력된 셀에 초록색 삼각형이 표시되었습니다. 초록색 삼각형 표시는 뭔가 오류가 있다는 뜻입니다.

다음과 같이 ① 숫자 셀 범위를 선택, 마우스 오른쪽 버튼을 클릭   ② 메뉴에서 [숫자로 변환]을 선택하면 초록색 삼각형 표시가 사라지고 정상적인 숫자로 변환됩니다.

 

3. 웹에서 데이터 가져오기

파워쿼리 이전에도 웹에서 엑셀로 데이터를 가져오는 기능이 있었는데 웹의 데이터를 그냥 엑셀에 붙여 넣기한 수준이어서 데이터를 정리해 주어야 하는 등 불편한 점이 많았습니다.  파워쿼리를 이용하면 웹 화면의 원하는 영역의 데이터를 선택하여 깔끔하게 가져올 수 있습니다.

이번 예에서는 다음과 같이 네이버 금융 사이트에서 주식의 'TOP종목' 정보를 가져오겠습니다.

 

리본 메뉴에서 [데이터] 탭 → [데이터 가져오기 및 변환] 그룹 → ① [데이터 가져오기] → ② [기타 원본에서]→ ③ [웹]을 선택하면

 

다음과 같이 사이트 주소를 입력하는 대화상자가 나타납니다.

④ '네이버 금융' 사이트 주소 'https://finance.naver.com/'를 입력하고 ⑤ <확인> 버튼을 누릅니다.

 

만약 아래와 같은 창이 나타나면 <연결> 버튼을 누릅니다.

 

[탐색 창]이 나타나면 왼쪽의 목록에서 ⑥ 원하는 항목을 선택하고 ⑦ <로드> 버튼을 누르면

 

다음과 같이 엑셀로 네이버 금융의 'TOP종목' 정보를 가져옵니다.

 

---

엑셀 수식 강좌 

[ 엑셀 데이터 다루기 ]

• 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
• 수식으로 한 번에 텍스트 나누기
• 엑셀 고급필터로 다양하게 자료 필터링하기
• 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
• 엑셀 오류데이터 처리하기
• 파워 쿼리(Power Query) 소개
• 파워쿼리로 외부 데이터 가져오기
• 엑셀에서 중복 데이터 제거하기
• 엑셀 텍스트 나누기의 모든 것
• 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
• 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
• 사용자 지정 수식으로 데이터 유효성 검사하기
• 데이터 유효성 검사로 허용된 데이터만 입력
• 엑셀 데이터 다루기 첫 단추는 데이터 구조화