Excel から SQL Server にデータをインポートする方法

適用対象: Microsoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Standard Edition 詳細

概要


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

技法の説明

この資料のサンプルでは、以下の技法を使用して Excel データをインポートします。

  • SQL Server Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • 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 SQL Server 2005 の利用可能なインスタンス
  • Visual Basic を使用する ADO サンプル用に Microsoft Visual Basic 6.0
この資料の一部は、次のトピックについて詳しい知識のあるユーザーを対象としています。

  • データ変換サービス
  • リンク サーバーと分散クエリ
  • Visual Basic での ADO 開発

サンプル

インポートと追加

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

DTS または SSIS の使用

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

リンク サーバーの使用

クエリを単純化するため、SQL Server 内のリンク サーバーとして Excel ブックを構成できます。

関連情報を参照するには、以下のマイクロソフト サポート技術情報番号をクリックしてください。

306397 SQL Server のリンク サーバーおよび分散クエリで Excel を使用する方法
次のコードは、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 に接続している場合、「分散クエリの使用」セクションにあるのと同じ「分散クエリ」構文を使用して、Excel データを SQL Server にインポートできます。

次の Visual Basic 6.0 コード サンプルの場合、ActiveX データ オブジェクト (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>"

'Import by using 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

'Import by using OPENROWSET and object name.
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

'Import by using OPENROWSET and SELECT query.
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 プロバイダーの使用

前のセクションのコード サンプルでは、ADO と SQLOLEDB プロバイダーを使用して、Excel から SQL へのインポートの宛先に接続しています。 OLE DB Provider for Jet 4.0 を使用して Excel ソースに接続することもできます。

Jet データベース エンジンは、次の 3 つの、それぞれ異なる書式を持つ特別な構文を使用して、SQL ステートメントの中で外部データベースを参照できます。

  • [Full path to Microsoft Access database].[Table Name]
  • [ISAM Name;ISAM Connection String].[Table Name]
  • [ODBC;ODBC Connection String].[Table Name]
このセクションでは、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\xltestt.xls;" & _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
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
Jet プロバイダーがサポートしている上記の構文を使用して、他の Microsoft Access データベースや、索引順次アクセス方式 (ISAM) ("デスクトップ") データベースや、ODBC デーベースに Excel データをインポートすることもできます。

トラブルシューティング

  • ドル記号 ($) が付いた Excel オブジェクト名 (たとえば Sheet1$) はワークシートを表し、ドル記号が付いていないオブジェクト名は Excel の名前付き範囲を表すことに注意してください。
  • SELECT クエリではなくテーブル名を使用して Excel のソース データを指定した場合など、状況によってはインポート先の SQL Server テーブルの列が再度アルファベット順に並び替えられます。Jet プロバイダーに関するこの問題の関連情報を参照するには、以下のマイクロソフト サポート技術情報番号をクリックしてください。

    299484 PRB: Columns Are Sorted Alphabetically When You Use ADOX to Retrieve Columns of Access Table
  • Jet プロバイダは、Excel の列にテキストと数値のデータが混在していると判断すると、"個数が多い方" のデータ型を選択して、選択された型に一致しない値を NULL として返します。この問題を回避する方法の関連情報を参照するには、以下のマイクロソフト サポート技術情報番号をクリックしてください。

    194124 [PRB] DAO の OpenRecordset を使用すると Excel の値として NULL が返される

関連情報


データ ソースとしての Excel の使用方法について詳細を参照するには、以下のマイクロソフト サポート技術情報番号をクリックしてください。

257819 Office Space: ADO を使用して Excel ワークシートにクエリを実行する
Excel へのデータの転送方法について詳細を参照するには、以下のマイクロソフト サポート技術情報番号をクリックしてください。

295646 ADO を使用して ADO データ ソースから Excel にデータを転送する方法
247412 [XL2003] [INFO] Visual Basic から Excel にデータを転送する方法
246335 オートメーションを使用して ADO レコードセットのデータを Excel に転送する方法
319951 [HOWTO] SQL Server データ変換サービスを使用して Excel にデータを転送する方法
306125 Microsoft SQL Server から Microsoft Excel にデータをインポートする方法