엑셀 VBA로 도서 관리 : ISBN이용 네이버 OpenAPI 도서정보

갖고 있는 책이 좀 많다보니 이미 가지고 있는데 바보같이 같은 책을 또 사는 일이 몇번 있었다. 안되겠다 싶어 갖고 있는 모든 책을 관리해야 겠다는 생각을 하게 되었다. 어떻게 하지? 엑셀로 다 정리를 하면 되겠지. 우선 ISBN을 다 스캔하기로 했다.

핸드폰 무료 바코드 스캔앱들 중 괜찮은 앱으로 스캔을 해봤다. 오류가 너무 많았다. 가장 큰 문제는 핸드폰을 가져다 대면 알아서 스캔하는 방식이 문제였다. 아직 포커스가 맞지 않았는데도 스캔해버리고 다른 책으로 옮길때 또 스캔하고... 문제가 많았다. 안되겠다 싶어 바코드 스캐너를 구입했다. 가격도 18,000원. 적당하다. 책의 ISBN 바코드에 대고 총 쏘듯이 버튼을 누르니 오류도 거의 없이 스캔을 빨리 할 수 있어 너무 좋았다.

이제 엑셀 파일에 ISBN은 어느 정도 정리가 된거 같다. ISBN 몇개를 네이버 책 사이트에서 검색해보니 정보도 검색이 잘 된다. 저 정보만 내 엑셀 파일로 가져오면 되는데...
공부만 좀 더 해봐야지.

도서 ISBN 스캔을 위해 스캐너를 샀다

1. 처음에는 웹 크롤링 방법으로 코딩을 따라함
웹 크롤링을 배워보기로 했다. 코딩을 따라 해보는데 많이 어렵다. 저걸 갖고 내가 하고자 하는 네이버 책 사이트 웹페이지를 내가 분석해서 코딩해야 하는거야?
우선 엑셀장인 촬스님의 유튜브들 중 관련된 몇편을 보면서 따라했다. 그다음 검색해보니 ISBN으로 검색해서 책정보를 가져오는 잔마왕님의 유튜브를 발견했다. 음. 그런데 내가 원하는 딱 그건 아니었다.
잔마왕님의 경우 네이버(search.naver.com)에서 검색한 결과를 갖고 했다.

. 나는 이 분의 동영상을 보기 전부터 네이버책 사이트가 따로 있으니 이곳에서 정보를 가져오고 싶었다.
. 내가 자주 이용하는 알라딘에도 isbn 검색 페이지가 있었다.
이왕이면 내가 자주 가는 알라딘 페이지를 이용해서 도서 정보를 얻고 싶었다.
. 하지만 엑셀장인 촬스님의 유튜브를 봐도 잔머리 대마왕님의 웹 크롤링 블로그를 봐도 웹 페이지에서 F12를 눌러 나오는 개발자도구를 이용해 웹페이지의 Class와 각 Tag를 잘 분석해내는 나의 능력이 중요하다는 걸 깨달았다.
결국 나는 페이지를 분석할 수준이 안되어 웹 크롤링해서 책정보를 가져오는 것은 포기했다.

2. Open API를 이용하는 것이 좀 더 쉽겠다는 생각을 하게 됨
정형화된 포맷으로 데이터를 리턴해 주기 때문에 이 부분만 잘 공부하면 될것 같음.
게다가 잔마왕님께서 예를 들어주신 블로그 내용이 내가 하고자 하는 것과 딱 맞음.

. 네이버에 OpenAPI 신청을 하자.
OpenAPI 신청 및 사용방법은 별도의 포스팅을 올렸으니 참고하자( 네이버 OpenAPI등록방법 보러가기 )
. 네이버 OpenAPI의 출력 포맷은 두가지이다. XML과 JSON 형태. 나는 XML방식으로 했다. (딴거 없다. 잔마왕님이 그걸로 하셨다.)

3. 이슈를 정리, 해결해 보자.
강좌 올려주신 엑셀에서는 문제가 없었는데 내가 갖고 있는 900여개의 isbn으로 돌려보니 몇가지 이슈가 발견되었고 개선이 필요한 점도 생겼다.

3-1. 10개 가져오고 수십개를 건너뛰는 현상 발생
첨에 해보니 10개정도 정상적으로 가져오고 그 뒤부터 수십개를 그냥 건너뛴다. 그리고 또 10개 가져오고 또 반복.
음. 왜 그럴까? 정상일때는 Status가 200이었지만 가져오지 못한 경우 Ohttp.Status를 찍어보니 429 였다.
네이버 OpenAPI 공통오류 페이지에서 오류코드 429를 찾아보니 공지사항이 연결되어 있고 원인이 나와 있었다. 2018년부터 초당 10건으로 제한을 했다고 나온다.
Loop 돌릴때 Delay를 줘야 겠구나. 인터넷에 검색해보니 1초 이하 시간으로 지연 해주는 VBA built-in 함수가 없는 모양이다. 아래의 소스가 있길래 적용하였고 WaitFor함수를 메인 함수에서 api 호출때마다 0.1초 지연을 하니 모든 행을 정상적으로 가져왔다.

