SQL Server Integration Services (SSIS) を使用して、Excel からデータをインポートする、または Excel にデータをエクスポートする

  • [アーティクル]

適用対象:SQL Server Azure Data Factory の SSIS Integration Runtime

この記事では、Excel からデータをインポートするとき、または SQL Server Integration Services (SSIS) を使って Excel にデータをエクスポートするときに、指定する必要のある接続情報および構成する必要のある設定について説明します。

以下のセクションには、SSIS で Excel を正常に使用するため、または一般的な問題を理解して解決するために必要な情報が含まれています。

  1. 使用できるツール。

  2. 必要なファイル。

  3. SSIS を使用して Excel から、または Excel にデータを読み込むときに、提供する必要がある接続情報、および構成する必要がある設定。

  4. 既知の問題と制限事項。

使用できるツール

SSIS で次のいずれかのツールを使って、Excel からデータをインポートしたり、Excel にデータをエクスポートしたりできます。

Excel に接続するために必要なファイルを取得する

SSIS を使用して Excel からデータをインポートしたり、データを Excel にエクスポートするには、事前に Excel の接続コンポーネントをダウンロードする必要があります (まだインストールされていない場合)。 Excel の接続コンポーネントは、既定ではインストールされません。

環境に追加のコンポーネントが必要かどうかを確認するには、「Access ODBC、OLEDB、または DAO インターフェイスを Office クイック実行の外部で使用できない」の表を使用します。

注: Office システム ドライバーは、特定のシナリオでのみサポートされています。具体的なガイダンスについては、「Office のサーバー サイド オートメーションについて」を参照してください。

データ ソースとして Excel を指定する

最初の手順は、Excel に接続することを指定することです。

SSIS

SSIS で、Excel ソースまたは変換先ファイルに接続するための Excel 接続マネージャーを作成します。 接続マネージャーを作成するには、いくつかの方法があります。

  • [接続マネージャー] 領域内を右クリックし、 [新しい接続] を選択します。 [SSIS 接続マネージャーの追加] ダイアログ ボックスで [EXCEL] 、 [追加] の順に選択します。

  • [SSIS] メニューで [新しい接続] を選択します。 [SSIS 接続マネージャーの追加] ダイアログ ボックスで [EXCEL] 、 [追加] の順に選択します。

  • Excel ソース エディターまたは Excel 変換先エディターの [接続マネージャー] ページで、Excel ソースまたは Excel 変換先を構成するときに、同時に接続マネージャーを作成します。

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

インポートおよびエクスポート ウィザードの [データ ソースの選択] または [変換先の選択] ページで、 [データ ソース] リストから [Microsoft Excel] を選択します。

データ ソースのリストに Excel が表示されない場合は、32 ビットのウィザードを実行していることを確認してください。 Excel 接続コンポーネントは、通常、32 ビット ファイルで、64 ビットのウィザードでは表示されません。

Excel ファイルとファイル パス

最初に指定する情報は、Excel ファイルのパスとファイル名です。 この情報は、SSIS パッケージの Excel 接続マネージャー エディター、またはインポートとエクスポート ウィザードの [データ ソースの選択] または [変換先の選択] のページで指定します。

次の形式でパスとファイル名を入力します。

  • ローカル コンピューター上のファイルの場合、C:\TestData.xlsx です。

  • ネットワーク共有上のファイルの場合、\\Sales\Data\TestData.xlsx です。

または、 [参照] をクリックして、 [ファイルを開く] ダイアログ ボックスを使用してワークシートを検索します。

重要

パスワードで保護された Excel ファイルには接続できません。

[Excel バージョン]

2 番目に指定する情報は、Excel ファイルのバージョンです。 この情報は、SSIS パッケージの Excel 接続マネージャー エディター、またはインポートとエクスポート ウィザードの [データ ソースの選択] または [変換先の選択] のページで指定します。

ファイルを作成するために使用した Microsoft Excel のバージョンか、別の互換性のあるバージョンを指定します。 たとえば、2016 接続コンポーネントのインストールに問題がある場合、2010 コンポーネントをインストールして、このリストで [Microsoft Excel 2007-2010] を選択できます。

古いバージョンの接続コンポーネントしかインストールされていない場合は、それより新しいバージョンの Excel をリストで選択することはできません。 Excel バージョン リストには、SSIS によってサポートされている Excel のすべてのバージョンが含まれています。 このリスト内に項目があっても、必要な接続コンポーネントがインストールされているとは限りません。 たとえば、2016 接続コンポーネントをインストールしていなくても、リストには Microsoft Excel 2016 が表示されます。

[先頭行に列名を含める]

Excel からデータをインポートしている場合、次の手順は、データの最初の行に列の名前が含まれているかどうかを示すことです。 この情報は、SSIS パッケージの Excel 接続マネージャー エディター、またはインポートとエクスポート ウィザードの [データ ソースの選択] ページで指定します。

  • ソース データに列名が含まれていないため、このオプションを無効にすると、ウィザードでは F1、F2 などが列見出しとして使用されます。
  • データに列名が含まれているのにこのオプションを無効にすると、ウィザードでは列名がデータの最初の行としてインポートされます。
  • データに列名が含まれていないのにこのオプションを有効にすると、ウィザードではソース データの最初の行が列名として使用されます。 この場合、ソース データの最初の行は、データ自体には含まれなくなります。

