이 글의 목적은 엑셀에서 외부 데이터 가져오기가 실패할 때 원인 진단과 해결 절차를 단계별로 제시하여 현장에서 바로 적용할 수 있도록 돕는 것이다.
1. 문제 정의와 증상 분류
엑셀의 외부 데이터 가져오기는 크게 Power Query(데이터 가져오기 및 변환), 기존 마법사(텍스트/CSV/ODBC/OLE DB), 연결된 통합 문서/피벗 캐시, 웹 쿼리 및 SharePoint·OneDrive 연결 등으로 나뉘며 각각의 실패 원인이 상이하다.
| 대표 증상 | 가능 원인 | 주요 점검 지점 |
|---|---|---|
| 연결 실패 또는 인증 요청 반복 | 자격 증명 불일치, 프록시/방화벽 차단, TLS/인증서 문제 | 데이터 원본 권한, 시스템 프록시, 네트워크 포트, 인증서 체인 |
| 미리 보기 로드 불가 또는 쿼리 편집기 오류 | 프라이버시 레벨 충돌, 쿼리 폴딩 불가, 시간 초과 | Power Query 프라이버시, 쿼리 단계, 제한 시간 설정 |
| CSV/텍스트 인코딩 깨짐 | 잘못된 문자 인코딩, 구분 기호/소수점 지역 설정 불일치 | 파일 인코딩, 지역 옵션, 자료 형식 자동 감지 끔/켬 |
| ODBC/OLE DB 드라이버 관련 오류 | 32/64비트 불일치, 드라이버 미설치, DSN 설정 오류 | 드라이버 버전, DSN/연결 문자열, 플랫폼 일치 |
| SharePoint/OneDrive 링크 실패 | 링크 형식 오류, 액세스 권한 부족, 동기화 지연 | URL 변환, 계정 권한, 파일 경로 길이 |
| 새로 고침 시만 실패 | 백그라운드 새로 고침 충돌, 일정 새로 고침 제한 | 연결 속성, 스케줄, 동시성 설정 |
주의 : 동일 증상이라도 원인이 복합적인 경우가 많으므로, 한 가지 조치 후 반드시 재현 테스트를 수행하고 연결 로그를 병행 확인해야 한다.
2. 10분 만에 끝내는 표준 점검 절차(체크리스트)
- 원본 주소와 접근권한 확인을 한다. 브라우저 또는 원본 툴에서 동일 계정으로 직접 접근 가능한지 확인한다.
- 엑셀 버전 및 비트 수를 확인한다. 드라이버와 플랫폼 일치 여부를 확인한다.
- 네트워크 제약을 점검한다. 프록시·VPN·방화벽 정책에서 대상 도메인/포트 허용 여부를 확인한다.
- Power Query 프라이버시 레벨을 검토한다. 서로 다른 도메인 결합 시 격리 정책 충돌 여부를 확인한다.
- 자격 증명을 초기화 후 재설정한다. 저장된 인증을 제거하고 올바른 방법으로 재로그인한다.
- 인코딩·구분 기호·지역 옵션을 명시한다. CSV/텍스트는 자동 감지를 신뢰하지 않는다.
- 쿼리 단계를 분해한다. 변환 단계마다 미리 보기로 오류 지점을 정확히 찾는다.
- 시간 제한과 페이지네이션을 설정한다. 대용량 API·DB는 배치/필터/Top N 로드를 적용한다.
- 경로·파일명·시트명 제약을 점검한다. 너무 긴 경로, 특수문자, 숨김 파일 여부를 확인한다.
- 기존 연결 캐시 및 피벗 캐시를 초기화한다. 필요 시 새로운 통합 문서에서 재구성한다.
3. 원본 유형별 핵심 해결 전략
3.1 텍스트·CSV 파일
- 인코딩을 명시한다. UTF-8, EUC-KR, ANSI 등 원본 인코딩을 파일 미리보기로 식별하고 불명확하면 파일 첫 행/특수 문자를 근거로 선택한다.
- 구분 기호를 고정한다. 쉼표, 탭, 세미콜론, 사용자 지정 구분 기호를 명시한다.
- 지역 옵션을 지정한다. 소수점 기호와 천 단위 구분이 원본과 다르면 숫자가 텍스트로 인식된다.
- 자료형 자동 감지를 끄고 단계별로 형 변환을 적용한다. 특히 날짜/시간은 원본 문화권 서식을 고려한다.
1) 데이터 > 데이터 가져오기 > 텍스트/CSV 2) 파일 선택 > 파일 원본: UTF-8 (예) 3) 구분 기호: 쉼표, 데이터 형식 감지: 없음 4) 데이터 변환 > 각 열에 형식 수동 지정 주의 : CSV 필드 내 줄바꿈이나 따옴표 포함 데이터는 Power Query의 텍스트 한정자 설정이 올바르지 않으면 열 깨짐이 발생한다.
3.2 웹(HTTP/HTTPS)·API
- 인증 방식 확인을 한다. 기본/베이식, OAuth, API Key, 쿠키 기반 등 방식에 따라 자격 증명 유형을 올바르게 선택한다.
- HTTP 프록시 환경을 점검한다. 시스템 프록시가 필요한 경우 Windows 프록시 설정과 일치시킨다.
- TLS/인증서 오류는 중간 인증서 체인 누락, 구형 TLS 제한이 원인일 수 있다. 서버·클라이언트 양측 정책을 점검한다.
- 대용량·페이징 API는 쿼리 매개변수(page, limit, offset, since 등)를 적용하여 배치 로드를 구현한다.
// Power Query - 고급 편집기 예시 let Source = Json.Document(Web.Contents("https://api.example.com/items", [ Query=[ page="1", limit="1000" ], Headers=[ Authorization="Bearer <TOKEN>" ], Timeout=#duration(0,0,90,0) ])), Data = Source[items] in Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error) 주의 : Web.Contents에 상대 경로와 동적 URL을 혼합하면 프라이버시 레벨 격리로 인해 Formula Firewall 오류가 발생할 수 있다. 루트 도메인을 고정하고 상대 경로를 매개변수화한다.
3.3 데이터베이스(ODBC/OLE DB/네이티브 커넥터)
- 플랫폼 일치가 핵심이다. 엑셀이 64비트이면 64비트 드라이버를, 32비트이면 32비트를 설치한다.
- DSN(시스템/사용자)과 연결 문자열을 정확히 구성한다. 서버, 데이터베이스, 포트, 암호화, 트러스트서버 인증을 확인한다.
- 쿼리 폴딩을 활용한다. 가능한 변환을 서버로 푸시하여 메모리 사용과 전송량을 줄인다.
- 시간 제한과 커맨드 타임아웃을 늘린다. 장시간 실행 쿼리는 분할·인덱스·필터링으로 최적화한다.
Provider=MSOLEDBSQL;Server=tcp:prod-sql.example.net,1433; Database=DW;Authentication=ActiveDirectoryPassword;User ID=reporter;Password=********; Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;주의 : 네트워크 구간에 TLS 검사(SSL Inspection)가 있는 기업 환경에서는 인증서 불일치로 연결이 중단될 수 있다. 예외 정책을 요청한다.
3.4 SharePoint·OneDrive·Teams 파일
- 링크를 브라우저 보기 URL이 아닌 파일 직접 다운로드 또는 SharePoint 루트 경로 형식으로 교정한다.
- 계정 일치가 필요하다. 엑셀의 계정과 브라우저 로그인 계정이 다르면 인증 루프가 발생한다.
- 경로 길이 제한을 피한다. 너무 긴 폴더/파일명은 동기화와 API 호출에 실패를 유발한다.
예) https://contoso.sharepoint.com/sites/BI/Shared%20Documents/Data/sales.csv 데이터 > 데이터 가져오기 > SharePoint 폴더 > 사이트 URL 입력4. Power Query 오류의 체계적 진단
4.1 프라이버시 레벨 및 Formula Firewall
서로 다른 원본을 결합할 때 조직, 공개, 비공개 등 레벨이 혼재하면 데이터가 차단된다. 가능한 한 동일 레벨로 설정하거나, 현재 통합 문서의 프라이버시 사용 옵션을 조정한다. 보안 요구가 높다면 원본 결합을 단계적으로 분리하여 중간 결과를 테이블로 캐시하고 이후 결합을 수행한다.
4.2 쿼리 단계별 분해
오류가 발생하는 단계 바로 이전으로 돌아가 미리 보기를 성공시키고, 변환을 더 세분화한다. 대용량 테이블의 경우 상위 행 유지(Keep Top Rows)로 샘플링 후 스키마를 안정화한 뒤 전체 로드를 수행한다.
4.3 쿼리 폴딩 확인
데이터베이스 원본에서 구현된 단계 보기를 통해 서버로 푸시되는지 확인한다. 폴딩이 끊기는 변환(사용자 지정 함수, 행 수준 조인 등)을 최소화한다.
4.4 매개변수화와 재사용
경로·날짜·환경 변수는 매개변수로 승격하여 개발/운영 전환 시 오류를 줄인다. 환경별 자격 증명도 매개변수로 분리한다.
5. 인증·자격 증명 문제 해결
- 데이터 > 데이터 원본 설정 > 모든 자격 증명 지우기를 실행한다.
- 원본별 권한 유형을 올바르게 선택한다. 조직 계정, 기본, 익명, 웹 API 키 등 맞춤 선택을 한다.
- 멀티 팩터가 필요한 경우 앱 암호 또는 토큰 기반 접근을 적용한다.
- 테넌트 정책으로 차단되는 경우 관리자에게 앱 허용 목록 등록을 요청한다.
주의 : 비공개 데이터와 공개 데이터를 같은 쿼리에서 결합하면 정보 유출을 막기 위해 기본적으로 차단된다. 정책 목적을 이해하고 예외 적용은 신중히 결정한다.
6. 네트워크·프록시·방화벽 진단
회사 네트워크에서는 보안 장비가 HTTP/HTTPS 트래픽을 필터링한다. 다음을 점검한다.
- 프록시 자동 스크립트(PAC) 또는 고정 프록시 사용 여부를 확인한다.
- 필요 포트(예: 443, 1433, 1521 등) 개방 여부를 네트워크 팀에 확인한다.
- 도메인 화이트리스트에 대상 원본을 추가한다.
- TLS 가시화 장비가 인증서를 재서명하는 경우 신뢰 루트 설치 및 예외를 적용한다.
:: 시스템 프록시 확인(관리자 권한 CMD) netsh winhttp show proxy
:: 프록시 내보내기/가져오기
netsh winhttp import proxy source=ie
7. 드라이버·플랫폼 불일치 해결
ODBC/OLE DB 오류는 드라이버 버전·비트수 불일치가 가장 흔하다. 다음을 확인한다.
- 엑셀 비트 수와 동일한 드라이버 설치를 한다.
- DSN은 시스템 DSN을 권장한다. 서비스 계정 실행 시 사용자 DSN은 보이지 않을 수 있다.
- 연결 문자열에 암호화 옵션과 타임아웃을 명시한다.
; ODBC .dsn 예시 [ODBC] DRIVER=ODBC Driver 18 for SQL Server SERVER=prod-sql.example.net,1433 DATABASE=DW TrustServerCertificate=No Encrypt=Yes Timeout=30 8. 성능과 안정성 튜닝
- 필요 열만 선택한다. 열 제거는 초기에 수행한다.
- 필터·집계를 서버 측으로 이동한다. 가능한 한 쿼리 폴딩을 유지한다.
- 증분 로드를 설계한다. 날짜 키를 기준으로 최신분만 로드 후 병합한다.
- 배경 새로 고침을 해제하고 수동 새로 고침으로 충돌을 줄인다.
- 캐시 크기와 임시 파일 경로의 여유 공간을 확보한다.
9. 파일 경로·권한·보안 보기(Protected View)
인터넷에서 내려받은 파일이나 신뢰할 수 없는 위치의 파일은 보호 모드에서 데이터 연결이 제한된다. 다음을 점검한다.
- 신뢰할 수 있는 위치에 파일을 배치한다.
- 보안 보기에서 차단 해제 후 다시 열기를 수행한다.
- 매크로 서명 및 파일 디지털 서명을 검토하여 경고를 줄인다.
주의 : 보안 기능을 무분별하게 해제하면 조직 보안 정책을 위반할 수 있다. 정책 범위 내에서 예외를 최소화해야 한다.
10. 오류 메시지별 원인-대응 매핑
| 오류 메시지 유형 | 주요 원인 | 즉시 대응 | 근본 조치 |
|---|---|---|---|
| 인증 실패 | 잘못된 자격 증명, 토큰 만료 | 자격 증명 지우기 후 재로그인 | SSO 구성 점검, 토큰 수명 정책 조정 |
| 접속 시간 초과 | 대용량 전송, 네트워크 지연 | 타임아웃 증가, 행 제한 | 인덱스 최적화, 증분 로드 설계 |
| 인코딩 오류 | 파일 인코딩 불일치 | 파일 원본 강제 지정 | 생산 시스템에서 인코딩 표준화 |
| 드라이버 찾을 수 없음 | 미설치·비트 불일치 | 해당 버전 드라이버 설치 | 소프트웨어 배포 정책에 추가 |
| Formula Firewall | 프라이버시 레벨 충돌 | 레벨 정렬 또는 쿼리 분리 | 데이터 도메인 설계 재구성 |
| 인증서/보안 오류 | 중간 인증서 누락, SSL 검사 | 신뢰 루트 설치 | 보안 장비 예외 정책 반영 |
11. 재현 테스트와 로그 수집
문제 재현 가능성이 낮으면 다음 절차로 로그를 확보한다.
- 새 통합 문서에서 최소 단계만으로 동일 원본을 로드한다.
- 단계별 저장 후 어느 단계에서 실패하는지 확인한다.
- 쿼리 진단 기능을 사용하여 지속 시간, 데이터 크기, 폴딩 상태를 기록한다.
12. 운영 환경 배포 체크리스트
- 매개변수와 환경 설정 파일을 분리 관리한다.
- 자격 증명은 조직 계정 또는 서비스 프린시펄을 사용한다.
- 증분 로드와 오류 재시도 규칙을 정의한다.
- 모니터링 지표(성공/실패, 지연, 행 수)를 수집한다.
13. 자주 하는 실수와 예방 팁
- 브라우저에서 보이는 URL을 그대로 붙여 넣어 다운로드 링크가 아닌 경우가 많다. API 문서 또는 공유 링크 변환을 확인한다.
- CSV 자동 감지에만 의존한다. 소수점·날짜 서식이 다른 문화권 데이터는 명시 변환이 필요하다.
- 여러 원본을 무작정 병합한다. 프라이버시 정책과 폴딩이 깨진다.
- 동일 드라이버를 최신화하지 않는다. 서버 업데이트 후 클라이언트 드라이버도 일치시킨다.
14. 단계별 솔루션 예시 시나리오
시나리오 A: CSV 로드 시 숫자가 텍스트로 인식됨
- 데이터 가져오기에서 데이터 형식 감지: 없음으로 불러온다.
- 첫 100행에서 컬럼별 형식을 수동 지정한다.
- 소수점 기호가 쉼표인 경우 지역 옵션을 해당 문화권으로 설정한다.
시나리오 B: API 인증 루프
- 모든 자격 증명을 지우고 웹 API 키 또는 조직 계정을 올바르게 선택한다.
- Web.Contents 루트 URL을 상수로 두고 Query 매개변수로 세부 경로를 넘긴다.
- 프라이버시 레벨을 동일하게 맞춘다.
시나리오 C: SQL Server 연결 시간 초과
- 연결 문자열에 Connection Timeout과 Command Timeout을 확대한다.
- 서버 측 인덱스와 Where 조건으로 데이터 범위를 축소한다.
- 증분 로드 테이블을 도입한다.
15. 점검 결과 기록 템플릿
[프로젝트/파일명] 2025-10-26 원본 유형: CSV / API / DB / SharePoint 증상 요약: <예: 미리 보기 실패, 인증 루프> 재현 절차: <클릭 경로와 매개변수> 원인 가설: <프라이버시 충돌/드라이버 불일치 등> 실행 조치: <자격 증명 초기화, 인코딩 지정, 타임아웃 90초> 결과/검증: <성공/실패, 소요시간, 로드 행 수> 추가 과제: <폴딩 개선, 증분 로드 도입> FAQ
Power Query 프라이버시 레벨을 어디에서 변경하나?
데이터 > 데이터 원본 설정 > 모든 원본 보기 > 원본 선택 > 편집에서 레벨을 변경한다. 전역 설정과 파일별 설정을 구분하여 적용한다.
CSV 인코딩이 무엇인지 모를 때 어떻게 판단하나?
BOM 유무와 특수 문자 표시를 보고 UTF-8, EUC-KR 등을 추정한다. 불명확하면 Power Query에서 여러 인코딩을 시도하여 한글이 정상 표시되는 값을 채택한다.
드라이버 비트 수는 어떻게 확인하나?
엑셀의 비트 수(계정 > 엑셀 정보)와 설치된 ODBC 드라이버 관리자에서 드라이버 속성을 확인한다. 일치하지 않으면 올바른 비트 버전을 설치한다.
SharePoint 링크가 브라우저에서는 보이는데 엑셀에서는 실패한다.
보기 URL 대신 사이트 루트 경로와 문서 라이브러리 경로를 사용한다. 계정 일치와 권한 부여를 확인한다.
새로 고침 시만 실패하는 경우가 있다.
배경 새로 고침과 동시 실행이 충돌할 수 있다. 연결 속성에서 배경 새로 고침을 해제하고 수동 새로 고침 순서를 조정한다.