[Excel] Open API를 활용해 공공데이터 가져오는 법

결과물부터 보여주자면 아래와 같다.

새로고침하면

이렇게 바뀐다

 

 

---

1. 공공포털 로그인 및 활용 신청

www.data.go.kr/data/15004449/openapi.do

공공데이터 포털

공공데이터를 사용하려면 로그인한 후 포털에서 활용신청을 클릭한다.

---

활용신청이 완료되고 신청 승인을 위해 1-2시간 기다리라고 하는 경우가 있다. 

이후 승인이 됐는지 확인하고 싶은 경우엔 웹 페이지 내 인증키를 넣고 미리보기를 클릭해서 열리면 승인이 된거다.

 

신청 후 마이페이지에 보면 신청완료된게 보이고 이후 필요한건 두가지다.

1) 인증키(서비스키) 2)워드 내 REST(Url) 

 

인증키는 인터넷에 있고

상세설명에 참고문서 워드를 다운받는다.

워드 내 요청이라고 검색하면 아래 REST(Url)가 존재한다.

인증키라고 써있는 부분에 내 인증키를 복붙해준다.

 

http://apis.data.go.kr/1262000/AccidentService/getAccidentList?ServiceKey=abcdefg&isoCode1=CHN

이런식으로 생겼다.

---

2. 엑셀 데이터 가져오기

이제 이 url을 엑셀 내 데이터>데이터 가져오기>기타 원본에서> 웹에서에 붙여준다. 

 

잘따라했는데 안되는 경우

1) 하루가 지나서 그렇다. 공공데이터 포털은 딱 하루만 열어주니까 사이트 내에서 연장하기를 신청해야한다.

2) 신청하고 승인이 아직나지 않아서

3) 웹사이트 잘못붙여넣어서 그럴 수 있으니 웹 내 미리보기해서 열린 웹주소를 긁어와보자.

---

엑셀에서 body를 누르고 데이터 변환을 눌러준다.

---

'확장하기'버튼 누르고 접두사로 사용 '언체크'한 후 '확인'을 클릭한다.

---

그러면 아래처럼 여러 열이 한번에 주르륵 뜬다.

다시 '확인'을 클릭한다.

---

그러면 아래와 같이 데이터를 가져올 수 있다. 

 

 

 

---

3. 데이터 유효성 검사

새로운 시트에 요렇게 하나정도만 테이블 써주고 데이터> 데이터 유효성 검사 클릭

내용 정리한 데이터 시트지 혹은 많으면 복붙해서 다른 시트에 열 것

원본 범위 설정 부분은 다른 시트에서 아래 그림처럼 드래그

---

다음 해야할 일은 삽입> 표  확인 클릭  

이걸 하는 이유는 파워쿼리를 쉽게쓰기 위해서다.

 

---

 

이제 쿼리로 불러와야 한다. 그 후 약간의 수정 과정을 거친다.

---

4. 함수로 변경 (고급 편집기 사용)

추가해준 부분을 아래 형관펜으로 표기해뒀다. 

하나 더 중요한건 앞에 사이트에서 계속 변경됐으면 하는 부분은

삭제 후 뒷부분에 이어서 &Text.Upper라는 코드를 이용해 줘야 한다.

 

let info_countr = (country) =>

let

    원본 = Xml.Tables(Web.Contents("http://apis.data.go.kr/1262000/AccidentService/getAccidentList?ServiceKey=6Y22zMihdCFMNYzSPWdfmishHHK2AGuH71RH1gp%2FKZVEWwFKJZxko8jwn3rKsEGbMZv88rAMWbIxw3nusf1dNg%3D%3D&isoCode1=CHN"&Text.Upper(country))),

    Table1 = 원본{1}[Table],

    #"변경된 유형" = Table.TransformColumnTypes(Table1,{{"numOfRows", Int64.Type}, {"pageNo", Int64.Type}, {"totalCount", Int64.Type}}),

    #"확장된 items" = Table.ExpandTableColumn(#"변경된 유형", "items", {"item"}, {"items.item"}),

    #"확장된 items.item" = Table.ExpandTableColumn(#"확장된 items", "items.item", {"continent", "ename", "id", "imgUrl", "imgUrl2", "name", "news", "wrtDt"}, {"items.item.continent", "items.item.ename", "items.item.id", "items.item.imgUrl", "items.item.imgUrl2", "items.item.name", "items.item.news", "items.item.wrtDt"})

in

    #"확장된 items.item"

in info_countr

 

 

 

 

이렇게 추가해준 뒤 완료를 클릭한다.

---

5. 사용자 지정함수 호출

이후 열추가>사용자지정함수호출> 확인 클릭한다.

---

이후 body에서 확장 누른 후 접두사 사용은 언체크 해주고 확인을 누른다.

---

6. 결과물 로드하기

이제 로드를 해준다.

기존 워크 시트는 GHA 밑에 남는 공간에 테이블 어디쯤 만들어줄까 묻는 것

$A$6정도에 넣어보자.

---

<결과물>

새로고침하면

이렇게 바뀐다