Home > 강좌 > 엑셀수식강좌 > 엑셀을 제대로 쓰는 데이터처리 > 파워쿼리로 외부 데이터 가져오기

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

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

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

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

1.  CSV파일 가져오기

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

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

엑셀 파워쿼리로 CSV 파일 가져오기

 

위의 CSV파일을 파워쿼리를 이용하여 다음과 같이 엑셀로 가져옵니다.

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

엑셀 파워쿼리로 CSV 파일 가져오기

 

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

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

엑셀 파워쿼리로 CSV 파일 가져오기

 

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

엑셀 파워쿼리로 CSV 파일 가져오기

 

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

엑셀 파워쿼리로 CSV 파일 가져오기

 

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

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

엑셀 파워쿼리로 CSV 파일 가져오기

 

2. PDF파일 가져오기

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

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

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

엑셀 파워쿼리로 PDF 파일 가져오기

 

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

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

엑셀 파워쿼리로 PDF 파일 가져오기

 

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

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

엑셀 파워쿼리로 PDF 파일 가져오기

 

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

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

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

엑셀 파워쿼리로 PDF 파일 가져오기

 

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

엑셀 파워쿼리로 PDF 파일 가져오기

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

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

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

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

엑셀 파워쿼리로 PDF 파일 가져오기

 

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

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

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

엑셀 파워쿼리로 웹에서 데이터 가져오기

 

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

엑셀 파워쿼리로 웹에서 데이터 가져오기

 

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

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

엑셀 파워쿼리로 웹에서 데이터 가져오기

 

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

엑셀 파워쿼리로 웹에서 데이터 가져오기

 

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

엑셀 파워쿼리로 웹에서 데이터 가져오기

 

 


엑셀 수식 강좌 

[ 수식 다루기 ]

[ 엑셀에서 입력과 표시방법 ]

[ 수식에서 셀과 범위 참조 방식 ]

[ 엑셀을 제대로 쓰는 데이터처리 ]

[ 엑셀 이름정의와 표기능 ]

[ 수식을 분석하고 오류 해결하기 ]

 

댓글 달기

이메일 주소는 공개되지 않습니다.

Scroll to Top
%d 블로거가 이것을 좋아합니다: