Excel Data 쿼리하기(EM)

강좌 전 지용군의 잡담>

사람들게는 저마다 자신만의 방이 있는 법이다. 조금만 귀를 기울이면 그 방에서 무슨 일이 일어나는지는 충분히 알 수 있다.

스 무살..
김 연수.

강좌 시작 >

작업 환경

• 운영체제 : Windows 2000 Advanced Server,. 5.0 (2195)
  
• SQL Server : SQL Server 2000 Enterprise Edition(8.00.954)
  
• 기타 : Office Excel 2003(11.6355.6360) SP1

0. EXCEL = Database??

프로젝트를 진행하다보면, 의외로 많은 현업/시스템 운영자분들께서 Excel로 데이터를 관리하시는 것을 볼 수 있습니다.
심지어는 데이터 이관을 위한 Format을 Excel로 하자는 분도 계셨다는...(긁적...)

조금 삼천포로 빠져보면... Windows 3.1 버전이 출시되면서 본격적으로 WYSIWYG(What You See Is What You Get) 환경이 시작되기는 하였습니다만, 정작 Windows 3.1을 설치하고 나면 할 수 있는 것이 카드 게임 정도였던 것으로 기억이 되는데요.
여기에 불법 복제를 통해서 처음으로 접한 Windows Application이 바로 Excel입니다. 마우스 사용이 생소하던 시절, Drag & Drop의 기능이 그렇게 대단해 보일 수 없었습니다.

현재는 많은 기능들이 추가되었고, VBA지원과 함께 데이터 원본(관계형 데이터베이스, Analysis Service)에까지 접속이 가능해져서, 하나의 기업용 Application으로도 활용이 가능할정도로 발전을 해왔습니다. 때문에, 근간에는 IT 전문가 뿐만 아니라 기업 업무에서부터 전문적인 통계 분야에 이르기까지 상당히 포괄적으로 사용되고 있죠.

이렇게 강력하면서도 편리한 기능을 제공하면서 광범위하게 사용되는 이유 때문인지, 언제부터인가 데이터를 Excel로 내려받는 기능을 제공하는 기능들이 MIS/ERP 시스템에 기본적으로 추가되어 왔습니다. 물론 대부분의 목적이 보고서 작성이나 보고서 작성의 기본이 되는 데이터를 제공하기 위한 것이지만, 반대로 Excel 데이터를 데이터 원본(데이터베이스)로 입력하기를 원하는 경우도 종종 있습니다.

Excel에 저장된 데이터는 기업용 Application과 같이, 미리 정의된 업무 규칙에 의해서 입력된 데이터가 아니라 사용자가 직접 작성한 데이터일 가능성이 높기 때문에, 잘못된 데이터의 입력을 규제하거나 업무 규칙을 적용시키지는 못하게 됩니다. 따라서, 데이터베이스의 데이터 정합성 측면에서 본다면, 바람직한 입력 방식은 아닌 것으로 생각됩니다.

각설하고, SQL Server에서는 연결된 서버(Linked Server)를 이용하여, Excel 파일을 별도의 데이터 원본으로 인식하도록 하고, 이를 대상으로 작업을 할 수 있습니다.

연결된 서버를 이용하여 Excel의 데이터를 읽어오는 방식은 다음과 같습니다.

1. Excel 파일을 가상의 데이터 원본으로 만든다.
   
2. 데이터 원본으로 로그인 한다.
   
3. 데이터 원본에 대하여 원하는 작업을 실행한다.
   
4. 데이터 원본에 대한 로그인을 삭제한다.
   
5. 가상의 데이터 원본을 삭제한다.

상기의 작업을 실행하기 위해서는, 예제 Excel 파일이 있어야 하는데요. 간단하게 쿼리 분석기에서 쿼리한 데이터를 이용하여 파일을 작성해보도록 하지요.

먼저, 쿼리 분석기를 열고 다음의 쿼리를 실행합니다.

다음의 그림에서 보여드리는 것과 같이, 결과 창의 데이터를 선택하고 오른쪽 마우스를 이용하여 Context Menu의 복사를 선택하거나, Ctrl+C를 눌러서 데이터를 복사합니다.

다음에는 새로운 Excel 문서에 복사한 내용을 붙입니다. 이때 그림과 같이, 1열에는 Column 이름을 입력합니다.

