SQL Server データを Excel ファイルにインポート/エクスポートする方法

SQL Server データを Excel ファイルにエクスポート/インポートする方法はいくつかあります。この記事では、SQL Server のデータを Excel ファイルにインポートおよびエクスポートするいくつかの方法を説明します。

SQL Server のデータを Excel ファイルにエクスポートするには、
- SQL Server インポート/エクスポートウィザード
- T-SQL コード
- ApexSQL Complete からの Excel へのエクスポート
データ接続ウィザードを使用して SQL Server データを Excel にインポートする

SQL Server インポートおよびエクスポートウィザードを使用して SQL Server データを Excel ファイルにエクスポートする

SQL Server Management Studio (SSMS) には、SQL Server のデータをエクスポートする機能があります。あるデータソースから別のデータソースにデータをエクスポートするための機能があります。今回のケースでは、SQL Server からのデータは、エクスポートウィザードを使って Excel ファイルにコピーされます。

この機能を使用するには、オブジェクトエクスプローラーに移動し、任意のデータベース (例: AdventureworksDW2016CTP3) を右クリックします。

これにより、SQL Server Import and Export Wizard ウィンドウが開きます

SQL Server データの Excel ファイルへのエクスポートを続行するには、[Next] ボタンをクリックします。すると、「データソースの選択」ウィンドウが表示されます。

[データソース] ドロップダウンボックスから [SQL Server Native Client 11.0] を選択します。サーバー名」のドロップダウンボックスで、データをコピーする必要のあるSQL Serverインスタンスを選択します。認証］セクションでは、データソース接続の認証モードを選択し、［データベース］ドロップダウンボックスでは、データのコピー元となるデータベースを選択します。

これにより、データソースからデータをコピーする場所を指定できる「コピー先を選択」ウィンドウが表示されます：

「コピー先」ドロップダウンボックスで、Microsoft Excelの項目を選択します。 Excelファイルのパス」で、SQL Serverデータソースからコピーされたデータの保存先（SQL Data.xlsx）を選択します。 Excelバージョンのドロップダウンボックスでは、Microsoft Excelのワークシートのバージョンを選択します。これらがすべて設定されたら、「Next」ボタンを押して続行します。

The operation could not be completed.
ADDITIONAL INFORMATION:
「Microsoft.ACE.OLEDB.12.0」プロバイダーがローカルマシンに登録されていません。 (System.Data)

これは、SSMS が 32 ビットアプリケーションであり、SSMS 経由で Export Wizard を起動すると、32 ビットバージョンの Export Wizard が起動するために起こります。一方、SSMS がインストールされているマシン (オペレーティングシステム) は 64 ビット版であり、インストールされている SQL Server も 64 ビット版です。

この問題を解決するには、SSMS を終了し、[スタート] メニューから 64 ビット版の SQL Server Import and Export Data を探します:

この SQL Server Import and Export Wizard を起動すると、SSMS を介して使用されるのと同じウィンドウが表示されます。これまでのオプションをすべて設定したら、「次へ」ボタンをクリックします。テーブルコピーまたはクエリの指定] ウィンドウが表示されます:

このウィンドウでは、1 つ以上のテーブルとビューからデータをコピーするか、クエリの結果をコピーするかを指定できます。

[1 つ以上のテーブルまたはビューからデータをコピー] ラジオボタンを選択し、[次へ] ボタンをクリックします。

お気づきのように、テーブル/ビューの隣にあるチェックボックスがチェックされるとすぐに、テーブル/ビューの名前が [送信先] 列にコピーされます。

どのデータが Excel ファイルに生成されるかをプレビューするには、[プレビュー] ボタンをクリックします。

どのテーブル/ビューのデータがExcelファイルにコピーされるかを選択した後、「次へ」ボタンをクリックします。

[Save as Run Package]ウィンドウで、[Run immediately]チェックボックスをチェックし、[Next]ボタンをクリックします:

[Complete the Wizard]ウィンドウには、エクスポート操作のために選択されたすべてのオプションが表示されます。 SQL Server のデータを Excel ファイルにエクスポートするプロセスを確定するには、[完了] ボタンをクリックします。最後の [SQL Server Import and Export Wizard] ウィンドウには、データが正常にエクスポートされたか、またはデータのエクスポート中に何らかのエラーが発生したかの状態が表示されます:

今回のケースでは、データは正常に SQL Data.

T-SQL コードを使用して SQL Server データを Excel ファイルにエクスポートする

Transact-SQL OPENROWSET を使用して、SSMS を介して SQL Server データを Excel ファイルにエクスポートすることができます。クエリエディターで次のコードを入力して実行します：

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ') 
SELECT * FROM dbo.DimScenario

