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

[アーティクル]
04/03/2023

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

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

方法の一覧

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

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

最初にテキストにエクスポートする (SQL Server および SQL Database)	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) パッケージとして設定を保存することもできます。

SQL Server Management Studioで、 SQL Serverデータベースエンジンのインスタンスに接続します。
[データベース] を展開します。
データベースを右クリックします。
[タスク] を選択します。
[データのインポート] または [データのエクスポート] を選択します。

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

Connect to an Excel data source

詳細については、次を参照してください。

Integration Services (SSIS)

SQL Server Integration Services (SSIS) に精通していて、SQL Server インポートおよびエクスポートウィザードを実行したくない場合は、Excel ソースおよびデータフロー内の SQL Server 変換先を使用する SSIS パッケージを作成します。

詳細については、次を参照してください。

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

Components in the data flow

OPENROWSET およびリンクサーバー

重要

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

Note

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

分散クエリ

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

重要

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

分散クエリを実行する前に、次の例で示すように、ad hoc distributed queries サーバー構成オプションを有効にする必要があります。詳しくは、「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 ... 構文を使用します。

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

分散クエリ (分散クエリは 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

リンクサーバーは、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 のリンクサーバーおよび分散クエリで Excel を使用する方法

前提条件 - 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 コンマ区切りファイルから既存のデータベーステーブルにデータを読み込みます。

前述の前提条件のセクションで説明したとおり、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 と SQL Database の詳細と例については、次の記事を参照してください:

BCP ツール

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

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

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

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

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

Azure Data Factory

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

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

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

リンクサーバー "(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)".

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

方法の一覧

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

Integration Services (SSIS)

OPENROWSET およびリンクサーバー

分散クエリ

リンクサーバー

前提条件 - Excel データをテキストとして保存する

フラットファイルのインポートウィザード

BULK INSERT コマンド

BCP ツール

コピーウィザード (ADF)

Azure Data Factory

一般的なエラー

Microsoft.ACE.OLEDB.12.0」が登録されていません

リンクサーバー "(null)" の OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" のインスタンスを作成できません

32 ビットの OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" を 64 ビットの SQL Server のインプロセスで読み込むことができません

リンクサーバー "(null)" の OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" でエラーが報告されました。

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

次のステップ

フィードバック

フィードバック

その他のリソース

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

方法の一覧

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

Integration Services (SSIS)

OPENROWSET およびリンク サーバー

分散クエリ

リンク サーバー

前提条件 - Excel データをテキストとして保存する

フラット ファイルのインポート ウィザード

BULK INSERT コマンド

BCP ツール

コピー ウィザード (ADF)

Azure Data Factory

一般的なエラー

Microsoft.ACE.OLEDB.12.0」が登録されていません

リンク サーバー "(null)" の OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" のインスタンスを作成できません

32 ビットの OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" を 64 ビットの SQL Server のインプロセスで読み込むことができません

リンク サーバー "(null)" の OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" でエラーが報告されました。

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

次のステップ

フィードバック

フィードバック

その他のリソース

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

OPENROWSET およびリンクサーバー

リンクサーバー

フラットファイルのインポートウィザード

コピーウィザード (ADF)

リンクサーバー "(null)" の OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" のインスタンスを作成できません

リンクサーバー "(null)" の OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" でエラーが報告されました。

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