文書番号: 321686 - 最終更新日: 2004年5月5日 - リビジョン: 3.1

[HOWTO] DTS: Excel から SQL Server にデータをインポートする方法

対象製品
お知らせお使いのオペレーティング システムには適用しない情報が含まれている場合があります。
この記事は、以前は次の ID で公開されていました: JP321686

目次

すべて展開する | すべて折りたたむ

概要

この資料では、Microsoft Excel ワークシートから Microsoft SQL Server データベースにデータをインポートするさまざまな方法について説明します。

先頭へ戻る

技法の説明

この資料のサンプルでは、以下の技法を使用して Excel データをインポートします。
  • SQL Server DTS (データ変換サービス)
  • SQL Server リンク サーバー
  • SQL Server 分散クエリ
  • ActiveX データ オブジェクト (ADO) および Microsoft OLE DB Provider for SQL Server
  • ADO および Microsoft OLE DB Provider for Jet 4.0
先頭へ戻る

要件

推奨するハードウェア、ソフトウェア、ネットワーク インフラストラクチャ、および必要な Service Pack の概略の一覧を以下に示します。
  • Microsoft SQL Server 7.0 または Microsoft SQL Server 2000
  • Microsoft Visual Basic 6.0 (Visual Basic を使用した ADO サンプル用)
この資料の一部は、以下のトピックに詳しいユーザーを想定しています。
  • データ変換サービス
  • リンク サーバーと分散クエリ
  • Visual Basic を使用する ADO 開発
先頭へ戻る

サンプル

インポートと追加

この資料で使用しているサンプル SQL ステートメントは、SELECT...INTO...FROM 構文を使用して Excel のデータを新規の SQL Server テーブルにインポートするクエリについて説明しています。これらのコード サンプルで示したインポート元オブジェクトとインポート先オブジェクトを参照したまま、INSERT INTO...SELECT...FROM 構文を使用してサンプル ステートメントを追加クエリに変換できます。

DTS の使用

SQL Server DTS (データ変換サービス) インポート ウィザードを使用すると、Excel データを SQL Server のテーブルにインポートできます。ウィザードの中で Excel のソース テーブルを選択するときは、ドル記号 ($) が付いた Excel オブジェクト名 (たとえば Sheet1$) がワークシートを表し、ドル記号が付いていないオブジェクト名は Excel の名前付き範囲を表すことに注意してください。

リンク サーバーの使用

クエリを簡単にするために、Excel ブックを SQL Server のリンク サーバーとして構成できます。 関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
306397? (http://support.microsoft.com/kb/306397/EN-US/ ) HOWTO: Use Excel with SQL Server Linked Servers and Distributed Queries
次のコードは、Excel リンク サーバー "EXCELLINK" の Customers ワークシートのデータを XLImport1 という新しい SQL Server テーブルにインポートします。 
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
また、以下のように OPENQUERY を使用することで、インポート元に対してパススルー方式でクエリを実行できます。 
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')

分散クエリの使用

リンク サーバーとしての Excel ブックへの常時接続を構成しない場合、OPENDATASOURCE 関数または OPENROWSET 関数を使用して、特定の目的で使用するデータをインポートできます。次のコード サンプルは、Excel の Customers ワークシートのデータを新しい SQL Server テーブルにインポートします。
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

ADO および SQLOLEDB の使用

ADO アプリケーションで Microsoft OLE DB for SQL Server (SQLOLEDB) を使用して SQL Server に接続している場合、「分散クエリの使用 」で説明した "分散クエリ" 構文を使用して SQL Server に Excel データをインポートできます。

次の Visual Basic 6.0 コード サンプルには、ActiveX Data Objects (ADO) へのプロジェクト参照を追加する必要があります。このコード サンプルでは、SQLOLEDB 接続で OPENDATASOURCE と OPENROWSET を使用する方法も例示しています。 
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
        "Initial Catalog=<database>;User ID=<user>;Password=<password>"

    'OPENDATASOURCE を使用してインポートします。
    strSQL = "SELECT * INTO XLImport6 FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=C:\test\xltest.xls;" & _
        "Extended Properties=Excel 8.0')...[Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'OPENROWSET とオブジェクト名を使用してインポートします。
    strSQL = "SELECT * INTO XLImport7 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "[Customers$])"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'OPENROWSET と SELECT クエリを使用してインポートします。
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "'SELECT * FROM [Customers$]')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing

