엑셀에 Power Query로 JSON 파일 불러오기가 실패할 때

 

엑셀에서 데이터를 자동화하려다 엑셀에 Power Query로 JSON 파일 불러오기가 실패할 때 좌절감을 느끼곤 합니다. 특히 보고용 대시보드를 만들어야 하는 날이라면 더 조급해집니다. 이 글은 같은 상황을 겪는 분들을 위해 작성했습니다. 원인을 빠르게 파악하고 최단 경로로 해결하도록 도와드릴게요.

---

반응형

왜 “엑셀에 Power Query로 JSON 파일 불러오기가 실패할 때”가 발생할까?

대표 오류 메시지	주요 원인	빠른 확인 포인트
We found extra characters at the end of JSON input	JSON 구문 오류(콤마, 따옴표)	메모장++ 또는 VS Code에서 JSONLint 실행
The credential type provided is not supported	인증 방식 불일치	“데이터 원본 설정”에서 자격 증명 다시 지정
Expression.Error: We cannot convert a value of type List to type Table	계층 구조가 너무 깊음	Power Query에서 Record.ToTable() 시도
DataSource.Error: Could not find file 'C:\...	잘못된 경로·파일명	경로에 한글·공백·특수문자 포함 여부
Out of Memory	JSON이 수십 MB 이상	샘플링 로드 후 단계적 병합

---

1️⃣ JSON 구조 문제부터 살펴보기

들여쓰기와 쉼표 누락 잡아내기

'—— VBA Quick Check: JSON 문법 검사 ——  
Sub QuickJSONValidate()
    Dim fPath As String, txt As String
    fPath = "C:\data\sample.json"
    txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fPath).ReadAll
    On Error Resume Next
    Dim json As Object: Set json = JsonConverter.ParseJson(txt)
    If Err.Number = 0 Then
        MsgBox "유효한 JSON!"
    Else
        MsgBox "구문 오류: " & Err.Description
    End If
End Sub

• VBA‑JSON 모듈 추가 후 실행
• 구문 오류라면 JSONLint(웹)→자동 포맷 후 재시도

계층 깊이 완화하기

• 방법 A: =Record.ToTable(Source)로 1차 평탄화 후 Expand
• 방법 B: Power Query 고급 편집기에서 단계적 Table.FromList
• 방법 C: 파일이 크면 jq CLI 도구로 미리 평탄화

---

2️⃣ 경로·권한·파일 크기 이슈 해결

경로와 접근 권한 점검

1. 파일 이름에 공백·한글·특수문자 제거
2. 네트워크 드라이브라면 UNC(\\Server\Share) 대신 로컬 복사
3. “데이터 > 데이터 원본 설정”에서 캐시 자격 증명 삭제 후 재연결

대용량 JSON 스트리밍 로드

'—— VBA Stream Load 예시 ——  
Sub StreamLoadBigJSON()
    Dim qt As QueryTable
    Dim url As String: url = "https://api.example.com/huge.json"
    Set qt = Worksheets(1).QueryTables.Add(Connection:="URL;" & url, Destination:=Range("A1"))
    qt.WebSelectionType = xlAllTables
    qt.Refresh BackgroundQuery:=False
End Sub

• URL JSON은 Power Query 대신 QueryTable로 1차 수집 → 시트 저장
• 이후 Power Query에서 “시트”를 가져오면 메모리 오류 완화

---

3️⃣ Power Query 고급 편집기로 오류 단계 추적

단계별 Column Preview 켜기

• 홈 > 미리 보기 열 활성화 → 어느 단계에서 Error 셀이 생성되는지 확인
• 오류 단계에서 try…otherwise 구문 삽입해 예외 처리

  = Table.TransformColumns(Source,
    {{"Amount", each try Number.FromText(_) otherwise 0, type number}})

인코딩 강제 지정

let
    Source = Json.Document(File.Contents("C:\data\kor.json"),1252)
in
    Source

• UTF‑8로 저장되지 않은 파일은 코드 페이지 값(949, 65001 등) 명시

---

4️⃣ JSON → Excel 자동화 전용 VBA 매크로 (완전 우회)

대량 작업이 반복되면 Power Query 대신 매크로를 돌려놓는 편이 안전합니다.

'—— VBA: JSON → Table → XLSX ——  
Sub ImportJSONViaVBA()
    Const fPath = "C:\data\sales.json"
    Dim txt As String: txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fPath).ReadAll
    Dim json As Object: Set json = JsonConverter.ParseJson(txt)

    Dim arr(), r As Long, c As Long, key
    ReDim arr(1 To json.Count, 1 To json(1).Count)

    For Each key In json(1).Keys: c = c + 1: arr(0 + 1, c) = key: Next key
    For Each itm In json: r = r + 1: c = 0
        For Each key In itm.Keys: c = c + 1: arr(r + 1, c) = itm(key): Next key
    Next itm

    With Sheet1
        .Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
        .ListObjects.Add xlSrcRange, .Range("A1").CurrentRegion, , xlYes
    End With
End Sub

• VBA‑JSON 모듈 필요
• 여러 파일을 순회하려면 Dir() 루프 추가

---

5️⃣ 자주 묻는 Q&A

질문	답변
“Power Query 인증 오류인데 로컬 파일인데요?”	회사 정책으로 ‘불러오기’가 네트워크 드라이브로 간주될 때 발생. 로컬 복사 후 시도
“테이블 변환 후 줄이 사라져요”	Expand 시 null 행 필터링 확인. 기본값 Remove null 해제
“필드가 256개 넘어가니 잘려요”	고급 편집기 Options → Allow 16k columns 체크

---

요약 & 실전 팁

엑셀로 자동 리포트를 만드는 길목에서 엑셀에 Power Query로 JSON 파일 불러오기가 실패할 때는 대체로 ① JSON 구조 오류, ② 경로·권한, ③ 대용량·메모리 한계, ④ 단계별 변환 실수 중 하나였습니다. 원인을 표로 먼저 식별하고, 고급 편집기에서 단계별 오류를 잡거나 VBA로 우회하면 95 % 이상 해결됩니다.

속성별 체크리스트

• 🔍 JSONLint로 구문 검증 후 재시도
• 🔑 데이터 원본 자격 증명 초기화
• ⚙️ 파일 크면 Stream Load → 시트 → Query 패턴
• 🛠️ 필요 시 try…otherwise 로 오류 줄 보호

마지막으로, 자동화가 완성되면 템플릿으로 저장해 재작업 시간을 0으로 만드세요. 이렇게 하면 다음에도 엑셀에 Power Query로 JSON 파일 불러오기가 실패할 때 두려워할 일이 없습니다.