[MSSQL] SSMS에서 엑셀 파일 데이터 읽기 (MSSQL 2012)

출처 : http://blog.naver.com/PostView.nhn?blogId=lemonyja&logNo=10177002870

SSMS에서 엑셀 파일 데이터 읽기 (MSSQL 2012)

 

OPENDATASOURCE, OPENROWSET 으로 엑셀 데이터 읽기를 시도하자 에러 발생. 

 

메시지 15281, 수준 16, 상태 1, 줄 1
구성 요소 'Ad Hoc Distributed Queries'이(가) SQL Server 보안 구성의 일부로 해제되었으므로 이 구성 요소의 문

'OpenRowset/OpenDatasource'에 대한 액세스가 차단되었습니다.  

시스템 관리자는 sp_configure를 통해 'Ad Hoc Distributed Queries'을(를) 활성화할 수 있습니다.

'Ad Hoc Distributed Queries' 활성화에 대한 자세한 내용을 보려면 SQL Server 온라인 설명서에서

'Ad Hoc Distributed Queries'을(를) 검색하십시오. 

 

EXEC SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE

GO

EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1;
RECONFIGURE

GO

 

엑셀 데이터 읽기를 시도하자 20~30초 뺑뺑이 후 인스턴스 내려감.

메시지 109, 수준 20, 상태 0, 줄 0
서버로부터 결과를 수신하는 동안 전송 수준 오류가 발생했습니다. (provider: Shared Memory Provider, error: 0 - 파이프가 끝났습니다.)

 

인스턴스 시작.

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
-- 필요시 실행 ( EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 )

 

엑셀 데이터 읽기를 시도 함.

메시지 7399, 수준 16, 상태 1, 줄 1
연결된 서버 "(null)"의 OLE DB 공급자 "Microsoft.ACE.OLEDB.12.0"에 오류가 발생했습니다. 공급자에서 오류에 관한 정보를 주지 않았습니다.
메시지 7303, 수준 16, 상태 1, 줄 1
연결된 서버 "(null)"에 대한 OLE DB 공급자 "Microsoft.ACE.OLEDB.12.0"의 데이터 원본 개체를 초기화할 수 없습니다.

 

인스턴스 재시작. (가끔 재시작 후 바로 인식이 안되는 듯. 같은 메시지 발생시 인스턴스 재시작 한번 더 ~)

SSMS에서 엑셀파일 데이터 읽기 가능.

 

 

-------------------- 형식 --------------------

SELECT 열이름1, 열이름2, 열이름5, 열이름9 ...
FROM OPENDATASOURCE
('Microsoft.ACE.OLEDB.12.0',  'Data Source=D:\ExcelName.xlsx;Extended Properties=Excel 12.0')...[SheetName$];
 
SELECT F1, F5, F6 ...
FROM OPENROWSET

('Microsoft.ACE.OLEDB.12.0',  'Excel 12.0;Database=D:\ExcelName.xlsx; hdr=no;imex=1', [SheetName$]);

--Excel 2007-2010
SELECT * --INTO #productlist
FROM OPENROWSET

('Microsoft.ACE.OLEDB.12.0',

  'Excel 12.0 Xml;HDR=YES;Database=C:\temp\Products.xlsx', 'SELECT * FROM [ProductList$]');

 

--Excel 97-2003
SELECT *

FROM OPENDATASOURCE

('Microsoft.Jet.OLEDB.4.0',
  'Data Source=C:\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;

 

SELECT *
FROM OPENDATASOURCE

('SQLNCLI',
  'Data Source=서버명\인스턴스명;Integrated Security=SSPI').DB명.스키마명.테이블명;

 

SELECT *
FROM OPENROWSET
  (
  'SQLOLEDB.1',
  'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DB명;Data Source=localhost',
  'SELECT TOP 1000 * FROM vTargetMail'
  )

 

OPENDATASOURCE ( provider_name, init_string )

provider_name : 데이터 원본에 액세스하는 데 사용하는 OLE DB 공급자의 PROGID로 등록된 이름

it_string : 대상 공급자의 IDataInitialize 인터페이스로 전달되는 연결 문자열

  - Data Source : 연결할 데이터 원본의 이름 (OLE DB 공급자의 경우 서버 이름 또는 .mdb 파일이나 .xls 파일의 전체 경로.)
  - Extended Properties : 공급자별 연결 문자열
  - Connect timeout : 연결 시도가 실패한 후의 제한 시간 값
  - User ID : 연결에 사용할 사용자 ID
  - Password : 연결에 사용할 암호
  - Catalog : 데이터 원본에 연결할 때의 초기 또는 기본 카탈로그 이름 (DB명)
  - Integrated Security : Windows 인증을 지정하는 SSPI

OPENROWSET ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'  | 'provider_string' } 
     , {   [ catalog. ] [ schema. ] object  | 'query'  }  | BULK 'data_file'

    , { FORMATFILE = 'format_file_path' [ <bulk_options> ] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } ) 
  
  <bulk_options> ::=
     [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
     [ , ERRORFILE = 'file_name' ]
     [ , FIRSTROW = first_row ] 
     [ , LASTROW = last_row ] 
     [ , MAXERRORS = maximum_errors ] 
     [ , ROWS_PER_BATCH = rows_per_batch ] 

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
       'SELECT 컬럼1, 컬럼2 FROM DB명.스키마명.테이블명 ORDER BY 컬럼1') AS a;

 

[출처] MSSQL 엑셀파일 읽기|작성자 양갱