/**********************************************************************************************
-- Title : [2k5] QA에서 EXCEL 데이터 쿼리하기
-- Reference : dBRang.com
-- Key word : openrowset, opendatasource, sp_configure 엑셀
**********************************************************************************************/
-- 우선 구성 요소 'Ad Hoc Distributed Queries'가 세팅되어야 한다.
-- 설정되지 않을 시 아래 에러 출력.
/*
서버: 메시지 15281, 수준 16, 상태 1, 줄 1
구성 요소 'Ad Hoc Distributed Queries'이(가) SQL Server 보안 구성의 일부로 해제되었으므로 이 구성 요소의
문 'OpenRowset/OpenDatasource'에 대한 액세스가 차단되었습니다. 시스템 관리자는 sp_configure를 사용하여
'Ad Hoc Distributed Queries'의 사용을 활성화할 수 있습니다.
'Ad Hoc Distributed Queries' 활성화 방법은 SQL Server 온라인 설명서의 "노출 영역 구성"을 참조하십시오.
*/
EXEC sp_configure 'show advanced option', '1' -- 1:고급옵션, 2:기본옵션
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure
GO
-- 옵션 변경
EXEC sp_configure 'Ad Hoc Distributed Queries', '1';
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure
GO
/* Openrowset으로 */
-- SQL 2000
SELECT cast(akey as int), cast(ord as int), cast(ap as char(7))
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\wips_raw\WIPSDown01.xls', PAT_SHHET$)
--시트명 뒤에 '$'붙여야 됨. [PAT_SHEET$]
--엑셀 셀서식에 따라 특히 숫자에서 타입이 바뀐다. 적절히 타입 변환 필요[cast(ord as int), ]
--물론 엑셀 파일은 DB서버 로컬에 위치해야 한다.
-- Over SQL 2005
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=c:\test.xlsx', SHEET1$);
/* Opendatasource로 */
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\wips_raw\WIPSDown01.xls";User ID=;
Password=;Extended properties=Excel 2000') --요건 안되드라..ㅡㅡ;;