Excel から SQL Server または Azure SQL Database にデータをインポートする

適用対象: SQL Server Azure SQL データベース

Excel ファイルからSQL Server または Azure SQL Database に、データをインポートする方法はいくつかあります。 一部の方法では、Excel ファイルから直接 1 ステップでデータをインポートできます。他の方法では、先に Excel データをテキスト (CSV ファイル) としてエクスポートしてから、インポートする必要があります。

この記事では、よく使われる方法をまとめ、詳細な情報へのリンクを示します。 SSIS や Azure Data Factory のような複雑なツールとサービスの詳細については、この記事の範囲外です。 興味のあるソリューションの詳細については、提示されたリンクを参照してください。

方法の一覧

Excel からデータをインポートする方法は複数あります。 これらのツールの一部を使用するには、SQL Server Management Studio (SSMS) をインストールする必要があります。

次のツールを使用して Excel からデータをインポートすることができます。

最初にテキストにエクスポートする (SQL Server と Azure SQL データベース)	Excel から直接 (SQL Server オンプレミスのみ)
フラット ファイルのインポート ウィザード	SQL Server インポートおよびエクスポート ウィザード
BULK INSERT ステートメント	SQL Server Integration Services (SSIS)
一括コピー ツール (bcp)	OPENROWSET 関数
コピー ウィザード (Azure Data Factory)
Azure Data Factory

Excel ブックから複数のワークシートをインポートする場合は、通常、シートごとに 1 回これらのいずれかのツールを実行する必要があります。

詳細については、Excel ファイルとの間で「データ読み込みの制限事項と既知の問題」を参照してください。

インポートおよびエクスポート ウィザード

SQL Server インポートおよびエクスポート ウィザードを使用して、Excel ファイルからデータを直接インポートします。 後でカスタマイズして再利用できる SQL Server Integration Services (SSIS) パッケージとして設定を保存することもできます。

1. SQL Server Management Studioで、 SQL Serverデータベース エンジンのインスタンスに接続します。

2. [データベース] を展開します。

3. データベースを右クリックします。

4. [タスク] を選択します。

5. [データのインポート] または [データのエクスポート] を選択します。

   

これにより、ウィザードが起動します。

詳細については、次の記事をご覧ください。

• SQL Server インポートおよびエクスポート ウィザードを起動する
• 簡単な例によるインポートおよびエクスポート ウィザードの概要

Integration Services (SSIS)

SQL Server Integration Services (SSIS) に精通していて [SQL Server インポートとエクスポート ウィザード] を実行したくない場合、Data Flow で Excel ソースと SQL Server 変換先を使用する SSIS パッケージを作成できます。

詳細については、次の記事をご覧ください。

• Excel ソース
• SQL Server 変換先

SSIS パッケージをビルドする方法の学習を開始するには、チュートリアル「How to Create an ETL Package (ETL パッケージを作成する方法)」を参照してください。

OPENROWSET およびリンク サーバー

重要

Azure SQL データベース では、Excel から直接インポートすることはできません。 まず、データをテキスト (CSV) ファイルにエクスポートする必要があります。

Excel データ ソースに接続する ACE プロバイダー (旧称 Jet プロバイダー) は、対話型のクライアント側での使用を対象としています。 特に自動化されたプロセスまたは並列で実行中のプロセスで、SQL Server 上の ACE プロバイダーを使用する場合、予期しない結果が発生することがあります。

分散クエリ

Transact-SQL OPENROWSET または OPENDATASOURCE 関数を使用して、Excel ファイルから直接データを SQL Server にインポートします。 このような使用方法は、"分散クエリ" と呼ばれます。

重要

Azure SQL データベース では、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 のデータをインポートせずに Excel のデータにクエリを実行するには、標準の SELECT ... FROM ... 構文を使用します。

分散クエリの詳細については、次の記事を参照してください:

• 分散クエリ ¹
• OPENROWSET (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)

¹ 分散クエリは SQL Server でもサポートされていますが、この機能のドキュメントは更新されていません。

