Q&A
csvファイルであれば大抵のDBは直接取り込む機能が存在するはずですがVBAを使う理由はなんでしょうか？そこを書かないとcsvを直接SQLSERVERに取り込んでください以外の回答は出にくいです。
データ件数が10万件程度のcsvファイルをVBAを使用してSQLSERVERに取り込みたいのですが、処理速度を考慮した上で最適な方法をご教授願えないでしょうか。
多分Excelだとは思いますが
VBAとひとくちに言ってもExcel・Access・Wordと種類がありますのでどのMS Officeアプリケーションか書いた方がいいと思います。
回答3件
0
ベストアンサー
データベースアクセスするには、VBAからMicrosoft ActiveX Data Objects (ADO)を使って行うことなります。Visual Studioを使ってVBやC＃で実装してもこのADOを使う事になるので、データベースへの登録自体は、Visual Studioを使って開発したソフトとパフォーマンス自体は変わりがありません。
パフォーマンス低下の原因は、CSVファイルを取り込んだシート上のセルの値を順々に取り込んでInsert命令を作る部分になります。特に、セルの値を順々に取り込むコード（例えば、Cells(row,col).valueを使って値を取得する部分）が遅いようです。
しかし、シート内の範囲を動的配列に一度にコピーして、この動的配列を使って値を取得する方法を使うと非常速く動作させることが可能でした。この方法で、私は、十数万件のデータ登録を数分で行うことができました。
コーディング例は次のようになります。
Dim v As Variant
' 1行1列目から60000行2列までの範囲を動的配列に一括して代入します。
v = sheet.Range("A1:B:60000")
あとは、v(r,c)を使って値を参照します。
これは、sheet.cells(r,c)と同じ事になります。このように処理すると非常に高速に値を参照することができます。
逆に、動的配列のデータを一括してシートに貼り付ける事も可能です。20万行くらいの範囲をコピーする場合、sheet.Cells(r,c).valueでアクセスしてコピーしていた場合は、数時間かかっていましたが、動的配列を一括コピーすると数十秒で処理することができました。
一点、注意すべき点は、
例えば、CSV内のデータが”00001"がシート上では、1となってしまう事があります。特に、"0E1"の場合には、浮動小数点と判断され、1"0.0000"と解釈されることがあります。
VBAからデータを登録する際に最も大切な点がこれとなります。データベース登録時に、"00001"や"0E1"などの値が存在するかどうか事前にチェックが必要と思います。
もし、存在する場合には、シート書式を文字列型にしておくと、このような解釈をしないようにすることができるかもしれませんが、よくわかりません。事前に検証しておくと良いと思います。
投稿2017/08/19 12:27
総合スコア157
0
はじめに。期待してる回答じゃなくてすみません。
処理速度を考えるなら間にVBA使わずにSQLSERVERに直接csvを取り込ませるのが早いはずです。
利用しているのがExcelでcsv開いてVBAという話なら
10万件程度のデータだと、
処理速度以前にExcelが途中で停止する可能性が出てくるデータ量だったかと思います。
(2010年頃の話なので今時のコンピュータなら大丈夫かもわかりませんが)
正直な話、十分な情報があってもこれに答えられる人がteratail見て答えてくれるのはすごく幸運な事かと。
お仕事されててどうしてもその処理が必要ならVBAの専門家探して金払ってでも聞く事かなぁと。
csvを直接SQLSERVERに取り込ませるのもダメ、専門家に相談するのもダメなら
「すみません。無理です。」って答えてもいい内容なのかなぁと。
投稿2017/08/18 21:52
総合スコア157
0
こんにちは。
処理速度を重視されるなら、BCPコマンドでのインポートをVBAで制御するのが一番高速だと思います。
BCPのフォーマットで対応できないような編集が必要なら、一旦ワークテーブルにCSVをインポートし、
そのワークテーブルを元にINSERTするSQLを発行する。
まあ、処理制御だけのVBAになってしまうと、VBA必要？ってなっちゃいますけどね。
投稿2017/08/19 01:03
編集2017/08/19 07:09
総合スコア25184
あなたの回答
tips
プレビュー
