Excel에서 SQL Server 또는 Azure SQL Database로 데이터 가져오기
적용 대상:
SQL ServerAzure SQL Database
Excel 파일에서 SQL Server 또는 Azure SQL Database로 데이터를 가져오는 여러 가지 방법이 있습니다. 한 단계로 Excel 파일에서 직접 데이터를 가져올 수 있는 방법도 있고, 가져오기 전에 Excel 데이터를 텍스트(CSV 파일)로 내보내야 하는 방법도 있습니다.
이 문서에서는 자주 사용하는 방법을 요약하고 자세한 정보의 링크를 제공합니다. SSIS 또는 Azure Data Factory와 같은 복잡한 도구 및 서비스에 대한 자세한 설명은 이 문서의 범위에 포함되지 않습니다. 관심 있는 솔루션에 대한 자세한 정보를 알아보려면 제공된 링크를 참조하세요.
메서드 목록
Excel에서 데이터를 가져오는 방법에는 몇 가지가 있습니다. 일부 관련 도구를 사용하려면 SSMS(SQL Server Management Studio)를 설치해야 할 수 있습니다.
다음 도구를 사용하여 Excel에서 데이터를 가져올 수 있습니다.
| 먼저 텍스트로 내보내기(SQL Server 및 SQL Database) | Excel에서 직접 가져오기(SQL Server 온-프레미스만 해당) |
|---|---|
| 플랫 파일 가져오기 마법사 | SQL Server 가져오기 및 내보내기 마법사 |
| BULK INSERT 문 | SSIS(SQL Server Integration Services) |
| BCP | OPENROWSET 함수 |
| 복사 마법사(Azure Data Factory) | |
| Azure Data Factory |
Excel 통합 문서에서 여러 워크시트를 가져오려는 경우 일반적으로 각 시트에서 한 번씩 이러한 도구를 각각 실행해야 합니다.
중요
자세한 내용은 Excel 파일로(또는 파일에서) 데이터를 로드하는 것과 관련된 제한 사항 및 알려진 문제를 참조하세요.
가져오기 및 내보내기 마법사
SQL Server 가져오기 및 내보내기 마법사를 사용하여 Excel 파일에서 직접 데이터를 가져옵니다. 설정을 SSIS(SQL Server Integration Services) 패키지로 저장하여 나중에 사용자 지정하고 다시 사용할 수도 있습니다.
SQL Server Management Studio에서 SQL Server 데이터베이스 엔진 인스턴스에 연결합니다.
데이터베이스를 확장합니다.
데이터베이스를 마우스 오른쪽 단추로 클릭합니다.
작업을 선택합니다.
데이터 가져오기 또는 데이터 내보내기를 선택합니다.
그러면 마법사가 시작됩니다.
자세한 내용은 다음을 검토하세요.
Integration Services(SSIS)
SSIS(SQL Server Integration Services)에 익숙하여 SQL Server 가져오기 및 내보내기 마법사를 실행하지 않으려면 데이터 흐름에서 SQL Server 대상과 Excel 원본을 사용하는 SSIS 패키지를 만듭니다.
자세한 내용은 다음을 검토하세요.
SSIS 패키지를 작성하는 방법을 알아보려면 ETL 패키지를 만드는 방법 자습서를 참조하세요.
OPENROWSET 및 연결된 서버
중요
Azure SQL Database에서는 Excel에서 직접 가져올 수 없습니다. 먼저 데이터를 텍스트(CSV) 파일로 내보내야 합니다.
참고
Excel 데이터 원본에 연결하는 ACE 공급자(이전의 Jet 공급자)는 대화형 클라이언트 쪽 사용을 위한 것입니다. 자동화된 프로세스 또는 병렬로 실행되는 프로세스에서 특히 SQL Server의 ACE 공급자를 사용하는 경우 예기치 않은 결과가 발생할 수 있습니다.
분산 쿼리
Transact-SQL OPENROWSET 또는 OPENDATASOURCE 함수를 사용하여 Excel 파일에서 SQL Server로 데이터를 직접 가져옵니다. 이 사용법을 분산 쿼리라고 합니다.
중요
Azure SQL Database에서는 Excel에서 직접 가져올 수 없습니다. 먼저 데이터를 텍스트(CSV) 파일로 내보내야 합니다.
분산 커리를 실행하려면 다음 예제에 나온 것과 같이 먼저 ad hoc distributed queries 서버 구성 옵션을 사용하도록 설정해야 합니다. 자세한 내용은 임시 분산 쿼리 서버 구성 옵션을 참조하세요.
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'ad hoc distributed queries', 1;
RECONFIGURE;
GO
다음 코드 샘플은 OPENROWSET을 사용하여 Excel Sheet1 워크시트의 데이터를 새 데이터베이스 테이블로 가져옵니다.
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]);
GO
다음은 OPENDATASOURCE와 같은 예제입니다.
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];
GO
가져온 데이터를 새 테이블을 만들지 않고 기존 테이블에 추가하려면 앞의 예제에서 사용된 SELECT ... INTO ... FROM ... 구문 대신에 INSERT INTO ... SELECT ... FROM ... 구문을 사용합니다.
Excel 데이터를 가져오지 않고 쿼리를 사용하려면 표준 SELECT ... FROM ... 구문을 사용하면 됩니다.
분산 쿼리에 대한 자세한 내용은 다음 문서를 참조하세요.
- 분산 쿼리(분산 쿼리는 SQL Server 2019(15.x)에서 계속 지원되지만 이 기능에 대한 설명서는 업데이트되지 않았습니다.)
- OPENROWSET
- OPENDATASOURCE
연결된 서버
SQL Server에서 Excel 파일로의 영구 연결을 ‘연결된 서버’로 구성할 수도 있습니다. 다음 예제에서는 기존 Excel 연결된 서버 EXCELLINK에 있는 Data 워크시트에서 Data_ls라는 새 SQL Server 데이터베이스 테이블로 데이터를 가져옵니다.
USE ImportFromExcel;
GO
SELECT * INTO Data_ls FROM EXCELLINK...[Data$];
GO
다음 예제에 나온 것과 같이 SSMS(SQL Server Management Studio)에서 연결된 서버 또는 시스템 저장 프로시저 sp_addlinkedserver를 실행하여 연결된 서버를 만들 수 있습니다.
DECLARE @RC INT;
DECLARE @server NVARCHAR(128);
DECLARE @srvproduct NVARCHAR(128);
DECLARE @provider NVARCHAR(128);
DECLARE @datasrc NVARCHAR(4000);
DECLARE @location NVARCHAR(4000);
DECLARE @provstr NVARCHAR(4000);
DECLARE @catalog NVARCHAR(128);
-- Set parameter values
SET @server = 'EXCELLINK';
SET @srvproduct = 'Excel';
SET @provider = 'Microsoft.ACE.OLEDB.12.0';
SET @datasrc = 'C:\Temp\Data.xlsx';
SET @provstr = 'Excel 12.0';
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server,
@srvproduct,
@provider,
@datasrc,
@location,
@provstr,
@catalog;
연결된 서버에 대한 자세한 내용은 다음 문서를 참조하세요.
연결된 서버 및 분산 쿼리 모두에 대한 정보 및 예제는 다음 문서를 참조하세요.
필수 구성 요소 - Excel 데이터를 텍스트로 저장
이 페이지에 설명된 나머지 방법(BULK INSERT 문, BCP 도구 또는 Azure Data Factory 등)을 사용하려면 먼저 Excel 데이터를 텍스트 파일로 내보내야 합니다.
Excel에서 파일 | 다른 이름으로 저장을 선택한 다음, 텍스트(탭으로 구분)(*.txt) 또는 CSV(쉼표로 구분)(*.csv)를 대상 파일 형식으로 선택합니다.
통합 문서에서 여러 워크시트를 가져오려는 경우 각 시트를 선택한 다음, 이 프로시저를 반복합니다. 다른 이름으로 저장 명령은 활성 시트만을 내보냅니다.
팁
데이터 가져오기 도구로 최상의 결과를 얻으려면, 데이터의 열 헤더 및 행만 포함하는 시트를 저장합니다. 저장된 데이터에 페이지 제목, 빈 줄, 메모 등이 포함되어 있는 경우 나중에 데이터를 가져올 때 예기치 않은 결과가 발생할 수 있습니다.
플랫 파일 가져오기 마법사
플랫 파일 가져오기 마법사 페이지를 단계별로 실행하여 텍스트 파일로 저장된 데이터를 가져옵니다.
앞의 필수 구성 요소 섹션에서 설명한 대로 플랫 파일 가져오기 마법사를 사용하여 가져오기 전에 Excel 데이터를 텍스트로 내보내야 합니다.
플랫 파일 가져오기 마법사에 대한 자세한 내용은 SQL 마법사로 플랫 파일 가져오기를 참조하세요.
BULK INSERT 명령
BULK INSERT는 SQL Server Management Studio에서 실행할 수 있는 Transact-SQL 명령입니다. 다음 예제에서는 Data.csv 쉼표로 구분된 파일에서 기존 데이터베이스 테이블로 데이터를 로드합니다.
앞의 필수 구성 요소 섹션에서 설명한 대로 BULK INSERT를 사용하여 데이터를 가져오려면 먼저 Excel 데이터를 텍스트로 내보내야 합니다. BULK INSERT는 Excel 파일을 직접 읽을 수 없습니다. BULK INSERT 명령을 사용하여 로컬 또는 Azure Blob Storage에 저장된 CSV 파일을 가져올 수 있습니다.
USE ImportFromExcel;
GO
BULK INSERT Data_bi FROM 'C:\Temp\data.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
GO
SQL Server 및 SQL Database에 대한 자세한 내용과 예제는 다음 문서를 참조하세요.
BCP 도구
BCP는 명령 프롬프트에서 실행되는 프로그램입니다. 다음 예제에서는 Data.csv 쉼표로 구분된 파일에서 기존 Data_bcp 데이터베이스 테이블로 데이터를 로드합니다.
앞의 필수 구성 요소 섹션에서 설명한 대로 BCP를 사용하여 데이터를 가져오려면 먼저 Excel 데이터를 텍스트로 내보내야 합니다. BCP는 직접 Excel 파일을 읽을 수 없습니다. 로컬 스토리지에 저장된 테스트(CSV) 파일에서 SQL Server 또는 SQL Database로 가져오는 데 사용합니다.
중요
Azure Blob Storage에 저장된 텍스트(CSV) 파일의 경우 BULK INSERT 또는 OPENROWSET를 사용합니다. 예제는 예제를 참조하세요.
bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,
BCP에 대한 자세한 내용은 다음 문서를 참조하세요.
복사 마법사(ADF)
ADF(Azure Data Factory) 복사 마법사 페이지를 단계별로 실행하여 텍스트 파일로 저장된 데이터를 가져옵니다.
앞의 필수 구성 요소 섹션에서 설명한 대로 Azure Data Factory를 사용하여 데이터를 가져오려면 먼저 Excel 데이터를 텍스트로 내보내야 합니다. Data Factory는 직접 Excel 파일을 읽을 수 없습니다.
복사 마법사에 대한 자세한 내용은 다음 문서를 참조하세요.
Azure 데이터 팩터리
Azure Data Factory에 익숙하여 복사 마법사를 실행되지 않게 하려면 텍스트 파일을 SQL Server 또는 Azure SQL Database로 복사하는 복사 작업으로 파이프라인을 만듭니다.
앞의 필수 구성 요소 섹션에서 설명한 대로 Azure Data Factory를 사용하여 데이터를 가져오려면 먼저 Excel 데이터를 텍스트로 내보내야 합니다. Data Factory는 직접 Excel 파일을 읽을 수 없습니다.
이러한 Data Factory 원본 및 싱크에 대한 자세한 내용은 다음 문서를 참조하세요.
Azure Data Factory를 사용해 데이터를 복사하는 방법을 알아보려면 다음 문서를 참조하세요.
일반 오류
Microsoft.ACE.OLEDB.12.0"이 등록되지 않음
이 오류는 OLEDB 공급자가 설치되지 않아 발생합니다. Microsoft Access 데이터베이스 엔진 2016 재배포 가능 패키지에서 설치합니다. Windows 및 SQL Server가 모두 64비트이면 64비트 버전을 설치해야 합니다.
오류 전문은 다음과 같습니다.
Msg 7403, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
연결된 서버 “(null)”에 대해 OLE DB 공급자 “Microsoft.ACE.OLEDB.12.0”의 인스턴스를 만들 수 없습니다.
이것은 Microsoft OLEDB가 올바르게 구성되지 않았음을 나타냅니다. 다음 Transact-SQL 코드를 실행하여 이 문제를 해결합니다.
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;
오류 전문은 다음과 같습니다.
Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
32비트 OLE DB 공급자 “Microsoft.ACE.OLEDB.12.0”은 64비트 SQL Server에서 In Process로 로드할 수 없습니다.
이것은 OLD DB의 32비트 버전이 64비트 SQL Server에 설치되었을 때 발생합니다. 이 문제를 해결하려면 32 비트 버전을 제거하 고 64비트 버전의 OLE DB 공급자를 설치합니다.
오류 전문은 다음과 같습니다.
Msg 7438, Level 16, State 1, Line 3
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.
연결된 서버 "(null)"에 대한 OLE DB 공급자 "Microsoft.ACE.OLEDB.12.0"이 오류를 보고했습니다.
연결된 서버 “(null)”에 대해 OLE DB 공급자 “Microsoft.ACE.OLEDB.12.0”의 데이터 원본 개체를 초기화할 수 없습니다.
이 오류는 모두 보통은 SQL Server 프로세스와 파일 간의 권한 문제를 의미합니다. SQL Server 서비스를 실행하는 계정에 파일에 대한 전체 액세스 권한이 있는지 확인합니다. 바탕 화면에서 파일 가져오기를 시도하는 것이 좋습니다.
오류 전문은 다음과 같습니다.
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
다음 단계
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기