リンク サーバー

SQL Server から Excel ファイルへの永続的な接続を "リンク サーバー" として構成することもできます。 次の例は、既存の Excel のリンク サーバー EXCELLINK の Data ワークシートからデータを、Data_ls という名前の新しい SQL Server データベース テーブルにインポートしています。

USE ImportFromExcel;
GO
SELECT * INTO Data_ls FROM EXCELLINK...[Data$];
GO

リンク サーバーは、SQL Server Management Studio (SSMS) から作成するか、次の例に示すように、システム ストアド プロシージャ 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;

リンクサーバーの詳細については、次の記事を参照してください:

• リンク サーバーの作成 (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 コンマ区切りファイルから既存のデータベース テーブルにデータを読み込みます。

前述の「前提条件」セクションで説明したとおり、Excel データをテキストとしてエクスポートしてから BULK INSERT を使用してインポートする必要があります。 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 データベースの詳細と例については、次の記事を参照してください。

• BULK INSERT または OPENROWSET(BULK...) を使用して SQL Server にデータをインポートする
• BULK INSERT (Transact-SQL)

一括コピー ツール (BCP)

コマンド プロンプトから BCP ツールが実行されます。 次の例では、Data.csv コンマ区切りファイルから既存の Data_bcp データベース テーブルにデータを読み込みます。

前述の「前提条件」セクションで説明したとおり、Excel データをテキストとしてエクスポートしてから BCP を使用してインポートする必要があります。 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)

Azure Data Factory (ADF) のコピー ウィザードのページをステップ実行して、テキスト ファイルとして保存したデータをインポートします。

前述の「前提条件」セクションで説明したとおり、Excel データをテキストとしてエクスポートしてから Azure Data Factory を使用してインポートする必要があります。 Data Factory では、Excel ファイルを直接読み取ることはできません。

コピー ウィザードの詳細については、次の記事を参照してください:

• Data Factory コピー ウィザード
• チュートリアル: コピー アクティビティがあるパイプラインを Data Factory コピー ウィザードで作成する。

Azure Data Factory

Azure Data Factory に精通していて、コピー ウィザードを実行したくない場合は、テキスト ファイルから SQL Server または Azure SQL Database にコピーするコピー アクティビティでパイプラインを作成します。

前述の「前提条件」セクションで説明したとおり、Excel データをテキストとしてエクスポートしてから Azure Data Factory を使用してインポートする必要があります。 Data Factory では、Excel ファイルを直接読み取ることはできません。

これらの Data Factory のソースおよびシンクの使用に関する詳細については、次の記事を参照してください:

• ファイル システム
• SQL Server
• Azure SQL Database

Azure Data Factory でデータをコピーする方法の学習を開始するには、次の記事を参照してください:

• コピー アクティビティを使用したデータの移動
• チュートリアル: コピー アクティビティがあるパイプラインを Azure Portal で作成する

一般的なエラー

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 のインプロセスで読み込むことができません

このエラーは、32 ビット バージョンの OLE DB プロバイダーが 64 ビットの SQL Server でインストールされているときに発生します。 この問題を解決するには、OLE DB プロバイダーの 32 ビット バージョンをアンインストールして、代わりに 64 ビット バージョンをインストールします。

エラーの全文は次のとおりです。

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" でエラーが報告されました

このエラーは通常、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.

リンク サーバー "(null)" の OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" のデータ ソース オブジェクトを初期化できません

このエラーは通常、SQL Server プロセスとファイルの間に発生するアクセス許可の問題を示します。 SQL Server サービスを実行しているアカウントに、ファイルへのフル アクセス許可があることを確認してください。 デスクトップからファイルをインポートしてみてください。

エラーの全文は次のとおりです。

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)".

関連するコンテンツ

• 簡単な例によるインポートおよびエクスポート ウィザードの概要
• SQL Server Integration Services (SSIS) を使用して、Excel からデータをインポートする、または Excel にデータをエクスポートする
• bcp ユーティリティ
• コピー アクティビティを使用したデータの移動