강좌 > 엑셀에서 Database사용하기 > 2강 - Microsoft Query를 이용하여 Database에 연결하기

2강 - Microsoft Query를 이용하여 Database에 연결하기

2021-05-03 / 25 댓글

1강은 개발자가 아니더라도 읽어 나가는데 부담이 없었겠지만 지금부터는 전문용어 투성이다. 개발자가 아니라면 어려운 시간이 되겠지만 약간만 배우만 업무에 도움이 될 수 있다.

뭔지 모르겠지만 일단 Database에 한번 연결해 보자. 프로그램을 짜서 연결하는 방식이 아닌 데이터베이스에 연결할 수 있는 프로그램을 이용하는 것이다. 이글 에 첨부된 엑셀파일을 다운로드 받아서 연 다음 다음 그림과 같이 따라해 본다.

1. 데이터 가져오기

데이터 > Microsoft Query 를 선택(엑셀 2013기준임, 버전에 따라 그림이 약간 다를 수 있다)

msquery01

 

2. 데이터 원본 선택

팝업 창에서 Excel Files* 를 선택하고 "확인"버튼을 누른다.

msquery02

 

3. 엑셀파일 선택

방금 다운로드 받은 엑셀 파일을 선택한다. c드라이브 밑에서 찾아서 안보이면 다른 드라이브에 있을 수 있으므로 아래 그림에서 드라이브를 변경해 본다.

msquery03

 

4. 테이블 옵션 확인

엑셀파일을 선택하면 왼쪽에 엑셀 Sheet의 이름이 목록으로 나온다. 맨 끝에 "$"붙어 있다. Microsoft Query내부적으로 그렇게 인식한다고만 알아두자. 만약 왼쪽에 아무것도 보이지 않는다면 아래 옵션 버튼을 누른다. 테이블 옵션에서 "시스템 테이블(S)"가 체크되어 있지 않다면 체크하고 확인 버튼을 누르면 Sheet의 이름이 보일 것이다.

msquery04

 

5. 쿼리 마법사 - 열 선택

아래에서 "사원정보$"를 선택하고 가운데 보이는 오른쪽 화살표가 있는 버튼 (">")을 누른다.

msquery05

 

6. 쿼리 마법사 - 데이터 필터

데이터 필터는 데이터를 가져오는 조건을 정한다. 일단, 아무것도 하지말고 다음 버튼을 누른다.

msquery06

 

7. 쿼리 마법사 - 정렬 순서 지정

데이터를 정렬하는 방법을 지정하는 팝업 화면이다. 일단, 아무것도 하지말고 다음버튼을 누른다.

msquery07

 

8. 쿼리 마법사 - 마침

"Microsoft Query에서 데이터 보기 또는 쿼리 편집" 선택하고 "마침"버튼을 누른다.

msquery08

 

9. 엑셀Sheet의 내용을 Query하기

엑셀파일의 "사원정보" Sheet의 내용을 전부 가져왔다. 엑셀 Sheet의 내용을 Database로 인식하고 내용을 전부 가져온(Query해 온)것이다.

msquery09

 

10. SQL문 확인

위 자료는 SQL이라는 특수한 형식의 프로그램으로 가져온 것이다. 메뉴에서 "SQL"버튼을 누른다. 아래 그림과 같은 내용이 나온다. 이것이 SQL이라고 하는 것이다.

msquery10

 

11. SQL문 편집

위의 내용은 쓸데 없는 내용이 너무 많이 들어가 있다. 다 지우고 아래와 같이 입력하고 확인버튼을 누른다. 부서가 영업팀인 사원정보만 가져오는 SQL이다.

SELECT * FROM 사원정보$ 
 WHERE 부서 = '영업팀'

msquery11

msquery12

 

무작정 데이터베이스를 연결해보았는데... MS-SQL, Oracle, MySQL등은 자체적으로 Database에 직접 연결하여 Data를 활용할 수 있는 툴을 제공하고 있지만 엑셀 파일을 데이터베이스로 이용하는 경우에는 Microsoft Query를 이용하여 Data를 조회하고 활용할 수 있다. VBA 프로그램을 작성해서 Database에 접근할 때 디버깅을 위해 Microsoft Query를 이용하면 편리하다.