Excel からデータをエクスポートする場合にこのオプションを有効にすると、エクスポートされたデータの最初の行に列名が含まれます。

ワークシートと範囲

データのソースまたは変換先として使用できる Excel オブジェクトには、ワークシート、名前付き範囲、またはそのアドレスを使って指定する名前のない範囲のセルの 3 種類があります。

  • 使用できます。 ワークシートを指定するには、シート名の末尾に $ 文字を付加し、文字列を区切り文字で囲みます (例: [Sheet1$] )。 または、リスト内の既存のテーブルとビューから $ 文字で終わる名前を探します。

  • 名前付き範囲。 名前付き範囲を指定するには、範囲名を指定します (例: MyDataRange)。 または、リスト内の既存のテーブルとビューから $ 以外の文字で終わる名前を探します。

  • 名前のない範囲。 名前のないセルの範囲を指定するには、シート名の末尾に $ 文字を付加し、範囲の指定を追加し、文字列を区切り文字で囲みます (例: [Sheet1$A1:B4] )。

データのソースまたは変換先として使用する Excel オブジェクトの種類を選択または指定するには、次のいずれかの操作を行います。

SSIS

SSIS で、Excel ソース エディターまたは Excel 変換先エディターの [接続マネージャー] ページで、次のいずれかの操作を行います。

  • ワークシートまたは名前付き範囲を使用するには、 [データ アクセス モード] に [テーブルまたはビュー] を選択します。 次に、 [Excel シートの名前] リストで、ワークシートまたは名前付き範囲を選択します。

  • そのアドレスを使用して指定する名前のない範囲を使用するには、 [データ アクセス モード] に [SQL コマンド] を選択します。 次に、 [SQL コマンド テキスト] フィールドに、次の例のようなクエリを入力します。

    SELECT * FROM [Sheet1$A1:B5]

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

インポートおよびエクスポート ウィザードで、次のいずれかの操作を行います。

  • Excel からインポートする場合は、次のいずれかの操作を行います。

    • ワークシートまたは名前付き範囲を使用するには、 [テーブルのコピーまたはクエリの指定] ページで、 [1 つ以上のテーブルまたはビューからデータをコピーする] を選択します。 次に、 [コピー元のテーブルおよびビューを選択] ページの [ソース] 列で、ソースのワークシートと名前付き範囲を選択します。

    • そのアドレスを使用して指定する名前のない範囲を使用するには、 [テーブルのコピーまたはクエリの指定] ページで、 [転送するデータを指定するためのクエリを記述する] を選択します。 次に、 [基になるクエリの指定] ページで、次の例のようなクエリを指定します。

      SELECT * FROM [Sheet1$A1:B5]

  • Excel にエクスポートする場合は、次のいずれかの操作を行います。

    • ワークシートまたは名前付き範囲を使用するには、 [コピー元のテーブルおよびビューを選択] ページの [変換先] 列で、エクスポート先のワークシートおよび名前付き範囲を選択します。

    • そのアドレスを使用して指定する名前のない範囲を使用するには、 [コピー元のテーブルおよびビューを選択] ページの [変換先] 列で、区切り記号は使わずに、Sheet1$A1:B5 の形式で範囲を入力します。 区切り記号はウィザードによって追加されます。

インポートまたはエクスポートする Excel オブジェクトを選択または入力すると、ウィザードの [コピー元のテーブルおよびビューを選択] ページで次の操作も行えるようになります。

  • [マッピングの編集] を選択して、変換元と変換先の間の列マッピングを確認する。

  • [プレビュー] を選択して、サンプル データが期待どおりになっていることをプレビューで確認する。

データ型に関する問題

データ型

Excel ドライバーでは、データ型の限定されたセットのみを認識します。 たとえば、すべての数値列は倍精度浮動小数点型 (DT_R8) として解釈され、すべての文字列の列 (メモ列以外) は 255 文字の Unicode 文字列 (DT_WSTR) として解釈されます。 SSIS では、Excel データ型を次のようにマップします。

  • Numeric - 倍精度浮動小数点数 (DT_R8)

  • Currency - 通貨 (DT_CY)

  • Boolean - ブール (DT_BOOL)

  • Date/time - 日時 (DT_DATE)

  • String - Unicode 文字列、長さ 255 (DT_WSTR)

  • Memo - Unicode テキスト ストリーム (DT_NTEXT)

データ型と長さの変換

SSIS では、データ型の暗黙的な変換は行われません。 したがって、派生列変換またはデータ変換の変換を使用して、Excel データを明示的に変換してから Excel 以外の変換先に読み込むか、Excel 以外のソースからデータを変換してから Excel 変換先に読み込む必要があります。

