CLR - EXCEL 파일 읽기

USP_EXCEL_READ.CS

EXCEL_READ.SQL

김종열

MS SQL Server MVP

엔트리브 소프트

DATABASE이전의 파일들, 정리된 파일들 중 우리가 가장 쉽게 접할 수 있는 파일이 아마도
엑셀이 아닐까 싶습니다.  이 엑셀의 내용을 바로 읽을 수 있다면 참으로 편리할 겁니다.
LINKED SERVER등등을 하셔도 되고 아니면 SSIS로 읽으온 다음 읽으셔도 되고..
직접 열어서 DATABASE로 보내주는 VBA를 만드셔도 되고..

방법은 여러가지가 있습니다. 또한 용도가 다르기에 어느 하나가 좋다라고 말할수도 없습니다.

저는 이번에 CLR로의 접근을 해보려고 합니다.
물론 안에는 OPENDATASET이라는 QUERY문을 사용하고 있습니다.
이는 CLR에서 직접 EXCEL을 CONNECT하기 위해서는 OLE개체를 참조할 수 있어야 하는데요. 
CLR WITH DATABASE(MSSQL)을 할 떄에 개체 참조는 지극히 제한적입니다.

해서 이런 변칙을 사용할 수 밖에 없었습니다.

그리고 OFFICE이기에 지극히 PRODUCT 종속적인 부분-DRIVER, PROVIDER등..-이 변경되어야 합니다.
(저는 VISTA, OFFICE2007을 사용하고 있습니다.)

이번에는 새로운 PROCEDURE 개체를 하나 추가해주십시오.
그런 다음 아래의 코딩을 입력하시고 배포하십시오. 그리고 추가적으로 해야 할 작업이 남아 있습니다.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

 

 

 

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void USP_EXCEL_READ(string fname, string sname)

    {

        SqlConnection _sqlConn = new SqlConnection("Context Connection=True");

        try

        {

            _sqlConn.Open();

 

            //제품종속적*****************

            string strSql = "SELECT * FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0', 'Excel 12.0;Database=" + fname + "', [" + sname + "$])";

            //제품종속적*****************

 

 

            //SqlContext.Pipe.Send(strSql);

            SqlCommand _sqlCmd = new SqlCommand(strSql, _sqlConn);

            SqlContext.Pipe.ExecuteAndSend(_sqlCmd);

        }

        catch (SqlException e)

        {

            SqlContext.Pipe.Send(e.Message.ToString());

        }

        finally

        {

            _sqlConn.Close();

        }

    }

};

 

이렇게 입력하고 나면 바로 실행을 할 수 있으면 좋겠는데요.
OPENDATASOURCE, OPENQUERY, OPENDATASET등의 쿼리를 실행하기 위해서는
SERVER에서 ADHOC DISTRIBUTED QUERY를 실행할 수 있는 세팅을 해주어야 합니다.

USE YOULYDB

GO

 

--adhoc distributed query를실행하기위한환경을세팅

SP_CONFIGURE 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

 

SP_CONFIGURE 'Ad Hoc Distributed Queries', 1

RECONFIGURE WITH OVERRIDE

GO

 

 

 

--실행

EXEC YOULYDB..USP_EXCEL_READ 'C:\LECTURE_CLR\PRJ_CLR\TEST.XLS', 'SHEET2'

 

 

 

/*

a     b     c     d    e    f     g  

----- ----- ----- ---- ---- ----- ----

80    55    39    41   20   25    39

96    79    42    50   22   47    78

40    18    98    51   42   51    78

43    87    31    57   93   93    24

57    91    69    86   32   78    90

92    78    5     17   53   13    51

26    2     26    99   45   49    51

60    14    22    51   19   5     31

8     68    98    76   50   53    80

83    39    12    88   98   66    46

82    89    95    95   8    70    95

89    95    42    13   57   18    5

81    37    49    12   38   85    7

 

(13 row(s) affected)

 

*/

참고파일 : USP_EXCEL_READ.CS
             EXCEL_READ.SQL

엑셀파일은 대충 만들고 테스트 해보시면 됩니다.
저는 RAND를 이용해서 대강의 데이터를 SHEET1, SHEET2에 각각 채우고
테스트를 해봤습니다.