SQL를 모른다면 데이터베이스를 사용하기 위해서는 다음 개념 정도는 간단히 이해하고 넘어가기 바라며, 더 궁금한 점이 있다면 구글링으로 해결할 수 있을 것이라고 본다.

개념적으로 Database는 냉장고처럼 좀 크게 생긴 컴퓨터에 데이터를 채워 놓은 것이라고 할 수 있다. 그리고 SQL을 짜서 서버로 보내주면 결과를 내 PC의 모니터에 보여주는 것이다. 위의 예에서는 그냥 엑셀Sheet만 있고 SQL만 작성해서 보냈는데 결과는 어찌되었건 나왔다. 엑셀 내부적으로 위의 그림과 같은 구조로 되어 있는데 파일 하나로 다 끝나기 때문에 우리가 못느낄 뿐이다. 여하튼 Database에서 자료를 가져오는 것은 개념적으로 위와 같은 형태로 진행된다고 알면 된다.

 

※ 익히고 갈 용어

SQL : Structured Query Language의 약자이며 우리말로 “구조화된 질의언어”라고 한다. IT분야에서는 에스큐엘, 씨퀄이라고 부른다. 데이터베이스의 자료를 읽어오고, 수정하고, 삭제하는 등 자료를 처리하기 위해서 만들어진 “대화식 프로그래밍 언어”이다. 1강에서 소개한 관계형데이터베이스 시스템들은 자료를 다루기 위해 SQL을 표준으로 채택하여 사용하고 있다.
위의 예에서 “SELECT * FROM 사원정보$ WHERE 부서 = '영업팀'”이 SQL이다. “사원정보"라는 곳에서 자료를 가져와라”라는 간단한 프로그램이다. 입력하면 바로 자료를 보여주기 때문에 대화식 프로그래밍 언어라고 부른다.

Query : Query는 “물어보다”, “문의”, “질의”라는 뜻이 있고, IT분야에서는 데이터베이스에 자료를 요청하는 것을 말한다. SQL을 작성해서 서버로 보내고 결과를 받는 것을 Query한다라고 한다.

Database Server : 데이터를 가지고 있는 좀 큰 컴퓨터 또는 데이터가 모여있는 곳이라고 보면되고, 사용자PC에서 SQL을 작성해서 Database Sever에서 보내주면 Database Sever는 SQL을 해석하여 적절한 결과를 사용자PC로 보내준다.


첨부(실습용 파일) : 2강_MS_Query를_이용하여DB연결하기.xlsx

 

이번 강좌에서는 MS Query를 이용하여 데이터베이스에 연결해 보았다. 다음 강좌에서는 VBA를 이용하여 Database를 연결해 보기로 한다.

 

[ 엑셀에서 Database사용하기 강좌 목록 ]

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

 자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