ただし、上記のコードを実行すると、次のようなエラーが発生することがあります。

Msg 15281, Level 16, State 1, Line 1
SQL Serverは、このサーバーのセキュリティ構成の一部としてこのコンポーネントがオフになっているため、コンポーネント「Ad Hoc Distributed Queries」のSTATEMENT「OpenRowset/OpenDatasource」へのアクセスをブロックしました。システム管理者は、sp_configure を使用して「アドホック分散クエリ」の使用を有効にすることができます。 Ad Hoc Distributed Queries」を有効にする方法の詳細については、SQL Server Books Onlineで「Ad Hoc Distributed Queries」を検索してください。

このエラーを解決するには、「Ad Hoc Distributed Queries」オプションを有効にする必要があります。これは、sp_configureプロシージャを使用して、クエリエディタで次のSQLコードを実行することで実現できます。

EXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'Ad Hoc Distributed Queries', 1RECONFIGURE

上記のコードを実行すると、「詳細オプションの表示」と「アドホック分散クエリ」のオプションが有効になっていることを示す以下のメッセージが表示されます：

構成オプションの「詳細オプションの表示」が0から1に変更されました。 RECONFIGURE ステートメントを実行してインストールしてください。
構成オプションの「アドホック分散クエリ」が 0 から 1 に変更されました。

ここで、再度コードを実行すると

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ') 
SELECT * FROM dbo.DimScenario

次のようなエラーが表示されることがあります：

OLE DB プロバイダー「Microsoft.ACE.OLEDB.12..0″ for linked server “(null)” returned message “The Microsoft Access database engine cannot open or write to the file “. すでに他のユーザーによって排他的に開かれているか、そのデータを表示および書き込みする権限が必要です。”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12 .この問題を解決する方法の詳細は、「SQL Server リンクサーバーを使用して Excel データをクエリする方法」のページを参照してください。

この問題を解決する方法の 1 つは、SSMS を管理者として開き、コードを再度実行することです。

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

これを解決するには、予定しているエクセルファイル (例: SQL Data.xlsx) を開きます。xlsxなど）を開き、DimScenarioテーブルの列名を表す列名を入力します。

SQL Data.xlsxファイルを閉じ、再度SQL Data.xlsxを実行します。xlsxファイルを閉じて、もう一度コードを実行します:

今度は、次のようなメッセージが表示されます:

(3 rows affected)

最後に、SQL ServerテーブルのデータがExcelファイルにコピーされます。

[データ接続ウィザード]ダイアログを使用して SQL Server データを Excel にインポートする

SQL Server テーブルから Excel ファイルにデータをコピーするもう 1 つの方法は、Excel から[データ接続ウィザード]ダイアログを使用することです。

そのためには、データをインポートしたいExcelファイル（例：SQL Data.xlsx）を開きます。

「データ接続ウィザード」ダイアログが表示されます。サーバー名」に、データをコピーする必要のあるSQL Serverインスタンスの名前を入力します。

[Select the database that contains the data you want]ドロップダウンボックスから、データのコピー元となるデータベースを選択します。グリッドには、利用可能なすべてのテーブルとビューが表示されます。目的のテーブル／ビュー（例：DimScenario）を選択し、「次へ」ボタンをクリックして進みます。

このウィンドウでは、すべてをそのままにして、[完了]ボタンを押します：

[データのインポート]ダイアログで、[テーブル]ラジオボタンを選択し、[データをどこに置きますか？セクションで、[既存のワークシート] ラジオボタンを選択し、[OK] ボタンを押します:

[OK] ボタンを押すと、DimScenario テーブルのデータが SQL Data.xslx ファイルにコピーされます。xslx ファイル:

ApexSQL Complete の Export to Excel を使用して SQL Server データを Excel ファイルにエクスポートする

SSMS および Visual Studio 用のアドインである ApexSQL Complete の Export to Excel 機能を使用して、SQL Server テーブルから Excel ファイルへのデータのエクスポートをワンクリックで行うことができます。

クエリエディターで、次のコードを入力して実行します：

SELECT * FROM dbo.DimScenario ds

結果グリッドから、Excelファイルにエクスポートしたいデータを選択し、結果グリッドで右クリックして、コンテキストメニューから「Excelにエクスポート」オプションを選択します。

これにより、新しいExcelワークブックが開き、結果グリッドから選択されたデータが最初のワークシートにコピーされます。

ApexSQL Complete による Excel へのエクスポートの詳細については、Excel へのエクスポートのページを参照してください。

Guinguette Marais Poitevin

Blog