JSON 파싱 함수 :: ParseJSON 명령문 사용법

JSON 데이터를 파싱하는 ParseJSON 명령문 사용법 및 동작원리를 살펴봅니다.

홈페이지 » JSON 파싱 함수 :: ParseJSON 명령문 사용법

JSON 파싱 함수 :: ParseJSON 명령문 사용법 총정리

엑셀 ParseJSON 명령문 목차 바로가기
요약

JSON 데이터에서 지정한 필드의 데이터만 선택하여 추출하는 엑셀 사용자 지정 함수입니다.

패치노트
  • 2020.11.17
    값이 숫자이나 0으로 시작하는 경우에는 텍스트 형태로 반환합니다.
명령문 구문
= ParseJSON ( JSON데이터, 추출할필드, [1열머리글], [제외할문자열] )
사용된 인수 및 변수 알아보기
인수 설명
JSON데이터
[String]
파싱할 JSON 데이터입니다.
추출할필드
[String]
JSON 데이터에서 추출할 필드명입니다. 여러개일 경우 쉼표(,)로 구분하여 입력합니다.
1열머릿글
[String, 선택인수]
결과값으로 반환될 배열 첫번째 열에 추가할 임의의 머릿글입니다. 기본값은 NULL 입니다.
제외할문자열
[String, 선택인수]
JSON에서 파싱 된 데이터 중 불필요한 문자열 (e.g. HTML 태그, <b>,<em>,<strong> 등.. 또는 불필요한 머릿글) 을 제거합니다. 기본값은 NULL 입니다.

예제파일 다운로드

상세 설명

JSON 데이터에서 특정 필드를 지정하여 필요한 데이터만 추출하여 배열로 반환하는 엑셀 JSON 파싱 함수입니다. 결과값으로 배열을 반환합니다. 온전한 형태의 JSON 데이터에서만 동작하며 JSON 항목 구분기호가 누락된 불완전한 데이터일 경우 아래첨자관련 컴파일 오류가 출력되거나 옳지 않은 형태로 데이터가 추출될 수 있습니다.

필요에따라 [1열머릿글] 및 [제외할문자열] 인수를 입력하여 다양한 형태로 JSON 데이터를 추출할 수 있습니다.

JSON 파싱 기본 형태

=ParseJSON(JSON데이터, "주소지,건물명,가격")
주소지 건물명 가격
서울시 강남구 1234 삼성 래미안 <strong>3억</strong> 5천
서울시 강남구 1394 힐스테이트 <strong>4억</strong>
서울시 강남구 3920 e편한세상 7천
서울시 강남구 2020 자이 아파트 <strong>5억</strong>

1열머릿글을 추가하여 JSON 데이터 파싱 (강남구 머릿글 추가)

=ParseJSON(JSON데이터, "주소지,건물명,가격", "강남구")
머릿글 주소지 건물명 가격
강남구 서울시 강남구 1234 삼성 래미안 <strong>3억</strong> 5천
강남구 서울시 강남구 1394 힐스테이트 <strong>4억</strong>
강남구 서울시 강남구 3920 e편한세상 7천
강남구 서울시 강남구 2020 자이 아파트 <strong>5억</strong>

제외할 문자열을 지정하여 JSON 데이터 추출 (<Strong> 태그 제거)

=ParseJSON(JSON데이터, "주소지,건물명,가격",,"<strong>,</strong>")
주소지 건물명 가격
서울시 강남구 1234 삼성 래미안 3억 5천
서울시 강남구 1394 힐스테이트 4억
서울시 강남구 3920 e편한세상 7천
서울시 강남구 2020 자이 아파트 5억
실전 사용 예제

JSON 데이터 예제:

[
{
color: "red",
value: "#f00"
},
{
color: "green",
value: "#0f0"
},
{
color: "blue",
value: "#00f"
},
{
color: "cyan",
value: "#0ff"
},
{
color: "magenta",
value: "#f0f"
},
{
color: "yellow",
value: "#ff0"
},
{
color: "black",
value: "#000"
}
]
  1. JSON 데이터에서 color 필드만 배열로 반환하기
    =ParseJSON(JSON데이터, "color")
  2. JSON 데이터에서 color,value 필드를 반환하면서 "#" 기호를 제거하기
    =ParseJSON(JSON데이터,"color,value",,"#")

JSON데이터 파싱 함수, ParseJSON 명령문 동작원리

ParseJSON 명령문 전체 코드
Function ParseJSON(strJSON, strToParse, Optional strID, Optional strToRemove) As Variant
 
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ ParseJSON 함수
'▶ strJSON 데이터에서 선택한 데이터 값만 추출합니다.
'▶ 인수 설명
'_____________strJSON            : JSON 데이터입니다.
'_____________strToParse        : JSON 추출할 데이터 필드명입니다. 쉼표(,)로 구분하여 입력합니다.
'_____________strID                 : 추출한 데이터 배열의 열에 추가할 ID 입니다. (선택인수)
'_____________strToRemove   : 추출한 데이터에서 제거할 문자열입니다. 쉼표(,)로 구분하여 입력합니다.
'▶ 사용 예제
'Dim v As Variant
'v = ParseJSON(JsonData, "Date, Name, Item")
'###############################################################
 
