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 および 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) パッケージとして設定を保存することを選択することもできます。

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

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

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

  4. [タスク] にカーソルを合わせます。

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

    ウィザードの起動 (SSMS)

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

Excel データ ソースに接続する

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

Integration Services (SSIS)

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

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

SSIS パッケージをビルドする方法の学習を開始するには、チュートリアル「How to Create an ETL Package (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 サーバー構成オプションを有効にする必要があります。 詳しくは、「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 から Excel ファイルへの永続的な接続を "リンク サーバー" として構成することもできます。 次の例は、既存の Excel のリンク サーバー EXCELLINKData ワークシートからデータを、Data_ls という名前の新しい SQL Server データベース テーブルにインポートしています。

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

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

前述の前提条件のセクションで説明したとおり、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 にコピーするコピー アクティビティでパイプラインを作成します。

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

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

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

一般的なエラー

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

このエラーは OLEDB プロバイダーがインストールされていない場合に発生します。 Microsoft Access データベース エンジン 2010 再頒布可能パッケージからインストールします。 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)".

次のステップ