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 및 Azure 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) 패키지로 저장하여 나중에 사용자 지정하고 다시 사용할 수도 있습니다.

1. SQL Server Management Studio에서 SQL Server 데이터베이스 엔진 인스턴스에 연결합니다.

2. 데이터베이스를 확장합니다.

3. 데이터베이스를 마우스 오른쪽 단추로 클릭합니다.

4. 작업을 선택하세요.

5. 데이터 가져오기 또는 데이터 내보내기를 선택합니다.

   

그러면 마법사가 시작됩니다.

자세한 내용은 다음 문서를 참조하세요.

• SQL Server 가져오기 및 내보내기 마법사 시작
• 가져오기 및 내보내기 마법사의 이 간단한 예제로 시작

Integration Services(SSIS)

SSIS(SQL Server Integration Services)에 익숙하여 SQL Server 가져오기 및 내보내기 마법사를 실행하지 않으려면 대신에 데이터 흐름에서 SQL Server 대상과 Excel 원본을 사용하는 SSIS 패키지를 만들 수 있습니다.

자세한 내용은 다음 문서를 참조하세요.

• Excel 원본
• SQL Server 목적지

SSIS 패키지를 작성하는 방법을 알아보려면 ETL 패키지를 만드는 방법 자습서를 참조하세요.

OPENROWSET 및 연결된 서버

중요

Azure SQL Database에서는 Excel에서 직접 가져올 수 없습니다. 먼저 데이터를 텍스트(CSV) 파일로 내보내야 합니다.

Excel 데이터 원본에 연결하는 Office에 포함된 ACE 공급자는 대화형 클라이언트 쪽 사용을 위한 것이므로 다음 예제에서는 JET 공급자를 사용합니다.

분산 쿼리

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.JET.OLEDB.4.0',
    'Excel 8.0; Database=C:\Temp\Data.xls', [Sheet1$]);
GO

다음은 OPENDATASOURCE와 같은 예제입니다.

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
    'Data Source=C:\Temp\Data.xls;Extended Properties=Excel 8.0')...[Sheet1$];
GO

가져온 데이터를 새 테이블을 만들지 않고 기존 테이블에 추가하려면 앞의 예제에서 사용된 INSERT INTO ... SELECT ... FROM ... 구문 대신에 SELECT ... INTO ... FROM ... 구문을 사용합니다.

Excel 데이터를 가져오지 않고 쿼리를 사용하려면 표준 SELECT ... FROM ... 구문을 사용하면 됩니다.

분산 쿼리에 대한 자세한 내용은 다음 문서를 참조하세요.

• 분산 쿼리¹
• OPENROWSET(Transact-SQL)
• OPENDATASOURCE(Transact-SQL)

¹ 분산 쿼리는 SQL Server에서 계속 지원되지만 이 기능에 대한 설명서는 업데이트되지 않습니다.

연결된 서버

SQL Server에서 Excel 파일로의 영구 연결을 ‘연결된 서버’로 구성할 수도 있습니다. 다음 예제에서는 기존 Excel 연결된 서버 Data에 있는 EXCELLINK 워크시트에서 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.JET.OLEDB.4.0';
SET @datasrc = 'C:\Temp\Data.xls';
SET @provstr = 'Excel 8.0';

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server,
    @srvproduct,
    @provider,
    @datasrc,
    @location,
    @provstr,
    @catalog;

연결된 서버에 대한 자세한 내용은 다음 문서를 참조하세요.

• 연결된 서버 만들기(SQL Server 데이터베이스 엔진)
• OPENQUERY(Transact-SQL)

연결된 서버 및 분산 쿼리 모두에 대한 정보 및 예제는 다음 문서를 참조하세요.

• SQL Server 연결된 서버 및 분산 쿼리와 함께 Excel을 사용하는 방법

필수 조건

이 페이지에 설명된 나머지 방법(BULK INSERT 문, bcp 도구 또는 Azure Data Factory 등)을 사용하려면 먼저 Excel 데이터를 텍스트 파일로 내보내야 합니다.

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 및 Azure SQL Database에 대한 자세한 내용과 예제는 다음 문서를 참조하세요.

• BULK INSERT 또는 OPENROWSET(BULK...)을 사용하여 SQL Server로 데이터 가져오기
• BULK INSERT(Transact-SQL)

대량 복사 도구(bcp)

bcp 도구는 명령 프롬프트에서 실행됩니다. 다음 예제에서는 Data.csv 쉼표로 구분된 파일에서 기존 Data_bcp 데이터베이스 테이블로 데이터를 로드합니다.

앞의 필수 구성 요소 섹션에서 설명한 대로 bcp를 사용하여 데이터를 가져오려면 먼저 Excel 데이터를 텍스트로 내보내야 합니다. bcp 도구구는 직접 Excel 파일을 읽을 수 없습니다. 로컬 스토리지에 저장된 테스트(CSV) 파일에서 SQL Server 또는 SQL Database로 가져오는 데 사용합니다.

중요

Azure Blob Storage에 저장된 텍스트(CSV) 파일의 경우 BULK INSERT 또는 OPENROWSET를 사용합니다. 예를 들면 BULK INSERT 또는 OPENROWSET(BULK...)을 사용하여 SQL Server로 데이터 가져오기를 참조하세요.

bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,

bcp에 대한 자세한 내용은 다음 문서를 참조하세요.

• bcp를 사용하여 대량 데이터 가져오기 및 내보내기(SQL Server)
• 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 원본 및 싱크에 대한 자세한 내용은 다음 문서를 참조하세요.

• 파일 시스템
• SQL Server
• Azure SQL Database

Azure Data Factory를 사용해 데이터를 복사하는 방법을 알아보려면 다음 문서를 참조하세요.

• 복사 활동을 사용하여 데이터 이동
• 자습서: Azure Portal을 사용하여 복사 활동으로 파이프라인 만들기

관련 콘텐츠

• 가져오기 및 내보내기 마법사의 이 간단한 예제로 시작
• SSIS(SQL Server Integration Services)를 사용하여 Excel에서 데이터 가져오기 또는 Excel로 데이터 내보내기
• bcp 유틸리티
• 복사 활동을 사용하여 데이터 이동