データベース

データベースに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Microsoft SQL Server)
SQLServerにExcel(csv,txt,etc・・・)をインポートしたい
投稿日時: 17/10/04 15:21:29
投稿者: yuki6982

いつもお世話になります。
 
標記の通り、SQLServer2008(R2)のテーブルに、
Excel2003(csvやtxtでも可)のデータをインポートするコードを書きたいのですが
上手く行かず困っています。
 
インターネット検索で、「分散クエリ」という方法があると書いてあったので
SQLServerのクエリに真似して書いてみたのですが、
「OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" が登録されていません。」
と表示されて上手く行きませんでした。
ちなみにサーバーにはOffice一式入っておらず、クライアント側にのみ入っています。
 
ちなみに↓を参考にしたのですが・・
https://docs.microsoft.com/ja-jp/sql/relational-databases/import-export/import-data-from-excel-to-sql
 
最終的には、Accessを使用して、ADPで、Excel→SQLServerの某テーブルにINSERTする
ストアドを実行するVBAを書こうとしているのですが
まずストアドから書けずに苦労してる状態です・・。
(VBAだけで出来れば良いのですが)
 
勉強しようにも、ドコをどう参照すればよいのかサッパリわからず・・・
とりあえずSQLServerのインポート機能を使って手動でインポートしたり、
テーブルに直にコピペしたりして凌いでいます・・・。
 
理解するまで先が長そうなのですが、ご教授頂けますでしょうか。
 
何卒宜しくお願いいたします。

回答
投稿日時: 17/10/04 18:01:56
投稿者: sk

引用:
ちなみに↓を参考にしたのですが・・
https://docs.microsoft.com/ja-jp/sql/relational-databases/import-export/import-data-from-excel-to-sql

引用:
インターネット検索で、「分散クエリ」という方法があると書いてあったので
SQLServerのクエリに真似して書いてみたのですが、
「OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" が登録されていません。」
と表示されて上手く行きませんでした。
ちなみにサーバーにはOffice一式入っておらず、クライアント側にのみ入っています。

・Access データベースエンジン( ACE )の再頒布コンポーネントを
 ダウンロードして、バックエンド側のサーバーにインストールする。
 
・(サーバーに ACE をインストールすることが叶わず、
  かつインポートする Excel ブックのファイル形式が
  xlsx ではなく xls である場合)
 ACE ではなく Jet をプロバイダとする。
 
引用:

https://docs.microsoft.com/ja-jp/sql/relational-databases/import-export/import-data-from-excel-to-sql

引用:
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=D:\Desktop\Data.xlsx', [Data$]);
GO

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0; Database=D:\Desktop\Data.xls', [Data$]);
GO
 
引用:
標記の通り、SQLServer2008(R2)のテーブルに、
Excel2003(csvやtxtでも可)のデータをインポートするコードを書きたいのですが
上手く行かず困っています。

引用:
最終的には、Accessを使用して、ADPで、Excel→SQLServerの某テーブルにINSERTする
ストアドを実行するVBAを書こうとしているのですが
まずストアドから書けずに苦労してる状態です・・。

CSV ファイルをインポートする場合は
BULK INSERT ステートメントを使用なさればよいのでは。
(前述の記事でも紹介されていますが)

投稿日時: 17/10/05 09:14:51
投稿者: yuki6982

sk様
 
早々の返信ありがとうございました!
 
「jet」及び「BULK INSERT」で試してみます!
 
実は以前「BULK INSERT」で試してみたのですが、
「パスが開けません」的な文が表示され、要するにフルパスが間違ってるような事を言われたのですが
クライアント側のcsv(txt)ファイルを指定したい場合、
「'C:\Users\〜'」では足りないのでしょうか?
コンピューター名を足したり、色々試してみたのですが上手く行かず・・・
もし上手く行けば、「BULK INSERT」でcsvでもインポート出来るように作成したいのですが・・
 
もう一度チャレンジしてみます!
jetの方も、試した後にご報告させて頂きます、
何卒宜しくお願いいたします。

回答
投稿日時: 17/10/05 10:13:53
投稿者: sk