“2강 - Microsoft Query를 이용하여 Database에 연결하기”의 25개의 댓글

  2. 신은수

    정말 흥미진진하네요.. 제가 회사에서 원하던 일이 잘 설명되어 있는 것 같아요 많은 도움 될 것 같습니다.^^

    답글
  3. 하일호

    좋은 글입니다. 내용을 작성하고 올리는 일이 쉽지 않았을텐데 수고가 많으십니다.
    덕분에 좋은 내용을 배우고 있습니다. 감사합니다.

    답글
  7. 전현

    다 성공적으로 되나요? 저는 링크된 다운로드파일을 받아서 실행하면
    "이 데이터 원본에는 볼수 있는 테이블이 없습니다" 라는 다이얼로그가 출력됩니다.

    답글
      1. 익명

        저도 같은 경우였는데 엑셀 파일을 미리 연 다음에 진행하시면 됩니다.

        답글
  8. 전현

    시스템테이블 옵션을 안택했네요.. 자체해결 - 댓글은 삭제 안되네요

    답글
  9. 김재성

    저는 첨부파일 받고 실행한후 기타원본에서 Excel Files* 를 골라 확인을 해도
    '데이터 원본 이름이 없고 기본 드라이버를 지정하지 않았습니다' 라고 뜨고
    찾아보기를 눌러 경로를 봐도 파일형식이 '데이터 원본' 뿐이라 엑셀파일을 고를수도 없습니다.
    뭐가 문제일까요?

    답글
    1. Admin

      안녕하세요. 아마도 Excel용 ODBC드라이버에 문제가 생긴 것 같습니다. ODBC데이터원본 관리자 > 드라이버 탭에서 Microsoft excel driver가 있는지 확인해 보시고 없다면 Office를 지우고 Office중에서 Excel부분은 전체설치를 해 보시고 다시 해 보시기 바랍니다. Microsoft excel driver가 있는데도 문제가 있다면 마찬가지로 Office를 새로 설치해 보시기 바랍니다.
      감사합니다.

      답글
      1. 익명

        회사에서는 정상적으로 돼서 확인해보니 Microsoft excel driver 가 있는걸 확인했습니다.
        집에서 할때 안된것이기 때문에 집에 가서 확인해보도록 하겠습니다.
        감사합니다^^

        답글
  10. 질문자

    저는 위에분 말처럼 “이 데이터 원본에는 볼수 있는 테이블이 없습니다” 라고 나오는데 해결을 못하고 있습니다. 어떻게 해결하신거지요.

    답글
    1. Admin

      안녕하세요. “이 데이터 원본에는 볼수 있는 테이블이 없습니다” 창이 뜨고 난 후에 아마도 "쿼리 마법사 - 열 선택"창이 나타날 겁니다. 그 창 밑에 보면 "옵션"버튼을 누르면 테이블옵션 창이 나타나는데 "시스템 테이블"에 체크가 되어 있지 않으면 체크하고 확인을 누르면 "사용할 수 있는 테이블과 열" 부분에 시트이름이 나타날겁니다.
      감사합니다.

      답글
  12. 익명

    감사합니다! 열심히 공부할게요! 소중한 지식 공유해주셔서 감사합니다.

    답글
  15. 조남석

    잘 보고 있습니다. 강의 감사합니다.
    순서대로 했는데요.

    3. 엑셀파일 선텍
    4. 테이블 옵션 확인 ->오류가 납니다.

    아래 메시지가 나옵니다. 어떻게 해야 할까요?
    MessageBox
    제목 : Microsoft Query
    내용 : 이 데이터 원본에는 볼 수 있는 테이블이 없습니다.
    [ 확인 버튼 ]

    답글
    1. 조남석

      ODBC데이터원본 관리자 > 드라이버 탭에서 Microsoft excel driver는 잘 설치되어 있습니다.
      윈도우10 검색창 > ODBC 데이터 원본 관리자 > "Excel Files ~~ "
      잘 되어 있는데 안됩니다.
      MS Access DB는 잘 로드 됩니다.

      답글
      1. Admin

        안녕하세요. 혹시 파일명과 파일 경로가 너무 길거나 한글이 포함되어 있으면 그럴 수 있으니 파일명을 짧게, 영문으로 바꾸고 c:\ 경로등에 복사한 후 테스트 해 보세요. 만약에 되면 경로문제일 가능성이 큽니다.

        답글
  16. 컴활준비중

    안녕하세요. 엑셀 관련 검색하다가 잘 알고 계신거 같아서 질문드려요!

    컴활 공부 중에 외부 데이터 가져오기 - Microsoft query를 하면 응답없음이 계속 도출되어 학습이 어려운 상태인데요. 오피스 재설치나 다른 방법을 해봐도 되질 않습니다. 이런경우 윈도우 재설치말고는 없을까요?

    답글
    1. Admin

      안녕하세요. '응답없음' 현상만으로는 정확히 원인이 무엇인지 알기가 어렵네요. 도움을 드리지 못해서 아쉽게 되었습니다 ㅠㅠ...

      답글
      1. 컴활준비중

        아닙니다. 길벗 사이트에 질문하여도 답이 없어 혹여 아실까하여 질문드렸습니다!
        감사합니다

        답글

댓글 남기기

Scroll to Top