Sub WaitFor(NumOfSeconds As Single)
    Dim SngSec As Single
    SngSec = Timer + NumOfSeconds

    Do While Timer < SngSec
        DoEvents
   Loop
End Sub

3-2. 책을 새로 살때마다 전체 ISBN을 다 검색해야 하나?
0.1초의 Delay를 넣으니 900개 정도의 ISBN을 읽으면서 책정보를 가져오는데 5분 이상 걸리는 것 같다. 책을 살때마다  전체를 매번 돌린다고? 안되겠다. 버튼을 하나 더 만들자.

왼쪽 버튼 : 처음부터 끝까지 ISBN을 Loop 돌려 책 정보를 가져옴.
오른쪽 버튼 : 책(ISBN)을 추가하고 커서가 위치한 곳 부터 끝까지 Loop 돌려 책 정보를 가져옴.

잔마왕 님의 소스를 그대로 사용하되 별도의 함수 Call_OpenAPI 로 뺐다. 두개의 버튼에서 이 함수를 호출하되 출발 행만 파라미터로 넘기도록 수정했다.
아래는 왼쪽 버튼을 눌렀을때 수행. ISBN의 첫 행은 3부터 시작하니 3을 넘기면 된다.

Sub Scan_ISBN_All()

    ' 모든 ISBN을 스캔하여 채운다.
    ' RowNum 3부터 시작
    Call Call_OpenAPI(3)

End Sub

아래는 오른쪽 버튼을 눌렀을때 수행. 새로 추가한 ISBN에 커서를 놓고 그 셀의 행(ActiveCell.Row)를 넘기면 된다.

Sub Scan_ISBN_Selection()

    ' 선택한 ISBN부터 끝까지 스캔한다.
    ' 선택한 Cell이 A열이 아닌 경우 종료
    If ActiveCell.Column <> 1 Then
        MsgBox ("ISBN 코드가 있는 A열을 선택해주세요")
        Exit Sub
    End If
    
    Call Call_OpenAPI(ActiveCell.Row)

End Sub

3-3. ISBN으로 검색이 안되는 경우가 있다. 정상적으로 가져온 경우와 차별하기 위해 색을 넣자.
책에는 분명히 ISBN이 찍혀 있는데도 책정보를 못 가져오는 경우들이 있었다.
이런 경우 정상적으로 책정보를 가져온 것과 차별하기 위해 ISBN셀에 색을 넣었다.
. 외국 서적의 경우 네이버책에서 조회가 안되는 경우들이 있다.
. 국내서적인데 네이버책에서 조회가 안되지만 국립중앙도서관에서는 조회가 되는 경우가 있다.
. 네이버책, 국립중앙도서관에서 모두 조회가 안되는 ISBN도 있다. (뭐야 이C~~)

Range("A" & i).Interior.Color = 65535

3-4. 하나의 ISBN에도 두개 이상의 책이 존재하는 경우가 있다.
아래와 같이 황석영의 모랫말 아이들은 하나의 ISBN에 두개의 책 정보가 나왔다.
이런 경우는 어느 정보를 가져다 쓸것인지 정해야 한다. 몇번 테스트해보니 최근 것이 먼저 나오는 걸로 보인다. 최근 것을 가져오도록 변경하면 될 것 같다. 현재 소스에 이건 반영해 놓지 않은 상태이다.

<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
  <channel>
    <title>Naver Open API - book ::'9788982813542'</title>
    <link>https://search.naver.com</link>
    <description>Naver Search Result</description>
    <lastBuildDate>Wed, 26 Jan 2022 18:07:49 +0900</lastBuildDate>
    <total>2</total>
    <start>1</start>
    <display>2</display>
    <item>
      <title>모랫말 아이들</title>
      <link>http://book.naver.com/bookdb/book_detail.php?bid=64788</link>
      <image>https://bookthumb-phinf.pstatic.net/cover/000/647/00064788.jpg?type=m1&amp;udate=20130628</image>
      <author>황석영|김세현</author>
      <price>8000</price>
      <discount></discount>
      <publisher>문학동네</publisher>
      <pubdate>20010120</pubdate>
      <isbn>8982813543 &lt;b&gt;9788982813542&lt;/b&gt;</isbn>
      <description>&amp;lt;장길산&amp;gt;, &amp;lt;장산곶매&amp;gt;의 저자 황석영의 어른을 위한 동화. 전쟁 직후 저자의 유년시절을 바탕으로 쓴 글이다. 먼데서 혼자 흘러들어와 꼼배라는 별명이 붙은 거지 춘근이 얘기 등 유년시절의 추억들을 10가지 작은 주제들로 나눠 담았다.</description>
    </item>
    <item>
      <title>모랫말 아이들</title>
      <link>http://book.naver.com/bookdb/book_detail.php?bid=1446848</link>
      <image></image><author>황석영</author><price>6500</price>
      <discount></discount>
      <publisher>문학동네</publisher>
      <pubdate>2001</pubdate>
      <isbn>8982813543 &lt;b&gt;9788982813542&lt;/b&gt;</isbn>
      <description></description>
    </item>
  </channel>