必要な変換の例を次に示します。

  • 特定のコードページを使用した Unicode Excel 文字列の列と Unicode 以外の文字列の列間の変換

  • 255 文字の Excel 文字列の列と異なる長さの文字列の列間の変換

  • 倍精度の Excel 数値列と他の型の数値列の列間の変換

ヒント

インポートおよびエクスポート ウィザードを使用していて、データにこれらの変換がいくつか必要な場合は、ウィザードによって必要な変換が構成されます。 そのため、SSIS パッケージを使用する場合でも、インポートおよびエクスポート ウィザードを使用して初期パッケージを作成しておくと役立つ場合があります。 ウィザードを使用すると、接続マネージャー、ソース、変換、および変換先を作成および構成できます。

インポートに関する問題

空の行

ソースとしてワークシートまたは名前付き範囲を指定すると、ドライバーは、ワークシートまたは範囲の左上の空でない最初のセルから、連続したセルのブロックを読み取ります。 そのため、データ行は行 1 で開始する必要はありませんが、ソース データで空の行を使用することはできません。 たとえば、列ヘッダーとデータ行の間に空の行を入れたり、ワークシートの上部のタイトルの後に空の行を入れることはできません。

データの上に空の行がある場合は、ワークシートとしてデータにクエリを実行できません。 Excel では、データの範囲を選択し、その範囲に名前を割り当ててから、ワークシートではなく名前付き範囲に対してクエリを実行する必要があります。

不足している値

Excel ドライバーは、指定した変換元の特定の行数 (既定では 8 行) を読み取り、各列のデータ型を推測します。 1 つの列に複数のデータ型が混在している可能性がある場合、特に数値データとテキスト データが混合している場合に、Excel ドライバーは数が多い方のデータ型を優先して判定し、それ以外のデータ型のデータが含まれるセルについては NULL 値を返します (同数の場合は、数値型が優先されます)。Excel ワークシートでのセル書式のほとんどのオプションは、このデータ型の判定に影響しません。

Excel ドライバーのこの動作を変更するには、すべての値をテキストとしてインポートするようにインポート モードを指定します。 インポート モードを指定するには、[プロパティ] ウィンドウで、Excel 接続マネージャーの接続文字列内の拡張プロパティの値に IMEX=1 を追加します。

切り捨てられたテキスト

Excel の列にテキスト データが含まているとドライバーが判定した場合、ドライバーはサンプリングした最も長い値に基づいて、データ型 (文字列またはメモ) を選択します。 ドライバーがサンプリングした行で 255 文字より長い値が検出されなかった場合、その列はメモ列ではなく、255 文字の文字列の列として扱われます。 このため、255 文字より長い値があると、切り捨てられる場合があります。

データを切り捨てずにメモ列からインポートするには、2 つのオプションがあります。

  • サンプリングされた行の少なくとも 1 つのメモ列に、255 文字より長い値が含まれていることを確認します。

  • このような行を含めるには、ドライバーによってサンプリングされる行数を増やします。 サンプリングする行数を増やすには、次のレジストリ キーの下で TypeGuessRows の値を増やします。

再配布可能なコンポーネントのバージョン レジストリ キー
Excel 2016 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
Excel 2010 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

エクスポートに関する問題

新しいエクスポート先ファイルを作成する

SSIS

作成する新しい Excel ファイルのパスとファイル名を使用して、Excel 接続マネージャーを作成します。 次に、Excel 変換先エディターで、 [Excel シートの名前] に [新規] を選択して、エクスポート先のワークシートを作成します。 この時点で、SSIS によって指定したワークシートを持つ新しい Excel ファイルが作成されます。

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

[変換先の選択] ページで、 [参照] を選択します。 [ファイルを開く] ダイアログ ボックスで、新しい Excel ファイルを作成するフォルダーに移動し、新しいファイルの名前を指定してから、 [開く] を選択します。

十分な大きさの範囲をエクスポートする

変換先として範囲を指定するときに、範囲の "列数" がソース データより少ないと、エラーが発生します。 一方、指定した範囲がソース データより "行数" が少ない場合、エラーは発生せず、ウィザードでは行の記述が続行され、新しい行数に合わせて行の定義が拡張されます。

長いテキストの値をエクスポートする

255 文字を超える文字列を Excel 列に正常に保存するには、変換先の列のデータ型を 文字列型 ではなく メモ型としてドライバーが認識する必要があります。

  • 既存の変換先のテーブルに既にデータ行が含まれている場合、ドライバーによってサンプリングされた先頭の数行のメモ列に、255 文字を超える値のインスタンスが 1 つ以上含まれている必要があります。

関連コンテンツ

この記事で説明した手順とコンポーネントに関する詳細は、次の記事を参照してください。

SSIS について

Excel 接続マネージャー
Excel ソース
Excel 変換先
Foreach ループ コンテナーを使用して Excel のファイルおよびテーブルをループ処理する
スクリプト タスクを使用した Excel ファイルの操作

SQL Server インポートおよびエクスポート ウィザードについて

Excel データ ソースに接続する
簡単な例によるインポートおよびエクスポート ウィザードの概要

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