작성한 Excel 파일을 C: 경로에 Titles.xls라는 이름으로 저장합니다.

Excel의 데이터를 이용한 작업은 다음과 같이 두 가지 방법에 의해서 설정이 가능합니다.

• 엔터프라이즈 관리자를 이용한 방법
  
• T-SQL을 이용한 방법

먼저, 친근한 GUI Interface를 제공하는 엔터프라이즈 관리자를 이용한 방법을 살펴보도록 하겠습니다.

1. 연결된 서버 설정

SQL Server에서는 다른 SQL Server뿐만이 아니라, Oracle, IBM DB2 등과 같은 다른 관계형 데이터베이스 제품에 대해서도 작업을 할 수 있는 기능을 제공합니다.

이와 같이, 다른 형태의 저장 매체 또는 데이터 원본의 데이터에 대해서 접속이 가능하도록 하는 기능은 SQL Server의 자체 기능이 아니며, Microsoft에 의해서 제공되는 OLE DB라는 제공자(Provider)를 이용하는 것입니다.

OLE DB 제공자는 전혀 새로운 개념이 아니며, OLE DB가 발표되기 전까지 업계의 표준처럼 사용되었던 ODBC의 단점을 극복하기 위해서 Microsoft에 의해서 제안된 제공자입니다.

그렇다면, OLE DB에 의해서 지원되지 않는 저장 매체나 데이터 원본으로의 접근은 어렵다는 것이 아닌가?

예. 이론적으로는 그렇습니다만, Microsoft는 OLE DB for ODBC라는 제공자를 제공함으로써 ODBC를 통하여 접근할 수 있는 데이터 원본에 대해서도 접속을 지원할 수 있도록 하고 있습니다.

따라서, 기존 ODBC로 접속이 가능한 모든 데이터 원본에 대해서 OLE DB가 지원한다고 볼 수 있습니다.

사설이 좀 길었군요. 각설하고, 앞서 작성하였던 Excel 파일에 대해서 본격적으로 연결된 서버를 설정해보도록 하겠습니다.

엔터프라이즈 관리자에서 연결된 서버 기능은 다음의 그림과 같이, 해당 서버 - 보안 - 연결된 서버에서 설정이 가능합니다.

연결된 서버에서 오른쪽 마우스를 선택하고, [새 연결된 서버]를 선택합니다.

해당 메뉴를 선택하면 다음의 그림과 같은 화면이 나타납니다.

[일반] 탭에는 다음의 그림과 같이,

[연결된 서버]에는 'XLSVR'을 입력하고

[서버 유형]은 [Microsoft Jet 4.0 OLE DB Provider]를 선택합니다.

[제품 이름]에는 데이터 원본의 제품인 'Excel'을 입력합니다.

[데이터 원본]에는 실제 데이터 원본이 위치하고 있는 경로와 Excel 파일의 명칭까지 작성합니다. 테스트를 위해서 작성한 경로와 파일 명칭인 'c:titles.xls'를 입력합니다.

[공급자 문자열]에는 'Excel 8.0;HDR=NO'를 입력합니다. Excel 8.0은 현재 파일을 작성하신 Excel의 버전을 기술하시면 됩니다. HDR=NO 항목에 대해서는 다음 강좌에서 좀 더 자세하게 다루도록 하겠습니다.

2. 데이터 원본으로 로그인 한다.

다음에는 [보안] 탭의 항목들을 살펴보도록 하겠습니다.

[보안] 탭에서는 연결된 서버에 로그인하기 위한 정보들을 설정하게 됩니다. [보안] 텝에서는 다음의 그림과 같이, 두 가지 항목만을 설정하여 줍니다.

[로컬 로그인]에는 'sa'를 [원격 사용자]에는 'admin'을 기입합니다. 하단의 [확인] 버튼을 선택하면, 다음의 그림과 같이 XLSVR이라는 이름으로 새로운 연결된 서버가 설정됩니다.

3. 데이터 원본에 대하여 원하는 작업을 실행한다.

이로써 연결된 서버에 대한 설정 작업은 모두 완료되었습니다. 이제부터는 원하는 작업을 수행할 수 있습니다. 쿼리 분석기를 열어서 몇 가지 간단한 예제를 실행해보도록 하겠습니다.