ADO および Jet プロバイダの使用

前のセクションのサンプルでは、Excel から SQL Server へデータをインポートする為に、ADO を使用しています。 ADO では、 SQLOLEDB プロバイダを使用して転送先の SQL Server に接続しています。また、OLE DB Provider for Jet 4.0 を使用して、転送元の Excel に接続することもできます。

Jet データベース エンジンは、以下の 3 つの書式を持つ特別な構文を使用して、SQL ステートメントの中で外部データベースを参照できます。
  • [Microsoft Access データベースの完全パス].[テーブル名]
  • [ISAM 名;ISAM 接続文字列].[テーブル名]
  • [ODBC;ODBC 接続文字列].[テーブル名]
ここでは、3 番目の書式を使用して、インポート先の SQL Server データベースへの ODBC 接続を行います。ODBC DSN (データ ソース名) か、DSN を持たない接続文字列を使用できます。 
DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]
次の Visual Basic 6.0 コード サンプルでは、ADO へのプロジェクト参照を追加する必要があります。このコード サンプルでは、Jet 4.0 プロバイダを使用して、ADO 接続で Excel データを SQL Server にインポートする方法を例示しています。 
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltest.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Jet プロバイダを使用してインポートします。
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    cn.Close
    Set cn = Nothing
上記の構文を使用して、他の Microsoft Access データベース、ISAM (索引順次アクセス方式) ("デスクトップ") データベース、または ODBC データベースに Excel データをインポートできます。この構文は、Jet プロバイダに対応しています。

先頭へ戻る

トラブルシューティング

  • (Sheet1$ など) ドル記号 ($) の付いた Excel オブジェクト名はワークシートを表し、ドル記号の付かないオブジェクト名は Excel の名前付き範囲を表していることに注意してください。
  • SELECT クエリではなくテーブル名を使用して Excel のインポート元データを指定した場合など、状況によってはインポート先の SQL Server テーブルの列が再度アルファベット順に並び替えられます。 Jet プロバイダの問題の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    299484? (http://support.microsoft.com/kb/299484/EN-US/ ) PRB: Columns Are Sorted Alphabetically When You Use ADOX to Retrieve Columns of Access Table
  • Jet プロバイダの問題の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。 Jet プロバイダは Excel の列にテキストと数値のデータが混在していると判断すると、個数が多い方のデータ型を選択して、選択された型に一致しない値を NULL として返します。 この問題を回避する方法の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    194124? (http://support.microsoft.com/kb/194124/EN-US/ ) PRB: Excel Values Returned as NULL Using DAO OpenRecordset
先頭へ戻る

関連情報

データ ソースとして Excel を使用する方法の詳細を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
257819? (http://support.microsoft.com/kb/257819/EN-US/ ) HOWTO: Use ADO with Excel Data from Visual Basic or VBA
257819? (http://support.microsoft.com/kb/257819/JA/ ) [HOWTO] VB または VBA から ADO を Excel データで使用する
Excel へのデータの転送方法に関連する情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
295646? (http://support.microsoft.com/kb/295646/EN-US/ ) HOWTO: Transfer Data from ADO Data Source to Excel with ADO
247412? (http://support.microsoft.com/kb/247412/EN-US/ ) INFO: Methods for Transferring Data to Excel from Visual Basic
246335? (http://support.microsoft.com/kb/246335/EN-US/ ) HOWTO: Transfer Data from an ADO Recordset to Excel with Automation
319951? (http://support.microsoft.com/kb/319951/EN-US/ ) HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services
306125? (http://support.microsoft.com/kb/306125/EN-US/ ) HOW TO: Import Data from SQL Server into Microsoft Excel
先頭へ戻る

関連情報

この資料は米国 Microsoft Corporation から提供されている Knowledge Base の Article ID 321686? (http://support.microsoft.com/kb/321686/EN-US/ ) (最終更新日 2002-09-11) をもとに作成したものです。

この資料に含まれているサンプル コード/プログラムは英語版を前提に書かれたものをありのままに記述しており、日本語環境での動作は確認されておりません。

先頭へ戻る
この資料は以下の製品について記述したものです。
  • Microsoft Excel 2000 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
先頭へ戻る
キーワード:?
kbatm kbgrpdsvbdb kbhowtomaster kbjet KB321686
先頭へ戻る
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。（Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"