現在オフラインです。再接続するためにインターネットの接続を待っています

お使いのブラウザーはサポートされていません

このサイトを利用するには、ブラウザーを更新する必要があります。

Internet Explorer を最新バージョンに更新する

[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 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 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 PRB: Excel Values Returned as NULL Using DAO OpenRecordset
先頭に戻る
関連情報
データ ソースとして Excel を使用する方法の詳細を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
257819 [HOWTO] VB または VBA から ADO を Excel データで使用する
Excel へのデータの転送方法に関連する情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
247412 INFO: Methods for Transferring Data to Excel from Visual Basic
246335 HOWTO: Transfer Data from an ADO Recordset to Excel with Automation
319951 HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services
306125 HOW TO: Import Data from SQL Server into Microsoft Excel
先頭に戻る
関連情報
この資料は米国 Microsoft Corporation から提供されている Knowledge Base の Article ID 321686 (最終更新日 2002-09-11) をもとに作成したものです。

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

excel sql ado dts sqloledb jet
プロパティ

文書番号:321686 - 最終更新日: 05/05/2004 16:20:05 - リビジョン: 3.1

  • 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
フィードバック
ript> > + (window.location.protocol) + "//c.microsoft.com/ms.js'><\/script>");