ストアドプロシージャよりEXCEL読み込みとDB登録

Question

環境はWinServer2008R2 (オフィスは入っておりませんので、リモートデスクトップとかでログインしてもエクセルは開けない状態)です。

ストアドからエクセルを読み込みDBへ登録したいと考えておりますが、

テキストファイルをDBへ登録、エクセルからストアド呼出、等は良く見かけますが

表題のような事が見つからず投稿させて頂きました。

ご教示頂きたいのは、下記の通りで御座います。

①エクセルファイルの中身を登録するには、オフィスがサーバに入っていないとダメでしょうか？

　(登録したデータはテーブルみたいな感じで登録したく、SELECT文で後に取り出したい)

②具体的なエクセルの読み込み方法

③一括登録があったとして、それを実行した場合、DB登録前にデータ存在確認を行いたい

以上、何卒宜しくお願い申し上げます。

Answer 1

> ①エクセルファイルの中身を登録するには、オフィスがサーバに入っていないとダメでしょうか？

サーバーでオフィスオートメーションの機能を利用すると言うことですか？　それは逆に推奨もサポートもされていません。詳しくは以下のページを見てください。

Office のサーバーサイド オートメーションについて
http://support.microsoft.com/kb/257757/ja

> ②具体的なエクセルの読み込み方法
> ③一括登録があったとして、それを実行した場合、DB登録前にデータ存在確認を行いたい

ストアドを作って、それにより SQL Server から直接 Excel ファイルを読んで SQL Server の DB にデータを取り込みたい。さらに「データ存在確認」もストアドで行いたいということでしょうか？　ストアドでは無理そうな感じがします（自分が知らないだけと言う可能性は否定できませんが）。

SQL Server インポートおよびエクスポートウィザードや SQL Server Management Studio を使ってインポートできますが、これでは目的に合わないのでしょうか？

SQL Server インポートおよびエクスポート ウィザードを実行する
http://msdn.microsoft.com/ja-jp/library/ms140052.aspx

インポート／エクスポート ウィザード による単純なデータ転送
http://mtgsqlserver.blogspot.jp/2013/03/blog-post_10.html

または、Excel ファイルと SQL Server の DB の間を仲介するアプリケーションを作れば可能です。「データ存在確認」もアプリに作りこんでおけば可能と思います。

Visual Basic .NET と ADO.NET を使用して Excel ブックのレコードの取得と変更を行う方法
http://support.microsoft.com/kb/316934/ja

Answer 2

私は実際に試したことがありませんが、ストアドプロシージャでファイルの扱いができますから、以下のような方法があるようです。

Read and Import Excel Sheet into SQL Server Database in ASP.Net
http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx

③の事前にデータ存在を確認したい場合は、一旦、一時テーブルに取り込んでから、カーソルで一件ずつ読みながら重複をチェックすることができると思います。ただ、件数がたくさんあるとパフォーマンスが不安です。その場合は、PRIMARY制約やUNIQUE制約によって、そのデータを登録しようとした時にエラーになることを利用した方が良いでしょう。
ちなみにExcelデータ取り込み時はinsertのみですか？ 既に存在している場合はupdateするという仕様もあります。また、同時に取り込むような可能性が0%ではないのであれば、必要があればアイソレーションレベルやトランザクションを使い、しっかりとテーブルのロックを管理して下さい。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 3

こんにちは。

実際に実行したことはありませんが、（検討の段階で面倒なことになりそうなのでやめましたが・・・）

もしかすると、CLR ストアドプロシジャ、もしくは、拡張ストアドプロシジャを作成すればできるかもしれません。
（CLR ストアドプロシジャから、OOXMLを扱う名前空間を使用できたかな・・・）

ただし、SurferOnWwwさんもおっしゃっているように、サーバーでオフィスオートメーションの機能を利用するのはNGですから、Excelは2007以降のOOXML形式であることが前提となります。
また、この方法でできるとしても、セキュリティ、メモリリークなど細心の注意は必要になると思います。
おそらく、保守・運用面で何かと面倒になると思います。
OOXMLから大量にデータを読み込もうとする場合、いくつかのテクニックを駆使しなければ、遅いという事もあるかと思います。

ただ、各種のハードルを越えられれば、かなり有効だという気もしています。

CLR ストアド プロシージャ
http://technet.microsoft.com/ja-jp/library/ms131094.aspx

拡張ストアド プロシージャのプログラミング
http://msdn.microsoft.com/ja-jp/library/ms164734.aspx

Open XML SDK 2.0 for Microsoft Office へようこそ
http://msdn.microsoft.com/ja-jp/library/office/bb448854(v=office.14).aspx

DocumentFormat.OpenXml.Packaging Namespace
http://msdn.microsoft.com/ja-jp/library/office/documentformat.openxml.packaging(v=office.14).aspx

