이 글에서는 XML 파일에서 엑셀(Excel)로 데이터를 추출하는 방법을 소개합니다. XML 형식은 주로 웹에서 데이터를 저장하고 전송하는 데 사용되지만, 필요에 따라 이 데이터를 엑셀로 가져와 가독성 좋게 정리해야 할 때가 많습니다. 오늘은 누구나 손쉽게 따라 할 수 있는 2가지 방법, 즉 시스템에 저장된 XML 파일을 가져오는 방법과 웹상의 XML 데이터를 가져오는 방법을 단계별로 살펴보겠습니다.
XML 파일이란?
XML(Extensible Markup Language, 확장 마크업 언어)은 다양한 응용 프로그램과 시스템 간에 데이터를 저장하고 전송하기 위한 파일 형식입니다. XML은 HTML과 비슷하지만 태그가 미리 정의되어 있지 않고 사용자가 직접 정의할 수 있다는 점이 큰 차이입니다.
XML 파일은 사람이 일반 텍스트처럼 읽기 어렵기 때문에, 내용을 확인하거나 분석하려면 다른 형식으로 변환해야 합니다. 예를 들어 파일에 텍스트 데이터만 있다면 메모장 같은 텍스트 편집기로 열어 수정할 수 있지만, 텍스트와 숫자가 섞여 있다면 엑셀로 변환하는 것이 가장 편리합니다. 엑셀은 데이터를 표 형태로 깔끔하게 보여주기 때문에 XML 데이터를 분석하기에 적합한 도구입니다.
XML 데이터를 엑셀로 추출하는 2가지 방법
설명을 위해 두 가지 예시를 사용합니다. 첫 번째 방법에서는 학생들의 이름, 성, 거리, 도시, 메일 주소 정보가 담긴 XML 파일을 활용하며, 데이터(Data) 탭과 개발 도구(Developer) 탭 두 가지 경로로 가져오는 과정을 보여드립니다. 두 번째 방법에서는 웹사이트의 사이트맵(sitemap)이 담긴 XML 링크를 엑셀로 불러오는 방법을 알아봅니다. 사이트맵이란 웹사이트의 주요 페이지 목록을 담고 있는 파일을 말합니다.
1. 컴퓨터에 저장된 XML 파일을 엑셀로 가져오기
첫 번째 방법은 로컬 시스템에 저장된 XML 파일을 엑셀로 가져오는 것입니다. 여기서는 데이터 탭을 이용하는 방법과 개발 도구 탭을 이용하는 방법, 두 가지를 차례로 설명합니다.
1-1. 데이터 탭 활용하기 (파워 쿼리)
XML 파일을 가져올 때는 엑셀의 데이터 탭과 파워 쿼리 편집기(Power Query Editor)를 활용할 수 있습니다. 방법은 생각보다 간단합니다. 아래 단계를 따라 해 보세요.
진행 순서:
- 먼저 데이터 탭으로 이동한 뒤 데이터 가져오기(Get Data)를 클릭합니다. 드롭다운 메뉴가 나타납니다.
- 파일에서(From File)를 선택하면 하위 메뉴가 열립니다.
- 여기서 XML에서(From XML)를 선택하면 데이터 가져오기 창이 나타납니다.
- 데이터 가져오기 창에서 원하는 XML 파일을 선택하고 가져오기(Import) 버튼을 클릭합니다.
- 이 예제에서는 “File to Import.xml” 파일을 선택하여 가져왔습니다.
- 그러면 탐색기(Navigator) 창이 열립니다.
- 탐색기 창에서 추출하려는 XML 데이터를 선택합니다.
- 본 예제에서는 왼쪽 패널에서 “student” 항목을 클릭했습니다.
- 이후 데이터 변환(Transform Data) 버튼을 클릭합니다.
- 그러면 파워 쿼리 편집기 화면이 나타납니다.
- 편집기에서 첫 번째 열인 name 열 머리글의 확장 아이콘을 클릭합니다.
- 이어서 확인(OK)을 클릭합니다.
- 같은 방식으로 address 열의 확장 아이콘을 클릭한 뒤 확인을 눌러 진행합니다.
- 확장 아이콘이 있는 모든 열에 대해 이 작업을 반복해야 전체 데이터를 온전히 가져올 수 있습니다.
- 이제 닫기 및 로드(Close & Load) 옵션을 클릭합니다.
- 마지막으로 새 시트에 XML 데이터가 정리되어 나타나는 것을 확인할 수 있습니다.
1-2. 개발 도구 탭 활용하기
두 번째 방법은 개발 도구 탭을 사용해 XML 파일을 가져오는 것입니다. 개발 도구 탭은 기본적으로 리본 메뉴에 표시되지 않지만, 몇 가지 설정만 추가하면 바로 사용할 수 있습니다. 아래 단계를 참고하세요.
진행 순서:
- 우선 리본 메뉴에 개발 도구 탭이 있는지 확인합니다.
- 탭이 없다면 파일 >> 옵션을 클릭해 Excel 옵션 창을 엽니다.
- Excel 옵션 창에서 리본 사용자 지정으로 이동한 뒤, “명령 선택” 상자에서 기본 탭(Main Tabs)을 선택합니다.
- 개발 도구 항목을 체크하고 추가(Add) 버튼을 클릭합니다.
- 이후 개발 도구 탭으로 이동해 XML 그룹의 가져오기(Import)를 선택하면 XML 가져오기 창이 열립니다.
- 가져올 XML 파일을 선택하고 가져오기 버튼을 클릭합니다.
- 이 예제에서는 “File to Import.xml” 파일을 가져왔습니다.
- 그러면 데이터를 어디에 배치할지 묻는 창이 나타납니다.
- 기존 워크시트 또는 새 워크시트 중 원하는 위치를 지정할 수 있습니다. 여기서는 기존 시트의 A1 셀을 지정했습니다.
- 확인을 클릭해 진행합니다.
- 마지막으로 기존 워크시트에 아래 그림처럼 데이터가 들어온 것을 확인할 수 있습니다.
2. 웹의 XML 데이터를 엑셀로 가져오기
두 번째 방법은 웹사이트에 공개된 XML 데이터를 엑셀로 직접 가져오는 것입니다. 여기서는 Forbes의 사이트맵(Sitemap) XML을 예시로 사용합니다. 해당 링크에 접속하면 아래 그림과 같은 정보를 볼 수 있습니다.
아래 단계에 따라 웹의 XML 데이터를 엑셀로 손쉽게 가져와 보세요.
진행 순서:
- 먼저 데이터 탭에서 데이터 가져오기(Get Data)를 클릭합니다. 드롭다운 메뉴가 나타납니다.
- 기타 원본에서(From Other Sources)를 선택하면 하위 메뉴가 열립니다.
- 웹에서(From Web) 옵션을 클릭하면 “웹에서” 입력 창이 나타납니다.
- 다음으로 XML 데이터가 담긴 링크 주소를 복사합니다.
- 복사한 주소를 URL 입력란에 붙여 넣습니다.
- 확인을 클릭하면 탐색기(Navigator) 창이 열립니다.
- 탐색기 창에서 추출하려는 XML 데이터를 선택합니다.
- 본 예제에서는 왼쪽 패널의 “sitemap”을 클릭했습니다.
- 이후 로드(Load) 버튼을 클릭합니다.
- 마지막으로 새 워크시트에 페이지 주소 목록이 정리되어 나타나는 것을 확인할 수 있습니다.
마무리
지금까지 XML 데이터를 엑셀로 추출하는 2가지 쉬운 방법을 살펴보았습니다. 로컬 파일이라면 데이터 탭의 파워 쿼리 또는 개발 도구 탭의 가져오기 기능을, 웹 데이터라면 ‘웹에서’ 가져오기 기능을 활용하면 됩니다. 상황에 맞는 방법을 선택해 업무 효율을 높여 보세요. 궁금한 점이나 추가로 알고 싶은 내용이 있다면 언제든 댓글로 남겨주세요.
함께 보면 좋은 관련 글
- 엑셀에서 XML로 사용자 지정 리본 추가하는 방법
- VBA 코드로 XML을 엑셀로 변환하기 (빠른 단계별 가이드)
- 파일을 열지 않고 XML을 XLSX로 변환하는 방법