위의 그림과 같이, 간단히 연결된 서버로부터 전체 데이터를 반환할 수도 있습니다.

SELECT 구문을 보시면, 아주 간단한 구문입니다. 하지만, 조금 다른 부분이 있습니다. 테이블 명칭을 작성하는 부분이 생각보다 길게 작성이 되어 있는 것을 볼 수 있습니다.

이는 SQL Server의 개체 명명법을 따른 것으로, SQL Server에서는 개체의 명칭을 다음과 같이 4가지 부분으로 나누어서 명시하도록 규정하고 있습니다.

개체 명칭의 4가지 요소는

• Server(서버)
  
• Database(데이터베이스)
  
• Owner(소유자)
  
• Object(개체)

로 나누어지며, Server.database.owner.object 형태로 쓰여집니다.

이는 전체 4가지 요소들을 모두 명시하여 사용할 수도 있고, 일부 항목들은 생략할 수도 있습니다.

예를 들어, 현재 제가 사용하고 있는 machine의 이름은 'vangelis'입니다. 따라서, [서버] 항목에는 'vangelis'가 올 수 있겠죠.

따라서, 다음의 쿼리를

4가지 요소들을 전부 명시하여 사용한다면, 다음과 같습니다.

현재 접속하여 있는 서버 자체가 바로 vangelis이므로, 이는 생략이 가능하기 때문에 쿼리문은 다음과 같이 수정할 수 있습니다.

또한, 접속한 데이터베이스가 바로 pubs이므로 'pubs'라는 데이터베이스 요소도 생략이 가능합니다.

또한, 데이터베이스 서버로 접속한 계정 자체가 dbo의 권한을 가지고 있다면, dbo라는 소유자 항목도 생략이 가능합니다.

하지만, 개체에 대한 소유자는 서로 다를 수 있으므로, 가능하면 개체 명칭과 함께 소유자의 명칭도 같이 명시해주시는 것이 쿼리의 성능에 약간이나마 도움이 됩니다.

이러한 개체 명명법을 기준을 연결된 서버에 적용하여 보면,

• Server : 서버에는 연결된 서버 명칭인 'XLSVR'를 명시하고,
  
• Database/Owner : EXCEL에는 데이터베이스나 소유자가 없으므로 생략합니다.
  
• Object : 마지막으로 개체 명칭에는 worksheet의 명칭을 기술합니다.

이러한 방식으로, SQL Server는 다른 서버나 다른 데이터베이스, 다른 개체 소유자의 개체를 이용한 작업을 할 수 있습니다.

주의 : 데이터 원본이 되는 Excel 파일에 대한 작업을 수행하는 경우, 해당 Excel 파일은 닫혀 있어야만 합니다. 그렇지 않은 경우에는 다음과 같은 오류가 발생될 수 있습니다.

4. 데이터 원본에 대한 로그인을 삭제한다.
5. 가상의 데이터 원본을 삭제한다.

이제 마지막으로 작업이 완료되거나, 더 이상 연결된 서버가 필요없는 경우에는 연결된 서버 정보를 해제하여야 합니다. 이는 간단한 마우스 작업 만으로 가능합니다. 엔터프라이즈 관리자의 연결된 해당 서버 항목에서 오른쪽 마우스를 선택하고,

[삭제] 메뉴를 선택함으로써 완료 됩니다.

삭제 여부를 묻는 MessageBox에서 [예]를 선택하면, 다음의 그림과 같이 서버의 원격 로그인도 삭제된다는 메시지가 보여지게 됩니다. 이는 앞서 연결된 서버를 생성하면서, [보안] 탭에서 설정하였던 로그인 정보도 같이 삭제된다는 것을 의미합니다. 다시 한 번 더 [예]를 선택하면, 연결된 서버와 관련된 모든 정보가 삭제됩니다.

마치며...

이번 강좌에서는 엔터프라이즈 관리자를 이용한 Excel의 Data를 쿼리하는 방법에 대해서 살펴보았습니다. 다음 강좌에서는 T-SQL을 이용하여 Excel 파일을 쿼리하는 방법과 앞서 설명을 미루었던 옵션과 함께, Excel 파일 이외의 데이터 저장소에 대한 쿼리에 대해서도 살펴보도록 하겠습니다.

오늘도 어김없이... 화.이.팅. 입니다. ^^