Office Open XML
http://ja.wikipedia.org/wiki/Office_Open_XML

Answer 4

Office のサーバーサイド オートメーションについて

『ストアドでエクセルを読込んで登録』にオフィスやその他の何かをサーバにインストールが必要であれば入れる予定でしたが、上記のリンクを拝見し、サーバ側にはエクセルは入れるな、という理解をしました。

そこで、改めて質問ですがサーバ側にエクセルファイルを配置した場合、サーバにオフィスが入っていないとエクセルは認識出来ないからストアドで読み込み⇒DBへ登録はそもそも無理だという認識で合っていますか？

つまり、エクセルの中身をDBに登録したい場合は、クライアント側からの読み込みで実施が必要だと・・・
また、前提と致しまして、自動化を考えておりますのでSSMSでの手動は考えておりません。

ちなみに下記のような記事を見つけたのですが、自分がやりたい事と違うのでしょうか？

※活動実績がない為、リンクが張れず【グーグル】にて『ストアドプロシージャ エクセル読み込み』で検索し、最初に出てくる楽天ブログまねして見た所、

『OLE DB プロバイダ'Micorsoft.Jet.OLEDB.4.0'を分散クエリに使用することはできません。このプロバイダーは、シングルスレッドアパートメント モードで実行するように構成されています。』とエラーが出て実行出来ませんでした。

以上、何卒宜しくお願い申し上げます。

Answer 5

>そこで、改めて質問ですがサーバ側にエクセルファイルを配置した場合、サーバにオフィスが入っていないとエクセルは認識出来ないからストアドで読み込み⇒DBへ登録はそもそも無理だという認識で合っていますか？

Excelというアプリケーションの機能を使用せずにExcelのファイル形式をしたファイルを読み取るのであれば、Excelというアプリケーションは必要ありません。例えば、独自にExcelファイルを読み込んで処理するアプリケーションを作成した場合、Excelというアプリケーションが必要ないのと同じです。

＞『OLE DB プロバイダ'Micorsoft.Jet.OLEDB.4.0'を分散クエリに使用することはできません。このプロバイダーは、シングルスレッドアパートメント モードで実行するように構成されています。』とエラーが出て実行出来ませんでした。

これだけではわかりませんが、64bitの対応の問題があるかもしれません。サーバーは64bitですか？　ところでどのようなコードを書かれていますか？　その楽天ブログはどこでしょうか？　以下でしょうか？

ストアドだけでマトリックスのExcelデータを読み込む
http://plaza.rakuten.co.jp/jamshid/diary/200812270000/

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 6

一応、SQL Server Integration Services(SSIS) はサーバーサイドで Excel ファイルを扱えるので、SSIS をストアドプロシージャから実行する、という手法もあります。
ただ、SSIS は権限周りが複雑な上、ソース管理(履歴)の難しさなど独特な世界ですので、個人的にはお勧めしません・・・。
要件上使用できる時は検討すべき対象ではありますので、念のための補足まで。

---

MCITP（Database Developer/Database Administrator）

Answer 7

ALTER                PROCEDURE [dbo].[EXCEL_IMPORT]
AS
    DECLARE @READ_CD int;            /* 読取値 */
    /*テーブルから読み込み*/
    --SELECT TOP 1 @READ_CD =REPLACE([VALUE],'S_CD=','') FROM TBL_EXCEL_IMPORT FROM WHERE VALUE LIKE 'S_CD=%'
    BULK INSERT  TBL_EXCEL_IMPORT FROM 'c:\csvtest\Text_Import.txt' WITH(rowterminator = '\n')
    PRINT @READ_CD/*デバッグ出力*/
    RETURN 0

上記が現在のテストコードであり、エラー内容は『一括読み込みデータ変換エラー』が表示されます

＞ストアドだけでマトリックスのExcelデータを読み込む

その通りです、そちらを参考に

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\マトリックス.xls;HDR=NO','SELECT * FROM [Sheet1$]')

を実施した所、『シングルスレッド・・・』なるエラーが表示されます。ちなみにサーバは32ビットです。

テストデータはエクセルに1列の数行で試しています。

まだ、皆様より頂いたアドバイスをもとにどのようにやるかを見出せておりません

最初に考えたのはエクセルを読込み⇒キーとなるデータの存在チェックをSQLを実施後、INSERT OR UPDATEという感じを考えておりましたが、どうやら難しいようですね。

Answer 8

64bit は Microsoft.ACE.OLEDB.12.0 にしないと駄目では？  

Excel も 12.0 で。

あ、32bit サーバーでしたか・・・

Answer 9

＞を実施した所、『シングルスレッド・・・』なるエラーが表示されます。ちなみにサーバは32ビットです。