</rss>

3-5. 내가 관리하는 도서 정보에 도서의 페이지 수가 있으면 좋겠다.
도서 관리 차원에서 엑셀에 페이지 수가 있으면 책이 어느 두께인지 알수 있어 좋을 것 같다. 하지만 네이버책 OpenAPI에서 출력하는 항목에 페이지 수가 없다.
국립중앙도서관의 OpenAPI에서는 페이지 수를 제공하고 있었다.
네이버 책 : 제공 항목 수 17개
국립중앙도서관 : 제공 항목 수 32개
국립중앙도서관이 훨씬 더 많은 정보를 제공하고 있다.
결론. 좀 더 나은 도서 정보를 관리하려면 네이버책 보다는 국립중앙도서관 OpenAPI를 이용하는게 좋겠다.
다음에는 국립중앙도서관 OpenAPI를 이용한 엑셀을 만들어야 겠다.

아래의 소스가 OpenAPI 핵심이다. 내가 아주 조금 바꿨다. 출처 : 잔머리 대마왕님의 블로그

Sub Call_OpenAPI(RowNum As Integer)

    Dim strURL As String
    Dim strR As String
    Dim Ohttp As WinHttpRequest
    Dim nodeList As IXMLDOMNodeList
    Dim nodeCell As IXMLDOMNode
    Dim nodeChild As IXMLDOMNode
    Dim objXML As DOMDocument60
    
    Dim ISBN_N As String
    Dim i As Integer

    i = RowNum
    Do While Range("A" & i) <> ""
        ISBN_N = Range("A" & i).Value
        
        strURL = "https://openapi.naver.com/v1/search/book.xml?query="
        Set Ohttp = New WinHttpRequest
        Ohttp.Open "GET", strURL & ISBN_N, False
        '아래에서 Client-ID와 Client-Secret는 본인 것으로 변경해야 합니다.
        Ohttp.SetRequestHeader "X-Naver-Client-Id", "XXXXClient-IDXXXX"
        Ohttp.SetRequestHeader "X-Naver-Client-Secret", "XXXXClient-SecretXXXX"
        
        Ohttp.Send
        
        '0.1초 대기 : 네이버에서 2018년부터 초당 10건만 리턴하는 걸로 변경했다.
        'Application.Wait (Now + TimeValue("0:00:01"))
        Call WaitFor(0.1)
        
        If Ohttp.Status = 200 Then
            Set objXML = New DOMDocument60
            
            'Debug.Print Ohttp.ResponseText
            objXML.LoadXML (Ohttp.ResponseText)
            
            'rss > channel > item > title/author/price/publisher/pubdate
            Set nodeList = objXML.SelectNodes("rss/channel/item")
            For Each nodeCell In nodeList
                For Each nodeChild In nodeCell.ChildNodes
                    If nodeChild.nodeName = "title" Then
                        ' Change Color to yellow
                        Range("A" & i).Interior.Color = 65535
                        Range("B" & i).Value = nodeChild.Text
                    ElseIf nodeChild.nodeName = "author" Then
                        Range("C" & i).Value = nodeChild.Text
                    ElseIf nodeChild.nodeName = "price" Then
                        Range("D" & i).Value = nodeChild.Text
                    ElseIf nodeChild.nodeName = "publisher" Then
                        Range("E" & i).Value = nodeChild.Text
                    ElseIf nodeChild.nodeName = "pubdate" Then
                        Range("F" & i).Value = nodeChild.Text
                    End If
                Next nodeChild
            Next nodeCell
        Else
            Debug.Print ISBN_N; Ohttp.Status
        End If
        
        i = i + 1
    Loop
End Sub

 

아래에 엑셀파일을 첨부합니다. 이 파일에는 ISBN코드 약 500여개가 있으니 다양한 테스트가 가능 할 겁니다. 바로 사용할 수 없습니다. 네이버 OpenAPI등록하시고 받은 Client-ID, Client-Secret를 소스 코드에 반영하셔야 합니다. 이 엑셀 파일은 공부를 넘어 제가 실제로 사용하기 위해 만든 파일이니 책을 관리 하시고자 한다면 도움이 될거라 생각합니다.. 혹시라도 제가 테스트한 부분 외에 추가하시거나 더 좋게 개선하신다면 공유해주세요. 유용하게 사용하겠습니다.

books_openAPI_Naver_20220317_BlogUpload.xlsm

0.04MB

* 관련 글
도서정보 검색 네이버 OpenAPI 등록하기