'----------------------------------------------------
'변수 설정
'----------------------------------------------------
Dim vaToParse As Variant: Dim vToParse As Variant
Dim vaToRemove As Variant: Dim vToRemove As Variant
Dim lngStart As Long
Dim objItm As Variant: Dim strItm As String: Dim tmpItm As String
Dim itmCnt As Long
Dim i As Long: Dim r As Long: Dim c As Long
Dim dicItm As Object
Dim vaItm As Variant: Dim vaItems As Variant: Dim vaReturn As Variant
Dim iCol As Long: Dim maxCol As Long: Dim j As Long
 
Set dicItm = CreateObject("Scripting.Dictionary")
 
'----------------------------------------------------
'JSON 쿼리 분할
'----------------------------------------------------
vaToParse = Split(strToParse, ",")
If Not IsMissing(strToRemove) Then vaToRemove = Split(strToRemove, ",")
 
lngStart = InStr(1, strJSON, "[")
strJSON = Right(strJSON, Len(strJSON) - lngStart)
 
objItm = Split(strJSON, "{")
itmCnt = UBound(objItm)
 
For i = 1 To itmCnt
    strItm = Split(objItm(i), "}")(0)
    strItm = Trim(strItm)
    If Left(strItm, 1) = """" Then strItm = Right(strItm, Len(strItm) - 1)
    If Right(strItm, 1) = """" Then strItm = Left(strItm, Len(strItm) - 1)
     iCol = Len(strItm) - Len(Replace(strItm, ":", ""))
    If iCol > maxCol Then maxCol = iCol
 
    If Not IsMissing(strID) And strID <> "" Then
        ReDim vaItm(0 To iCol)
        vaItm(0) = strID: j = 1
    Else
        ReDim vaItm(0 To iCol - 1)
        j = 0
    End If
 
    On Error Resume Next
    For Each vToParse In vaToParse
        If InStr(strItm, Trim(vToParse) & """:") > 0 Then
            tmpItm = Split(strItm, Trim(vToParse) & """:")(1)
            tmpItm = Split(tmpItm, ",""")(0)
            tmpItm = Trim(tmpItm)
            If Left(tmpItm, 1) = """" Then tmpItm = Right(tmpItm, Len(tmpItm) - 1)
            If Right(tmpItm, 1) = """" Then tmpItm = Left(tmpItm, Len(tmpItm) - 1)
            tmpItm = Replace(tmpItm, "< ", "") If Not IsMissing(strToRemove) Then For Each vToRemove In vaToRemove tmpItm = Replace(tmpItm, Trim(vToRemove), "") Next End If vaItm(j) = tmpItm ElseIf InStr(strItm, Trim(vToParse) & ":") > 0 Then
            tmpItm = Split(strItm, Trim(vToParse) & ":")(1)
            tmpItm = Split(tmpItm, ",")(0)
            tmpItm = Replace(tmpItm, vbTab, "")
            tmpItm = Trim(tmpItm)
            Do While Right(tmpItm, 1) = vbLf
                tmpItm = Left(tmpItm, Len(tmpItm) - 1)
            Loop
            If Left(tmpItm, 1) = """" Then tmpItm = Right(tmpItm, Len(tmpItm) - 1)
            If Right(tmpItm, 1) = """" Then tmpItm = Left(tmpItm, Len(tmpItm) - 1)
            tmpItm = Replace(tmpItm, "< ", "") If Not IsMissing(strToRemove) Then For Each vToRemove In vaToRemove tmpItm = Replace(tmpItm, Trim(vToRemove), "") Next End If vaItm(j) = tmpItm End If j = j + 1 Next On Error GoTo 0 dicItm.Add i, Array(vaItm, 1) Next '---------------------------------------------------- 'Dictionary -> 배열 변환
'----------------------------------------------------
r = dicItm.Count
c = UBound(vaToParse) + 1
If Not IsMissing(strID) And strID <> "" Then c = c + 1
 
If r = 0 Then ParseJSON = ""
 
vaItems = dicItm.Items
 
ReDim vaReturn(1 To r, 1 To c)
 
On Error Resume Next
For i = 0 To r - 1
    For j = 0 To c - 1
        tmpItm = vaItems(i)(0)(j)
        If IsNumeric(tmpItm) And Left(tmpItm, 1) <> 0 Then vaReturn(i + 1, j + 1) = CDbl(tmpItm) Else vaReturn(i + 1, j + 1) = tmpItm
    Next
Next
On Error GoTo 0
 
'----------------------------------------------------
'결과값 리턴
'----------------------------------------------------
ParseJSON = vaReturn
 
End Function
명령문 동작원리 단계별 알아보기
  1. 변수 선언 및 설정
    Dim vaToParse As Variant: Dim vToParse As Variant
    Dim vaToRemove As Variant: Dim vToRemove As Variant
    Dim lngStart As Long
    Dim objItm As Variant: Dim strItm As String: Dim tmpItm As String
    Dim itmCnt As Long
    Dim i As Long: Dim r As Long: Dim c As Long
    Dim dicItm As Object
    Dim vaItm As Variant: Dim vaItems As Variant: Dim vaReturn As Variant
    Dim iCol As Long: Dim maxCol As Long: Dim j As Long
     
    Set dicItm = CreateObject("Scripting.Dictionary")
  2. JSON 데이터 1차 파싱 - 각 항목별 분리
    vaToParse = Split(strToParse, ",")
    If Not IsMissing(strToRemove) Then vaToRemove = Split(strToRemove, ",")
     
    lngStart = InStr(1, strJSON, "[")
    strJSON = Right(strJSON, Len(strJSON) - lngStart)
     
    objItm = Split(strJSON, "{")
    itmCnt = UBound(objItm)
  3. JSON 데이터 2차 파싱 - 필드 & 데이터 추출
    For i = 1 To itmCnt
        strItm = Split(objItm(i), "}")(0)
        strItm = Trim(strItm)
        If Left(strItm, 1) = """" Then strItm = Right(strItm, Len(strItm) - 1)
        If Right(strItm, 1) = """" Then strItm = Left(strItm, Len(strItm) - 1)
         iCol = Len(strItm) - Len(Replace(strItm, ":", ""))
        If iCol > maxCol Then maxCol = iCol
     
        If Not IsMissing(strID) And strID <> "" Then
            ReDim vaItm(0 To iCol)
            vaItm(0) = strID: j = 1
        Else
            ReDim vaItm(0 To iCol - 1)
            j = 0
        End If
     
        On Error Resume Next
        For Each vToParse In vaToParse
            If InStr(strItm, Trim(vToParse) & """:") > 0 Then
                tmpItm = Split(strItm, Trim(vToParse) & """:")(1)
                tmpItm = Split(tmpItm, ",""")(0)
                tmpItm = Trim(tmpItm)
                If Left(tmpItm, 1) = """" Then tmpItm = Right(tmpItm, Len(tmpItm) - 1)
                If Right(tmpItm, 1) = """" Then tmpItm = Left(tmpItm, Len(tmpItm) - 1)
                tmpItm = Replace(tmpItm, "< ", "") If Not IsMissing(strToRemove) Then For Each vToRemove In vaToRemove tmpItm = Replace(tmpItm, Trim(vToRemove), "") Next End If vaItm(j) = tmpItm ElseIf InStr(strItm, Trim(vToParse) & ":") > 0 Then
                tmpItm = Split(strItm, Trim(vToParse) & ":")(1)
                tmpItm = Split(tmpItm, ",")(0)
                tmpItm = Replace(tmpItm, vbTab, "")
                tmpItm = Trim(tmpItm)
                Do While Right(tmpItm, 1) = vbLf
                    tmpItm = Left(tmpItm, Len(tmpItm) - 1)
                Loop
                If Left(tmpItm, 1) = """" Then tmpItm = Right(tmpItm, Len(tmpItm) - 1)
                If Right(tmpItm, 1) = """" Then tmpItm = Left(tmpItm, Len(tmpItm) - 1)
                tmpItm = Replace(tmpItm, "< ", "")
                If Not IsMissing(strToRemove) Then
                    For Each vToRemove In vaToRemove
                        tmpItm = Replace(tmpItm, Trim(vToRemove), "")
                    Next
                End If
                vaItm(j) = tmpItm
            End If
            j = j + 1
        Next
        On Error GoTo 0
        dicItm.Add i, Array(vaItm, 1)
    Next
  4. Dictionary 를 배열로 변환
    r = dicItm.Count
    c = UBound(vaToParse) + 1
    If Not IsMissing(strID) And strID <> "" Then c = c + 1
     
    If r = 0 Then ParseJSON = ""
     
    vaItems = dicItm.Items
     
    ReDim vaReturn(1 To r, 1 To c)
     
    On Error Resume Next
    For i = 0 To r - 1
        For j = 0 To c - 1
            tmpItm = vaItems(i)(0)(j)
            If IsNumeric(tmpItm) And Left(tmpItm, 1) <> 0 Then vaReturn(i + 1, j + 1) = CDbl(tmpItm) Else vaReturn(i + 1, j + 1) = tmpItm
        Next
    Next
    On Error GoTo 0
  5. 결과값 리턴 후 함수 종료
    ParseJSON = vaReturn
guest
0 댓글
Inline Feedbacks
모든 댓글 보기
0
여러분의 생각을 댓글로 남겨주세요.x