引用:
実は以前「BULK INSERT」で試してみたのですが、
「パスが開けません」的な文が表示され、要するにフルパスが間違ってるような事を言われたのですが
クライアント側のcsv(txt)ファイルを指定したい場合、
「'C:\Users\〜'」では足りないのでしょうか?
コンピューター名を足したり、色々試してみたのですが上手く行かず・・・
もし上手く行けば、「BULK INSERT」でcsvでもインポート出来るように作成したいのですが・・

ストアドプロシージャを実行するのは SQL Server であるわけですから、
SQL Server が稼働しているサーバー側から見た絶対パス
指定する必要があります。
 
上記のパスの場合、クライアント側のローカルドライブ( C:\ )ではなく
サーバー側のローカルドライブ( C:\ )内のファイルを参照することになるわけで、
実際にそこにファイルがなければ当然エラーとなります。
 
・そのストアドプロシージャを実行する前に、SQL Server が稼働している
 サーバー(側の任意の共有フォルダ)に Excel ブックをコピー(アップロード)し、
 サーバー側から見た絶対パスを指定して、そのブックをインポートさせるようにする。
 
・そのブックへの UNC パスを直接指定してそのブックをインポートさせるようにする。
 (この場合、ブックが保存されているコンピュータ/フォルダへの
  アクセス権限がサーバー側のアカウントに与えられていなければならない)
 
確実なのは前者でしょう。

回答
投稿日時: 17/10/05 10:30:16
投稿者: sk

補足:

引用:
VBAだけで出来れば良いのですが)

「ADO を使用して、ブック内のレコードを 1 件ずつ任意のテーブルに書き込む」
という手段も一応考えられますが、場合によって実行時間が掛かり過ぎることも
あり得るため、あまりお奨めはしません。

投稿日時: 17/10/05 14:25:59
投稿者: yuki6982

sk様
 
引き続き書き込みありがとうございます。
 
VBAだけでは速度が遅くなるような気がしていましたが、やはりそうなんですね・・。
 
ちなみに「BULK INSERT」の方は、UNCパスを指定する方法でひとまず実行できました!
ありがとうございます!
可能であれば、Accessのインターフェイスで「ファイルを開く」ダイアログボックス等を使用して
指定したcsvをアップロードしたいのですが、
ネットワーク名の指定が必要なので、上手く作動しないようですね・・。
クライアントPCが変わった場合は使えないので、
後々はサーバーにファイルをコピーしてから読み込む方法に改良したいです!
ちなみに実装方法がサッパリ検討つかないのですが、ヒントを頂ければ幸いです・・・。
 
また、jetの方ですが、エラーが出てしまいました・・・
「OLE DB プロバイダー 'Microsoft.Jet.OLEDB.4.0' を分散クエリに使用することはできません。このプロバイダーは、シングル スレッド アパートメント モードで実行するように構成されています。」
教えていただいのに申し訳無いのですが全く理解出来なかったので
ACEの再頒布コンポーネントをダウンロードしてみようかと思います。
もしお解かりでしたら教えて頂きたいのですが、
マイクロソフトのダウンロードセンターから
「Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント」
を入れようと思っているのですが、
サーバー側にAccessも入っていないといけないのでしょうか?
 
勉強不足で申し訳ございません・・・
宜しくお願いいたします。

回答
投稿日時: 17/10/06 11:01:03
投稿者: sk

yuki6982 さんの引用:
可能であれば、Accessのインターフェイスで「ファイルを開く」ダイアログボックス等を使用して
指定したcsvをアップロードしたいのですが、
ネットワーク名の指定が必要なので、上手く作動しないようですね・・。
クライアントPCが変わった場合は使えないので、
後々はサーバーにファイルをコピーしてから読み込む方法に改良したいです!
ちなみに実装方法がサッパリ検討つかないのですが、ヒントを頂ければ幸いです・・・。

それはそのファイルをどこにコピー(アップロード)するかによります。
 
例えば、LAN 上において SQL Server が稼働しているサーバーに
既に何らかの共有フォルダが設けられているのであれば、
VBA の FileCopy ステートメントを用いて
(ファイルダイアログによって取得したパスの)
ファイルをその共有フォルダにコピーなさればよいでしょう。
 