この辺り、私はあまり詳しくないのですが、そのサーバーにJet 4.0のドライバーが入っていないのかもしれません。
以下を参考にして確認してみて下さい。サーバーが32ビットであれば、Jet 4.0のドライバーで大丈夫なはずです。

＃ただ、調べてみた限り、メッセージ的にはサーバーが64bit環境っぽいんですが・・・

（参考）
Windows7 32ビットオペレーティングシステムにて、「Microsoft.Jet.OLEDB.4.0 プロバイダはローカルのコンピュータに登録されていません」と表示されてしまう。
http://social.msdn.microsoft.com/Forums/ja-JP/37c586c5-4911-4529-869d-3705c08ab816/windows7-32microsoftjetoledb40-?forum=vbgeneralja

＞最初に考えたのはエクセルを読込み⇒キーとなるデータの存在チェックをSQLを実施後、INSERT OR UPDATEという感じを考えておりましたが、どうやら難しいようですね。

これについては既に述べた通り、可能だと思います。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 10

御免なさい、64ビットでした、確認したつもりが間違っておりました。

＞Windows7 32ビットオペレーティングシステムにて、「Microsoft.Jet.OLEDB.4.0 プロバイダはローカルのコンピュータに登録されていません」と表示されてしまう。
リンクを拝見致しましたが、system32には『msjet4.0.dll』はなく、systemWOW64にありました。

これは64ビット用はインストールされていない、と認識致しました。また、64ビットの場合、プログラムの記載は

OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0; ⇒ 'Microsoft.ACE.OLEDB.12.0','Excel 12.0へ変更したところ、『OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" が登録されていません。』と表示されました

これは64ビット用がインストールされていないと認識しましたが、間違えありませんか？

また、確かにアドバイス頂きました

＞Read and Import Excel Sheet into SQL Server Database in ASP.Net

はストアドの箇所が参考になると思われます、とりあえず上記のエラーを解消する事に努めてみます。

Answer 11

回答付いていますし今更とは思いますが、今回の環境として提示されている「Windows Server 2008 R2」に32bit版はありません。

Answer 12

間違えておりました、64ビットでした。

アドバイス通り変更してみましたが、『OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" が登録されていません。』

と表示されましたので、探してインストールしてみます。

Answer 13

『OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" が登録されていません。』を解決する為にネットで検索したところ、

マイクロソフトより

①Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント

②2007 Office system ドライバ: データ接続コンポーネント

上記で解決している人が何人かおりましたが、①はサーバー側アプリケーション内での Jet OLEDB プロバイダーとしての代替使用はできないと記載があり、②は用途として当てはまりますが、サポートされるオペレーティングシステムより、古くてWin7や今回のサーバは対象外となっております

グーグルやビーイングで検索してみましたが、これだ！というのが見つかりません、まだ探してみようと思っていますが見つからない場合、②をいれて試してみようかと考えています。

もし、他に解決方法をご存知の方がおりましたら是非ご教示頂きたく、何卒宜しくお願い申し上げます。

Answer 14

利用できないとは書いてないから、日本語でも問題ないと思いますが。
Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center

Answer 15

ACE プロバイダがサーバーサイドで使えるかという話は以前もこのフォーラムで話題になりました。

その話は、以下のページに "注: この資料では、Microsoft 2007 Office system ドライバーおよび 2010 Access データベース エンジンは、Microsoft Office コンポーネントと見なされます。"（即ち、オフィスオートメーションをサーバーサイドで使うのと同様に非推奨という意味に取れます）とあったことに始まります。

Office のサーバーサイド オートメーションについて
http://support.microsoft.com/kb/257757/ja

その時は、単なるライセンスの問題なのか、オフィスオートメーションをサーバーで使うのと同じ技術的な問題があるのかは、フォーラム参加者の誰もわかりませんでした。

以下の MSDN ライブラリを見ると、どう見ても「JET は止めて ACE を使いましょう。Web サーバーでも使えます」的なことが書いてあって、一体本当のところは何なんだろうと疑問に思っています。

Access 2010 を使用したデータ プログラミング
http://msdn.microsoft.com/ja-jp/library/ff965871(v=office.14).aspx

Answer 16

皆様のアドバイスにより、表題の事が実現できましたのでご報告致します。

具体的には、

①Microsoft Access データベース エンジン 2010 再頒布可能コンポーネントをサーバにインストール

②アドホッククエリ有効化

③OPENROWSET関数を使用

上記により、

SELECT SerialNo,Xaddress,Yaddress FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=c:\csvtest\Excel_Import.xlsx;HDR=YES',
'SELECT * FROM [Sheet1$]')

のクエリを発行する事が出来るようになりました。

皆様有難う御座いました。