yuki6982 さんの引用:
また、jetの方ですが、エラーが出てしまいました・・・
「OLE DB プロバイダー 'Microsoft.Jet.OLEDB.4.0' を分散クエリに
使用することはできません。このプロバイダーは、シングル スレッド
アパートメント モードで実行するように構成されています。」

直接の原因に関しては今のところ私にも判りかねますが、
恐らくサーバー側の環境の問題ではないかと思います。
 
そもそも、Jet も ACE もサーバーサイドで実行することを
前提として設計されたデータベースエンジンではなく、
Microsoft もサーバーサイドでの実行を推奨していません。
 
yuki6982 さんの引用:
ちなみに↓を参考にしたのですが・・
https://docs.microsoft.com/ja-jp/sql/relational-databases/import-export/import-data-from-excel-to-sql

引用:
Excel データ ソースに接続する ACE プロバイダー (旧称 Jet プロバイダー) は、
対話型のクライアント側での使用を対象としています。
特に自動化されたプロセスまたは並列で実行中のプロセスで、
サーバー上の ACE プロバイダーを使用すると、予期しない結果になることがあります。

BULK INSERT ステートメントによる CSV ファイルのインポートは
一応成功しているわけですから、とりあえずそちらの方向で進める
形でもよいと思います。
 
yuki6982 さんの引用:
教えていただいのに申し訳無いのですが全く理解出来なかったので
ACEの再頒布コンポーネントをダウンロードしてみようかと思います。
もしお解かりでしたら教えて頂きたいのですが、
マイクロソフトのダウンロードセンターから
「Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント」
を入れようと思っているのですが、
サーバー側にAccessも入っていないといけないのでしょうか?

本来 Excel は表計算ソフトであってデータベース管理ソフトではなく、
Excel ブックもデータベースファイルではありません。
当然、表形式になっていないワークシート/セル範囲を、
「データベースのテーブル」として扱うことは出来ません。
 
ただ、Jet / ACE というデータベースエンジンには、
Excel ISAM ドライバーを使用して、Excel ブックを
1 つのデータソースと見なし、その中に含まれている
(表形式になっている) Excel のワークシート/セル範囲を
「データベースのテーブル」として解釈して、
データの読み取り/書き込みを行なう機能があります。
 
SQL Server におけるリンクサーバーや分散クエリといった
外部データソースとの連携処理の際においては、
SQL Server と Excel ブックとの間を
Jet / ACE に仲立ちさせることによって
データのやりとりが実現されます。
 
(ADO によって Excel ブックからレコードセットを参照する場合も、
 Access データベース上のリンクテーブルを介して
 Excel ブック上のワークシート/セル範囲を参照する場合も、
 同じ原理でデータ接続が行なわれる)
 
Jet については MDAC / WDAC に同梱される形で
ほとんどの Windows にインストールされていますが、
その後継エンジンである ACE は初期状態では
インストールされていませんので、別途 Access / Office
( 2007 以降)をインストールするなり、前述の通り
ACE 単体をダウンロードしてインストールする
といった作業が必要となります。

投稿日時: 17/10/06 11:27:43
投稿者: yuki6982

sk様、非常にわかりやすい解説ありがとうございます!
 
結論として、JetやACEを使用せず
CSVとTXTのみインポートする仕様で作成しようと思います。
 
また、ファイルコピーの件も
共有フォルダがあるので
FileCopyを使用して実装出来ると思います!
 
ひとまず題名のインポートに関しては解決しておりますし、
またその後の改良についても
実装はまだですが、今のところ疑問点はありませんので
この件は解決済みとさせて頂きます。
 
sk様、本当にありがとうございました。
実は過去にも別件でお世話になったことがあります、
その際もありがとうございました。
疑問が出るたび点としての解決しか出来ず
中々線になって理解出来るに至りませんが
今回の件では丁寧に解説頂き、とても解り易く
また勉強になりました!
 
またまたお世話になることもあるかと思いますが
その際はどうぞ宜しくお願いいたします。
失礼致します m(__)m