●背景
CSVファイル(9 MB/184列)程度の読出処理を行う「手法」を実行速度基準に選択したく、ダミーデータを読出/消去/読出・・・(N10)繰返すプログラムをネットで調べた物をベースに作成し、各「手法」別に処理時間を確認した結果、結局はA)エクセルが最短となりました。
勉強にはなりましたが、拍子抜けで・・・・
●質問
1)CSVファイル(9 MB/184列)程度の読出であれば「エクセルで素直に」が一番速いとの認識は当たっていますか?
2)下記B)C)でまだ、速度向上の余地はあるのでしょうか?
3)ADO/DAO選択に関して後発ADOを選択しましたが、DAOを使用した場合、速度向上の余地はあるのでしょうか?
4)ADO+SQLに関しては勉強が追いつかず確認できていませんが、
5)FSOも結局は1行処理になるため試していませんが、速度向上の余地はあるのでしょうか?
A)エクセル呼出
Dim writeSheet As Worksheet
Set writeSheet = Worksheets("●呼出Data")
Dim readBook As Workbook
Set readBook = Workbooks.Open(対象保存先フォルダ & 呼出Sf内処理ログファイル名)
Dim readSheet As Worksheet
Set readSheet = readBook.Worksheets("Sheet1")
readSheet.UsedRange.Copy
writeSheet.Cells(1, 1). _
PasteSpecial Paste:=xlPasteValues, Ope・・・・・・・・・・・
Application.CutCopyMode = False
readBook.Close False
B)1行処理
intFileNo = FreeFile
Open 対象ファイル For Input As intFileNo
Do Until EOF(intFileNo)
Line Input #intFileNo, strLine
arrLine = Split(strLine, ",")
For cnt列 = 0 To UBound(arrLine)
Sheets("●呼出Data").Cells(cnt行, cnt列 + 1).Value = arrLine(cnt列)
DoEvents
Next cnt列
cnt行 = cnt行 + 1
Loop
C)ADO
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Set CN = New ADODB.Connection
CN.Provider = "Microsoft.Jet.OLEDB.4.0"
CN.Properties("Extended Properties") = "Text;HDR=NO"
CN.ConnectionString = "C:\"
CN.Open
Set RS = CN.Execute("SELECT * FROM ftr4q_try_b2SHORT_csv.CSV")
rowCnt = 0
colCnt = 0
Do Until RS.EOF
rowCnt = rowCnt + 1
colCnt = 0
For Each dbCol In RS.Fields
colCnt = colCnt + 1
Cells(rowCnt, colCnt).Value = dbCol.Value
Next dbCol
DoEvents
RS.MoveNext
Loop
No.5ベストアンサー
- 回答日時:
re:補足コメント
元々の(C)のコードでADOで取れてるわけですからAccess購入するまでもないとは思います
>1行目の不可解で迷走中
これはヘッダーがおかしくなるという意味でしょうか
>CN.Properties("Extended Properties") = "Text;HDR=NO"
HDR=NO にしてデータ型の問題で読み込みがおかしくなるようでしたら
ヘッダーのみLoopして別途読み込むようにしても良いかもしれませんね
Sub test_ADO()
Const wkPath As String = "C:\TEST"
Const wkCSV As String = "TEST.csv"
Dim wkCon As ADODB.Connection
Dim wkRst As ADODB.Recordset
Dim wkSQL As String
Dim ws As Worksheet
Dim x() As String
Dim fc As Long
Dim i As Long
Set ws = Sheets.Add
On Error GoTo errHandr1
Set wkCon = New ADODB.Connection
With wkCon
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "TEXT"
.Open wkPath
End With
On Error GoTo errHandr2
wkSQL = "SELECT * FROM [" & wkCSV & "]"
Set wkRst = New ADODB.Recordset
With wkRst
.Open wkSQL, wkCon
On Error GoTo errHandr3
fc = .Fields.Count - 1
'フィールド名取得
ReDim x(0 To fc)
For i = 0 To fc
x(i) = .Fields(i).Name
Next
End With
ws.Range("A1").Resize(, fc + 1).Value = x
ws.Range("A2").CopyFromRecordset wkRst
errHandr3:
wkRst.Close
errHandr2:
wkCon.Close
errHandr1:
Set wkRst = Nothing
Set wkCon = Nothing
Set ws = Nothing
If Err.Number <> 0 Then Debug.Print Err.Number, Err.Description
End Sub
>現状ではやはり、エクセル直呼が一番評価です。
との事ですのであまりムリされなくても良いかと思いますが、一応サンプル2件
Sub sampleQT()
Const wkCSV As String = "C:\TEST\TEST.csv"
Dim ws As Worksheet
Set ws = Sheets.Add
With ws.QueryTables.Add(Connection:="TEXT;" & wkCSV, Destination:=ws.Range("A1"))
.AdjustColumnWidth = False
.TextFilePlatform = xlWindows
.TextFileCommaDelimiter = True
.Refresh False
.Parent.Names(.Name).Delete
.Delete
End With
Set ws = Nothing
End Sub
Sub sampleCB()
Const wkCSV As String = "C:\TEST\TEST.csv"
Const DLM As String = ","
Const REP As String = vbTab
Dim buf As String
Dim reg As Object
Dim n As Long
n = FreeFile
Open wkCSV For Input As #n
buf = StrConv(InputB(LOF(n), #n), vbUnicode)
Close #n
Set reg = CreateObject("VBScript.RegExp")
reg.Global = True
reg.Pattern = DLM
With GetObject("new:1C3B4210-F441-11CE-B9EA-00AA006B1A69")
.SetText reg.Replace(buf, REP)
.PutInClipboard
End With
With Sheets.Add
.Paste .Range("A1")
End With
Set reg = Nothing
End Sub
画面更新や再計算やイベントの停止解除は別途組み入れてくださいね
ありがとうございました。
もう少しムリしてみます。
ご教示内容はやってみます、結果はまた補足で・・・・。
(AccessはSQL文の勉強がてらです。)
No.7
- 回答日時:
ひとつだけ、隠し技ではないのですが、ほんのちょっとしたことですが、このようにすると、繰り返しのマクロでは楽になります。
これは、ADOでも同じです。こうすれば、二度目からのオーバーヘッドの負担は軽減されます。
後は、どんな使い方をするかにもよります。
なおADOは、Excel の2007以降ですと、昔のコードとは変わるそうです。
'--------------
Private objFS As FileSystemObject '←ここでオブジェクトを確保します。
Sub FSOImport()
Dim objFile As Object
Dim objText As Object
Dim myText As String
If objFS Is Nothing Then
'もしもオブジェクトを確保していなかったら、インスタンスを作る。
Set objFS = New FileSystemObject
End If
・
・
今後、また、新しい技術が増えるでしょうし、今までのものでも、クリアしていないものもあり、まだまだ、別に抱えている宿題が残っています。
No.6
- 回答日時:
これが今回、時間はかかりましたが、最後の宿題の答えになります。
結論めいたことは言わないで置きますが、最後まで通して、思わぬ結果になったと思っています。ADOは、アーリー・バインディングで最高のタイムは出していますが、FileSystemObjectも同様の条件にしたら、変わるかもしれません。ただ、通常、FileSsytemObject は、参照設定はしないことにしています。
>5)FSOも結局は1行処理
私は、前回も書いたとおり、スピード競争ではありませんから、そのテクニックだけを確立すればよいと思っています。以下、レイト・バインディングのFileSystemObject です。前回の郵便番号CSVを使い、結果としても6秒の好タイムで、終わることが出来ました。ストレスを感じるか感じないかは、ひとそれぞれですから、それ自体は、なんとも言えません。
コードは、行き当たりばったりで、お世辞にもうまいとは言えませんが、ReadAll で、最後の行数を取る所がミソです。若干、行数のズレが存在しているかもしれませんが、エラーを出さない限りはよしとしています。
>補足日時:2016/11/16 11:38
>「Open FileName For Input As #1・・・Output と組み合わせると」とB)は同じ?
同じかどうかは、細かいテクニックの違いがあります。
Excelの原則論としては、
『スプレッドシート上の出入りを極力避けることでスピードを速くすることができる。』ということです。
For i = 0 To UBound(buf)
x(j, i) = buf(i)
Next i
それは、以下の部分でも生きています。
私としては、Open FileName For ~は捨てがたいのは、プログラムが、構造的に単純だからです。
'//
Const myFile As String = "C:\Temp\Test1\KEN_ALL.CSV"
Sub FSOImport()
Dim objFS As Object
Dim objFile As Object
Dim objText As Object
Dim myText As String
Set objFS = CreateObject("Scripting.FilesystemObject")
Set objText = objFS.OpenTextFile(myFile)
Dim rw As Long, cl As Long, arbuf, ar, i As Long, j As Long
Dim flg As Boolean, ea, buf
myText = objText.ReadAll
rw = objText.Line
If rw < 2 Then
MsgBox "複数の行が存在しません。", vbExclamation
Exit Sub
End If
arbuf = Split(myText, vbCrLf)
rw = UBound(arbuf)
arbuf = Split(myText, vbLf) 'Unixタイプ
rw = UBound(arbuf)
If rw < 1 Then
MsgBox "改行コードが見つかりません。", vbExclamation
End If
i = 0
'ここでは、ヘッダーが存在しているとします。
buf = Split(arbuf(0), ",")
cl = UBound(buf) 'ヘッダーもしくは最初の行の列数を調べる
ReDim x(1 To rw, 1 To cl + 1)
For i = 1 To rw - 1
buf = Split(arbuf(i - 1), ",")
For j = 1 To UBound(buf) + 1
If j < (cl + 1) Then 'エラー防止
x(i, j) = buf(j - 1)
End If
Next j
Next i
Application.ScreenUpdating = False
Cells(1, 1).Resize(rw, cl).Value = x
Application.ScreenUpdating = True
End Sub
'//
ありがとうございました。
ご教示いただいた物を参考に、事前バインディングした物が最速になりました。
5秒/1回に対し4秒/1回に短縮、背景の背景で述べた多量/繰返もあり、もう少し速度にこだわってやってみます。
(以前FSOでファイルリスト作成時は事前がかなり速かったに比べ、今回は事前事後は僅差で事前、通信のバラツキ?)
後はAODの迷走をクリア(独特のテクニックが要りそう)して最速評価+扱い易さ評価で手法を決めたいと思います。
No.4
- 回答日時:
こんにちは。
私も勉強しながらの回答ですから、解答の切れ味が悪くてすみませんです。
C)を試してみました。
なるほど、速いですね。長いあいだやったことがなかったもので、いろいろと変わっている部分もありました。
郵便番号辞書 CSV 2016年版で試してみました。
123799行, 15列
計測タイム
CSV_DirectConnect: 5103 /1000 秒 ->5.1秒
残るは、「FSO」になりますね。「結局は1行処理になるため」ではないと思います。
以下、参照設定は、Microsoft ActiveX Data Objects 6.1 Library
Windows10 32bit, Excel 2013
今回は、計測の方法を公開しておきます。(これは、テンプレートに入れています。)
'//
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Sub CSV_DirectConnect()
Dim myTime(1) As Long
myTime(0) = timeGetTime() 'Start
Dim cnADO As ADODB.Connection
Dim mySource As String
Dim myPath As String
Dim strQuery As String
Set cnADO = New ADODB.Connection
'On Error GoTo ErrHandler
myPath = "C:\Temp\Test1\"
mySource = "KEN_ALL.CSV"
With cnADO
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=NO"
.Open myPath
End With
strQuery = "SELECT * FROM " & mySource & ";"
Set rsADO = New ADODB.Recordset
Set rsADO = cnADO.Execute(strQuery)
'空のシート
ThisWorkbook.Activate
ActiveSheet.Range("A1").CurrentRegion.ClearContents
ActiveSheet.Range("A1").CopyFromRecordset rsADO
cnADO.Close
Set rsADO = Nothing
Set cnADO = Nothing
ErrHandler:
If Err() <> 0 Then
MsgBox Err.Number & ": " & Err.Description
End If
myTime(1) = timeGetTime() 'Finish
Debug.Print "CSV_DirectConnect:"; myTime(1) - myTime(0)
End Sub
'//
No.3
- 回答日時:
まず、QueryTableだけは苦手ですね。
それは、お仕着せで、コントロールがほとんど効かないし、後々、変な痕跡を残します。「,」 + 「/」とかの切り分けができるという人もいますが、それすら、一般のコードでも出来ますからね。それはともかくとして、
B)
#2さんのご指摘にもありましたが、わたし流ですと、最初に、大きな配列変数を作っておいて、そこに入れてしまいます。
x(10,10000) とか。郵便番号辞書のCSV 123,733行 16列 12.4M 14秒ぐらい。
大きさが分からない時は、FileSystemObjectで、調べれば取れます。
Dim Fname As String
Dim FNo As Integer
Dim TextLine As String
Dim i As Long, j As Long
Dim x As Variant
Fname = "C:\Temp\Yubin.csv"
FNo = FreeFile()
ReDim x(130000, 16)
Open Fname For Input As #FNo
Do While Not EOF(FNo)
Line Input #FNo, TextLine
buf = Split(TextLine, ",")
For i = 0 To UBound(buf)
x(j, i) = buf(i)
Next i
j = j + 1
Loop
Close #FNo
Range("A1").Resize(j , 16).Value = x 'ここらへんは適当
'セルに貼り付ける大きさは、配列に従ってはいません。
これで遅いと感じたことはありません。得意不得意はあるかもしれませんが、ハング状態になっているわけでもありません。
No.2
- 回答日時:
最近verのVBAはあまり弄ってないのですが
>1)
ちょい前までは「エクセルで素直に」開くのが一番遅い、って認識を持ってました
(CSVファイルのデータ量やデータ形式にもよりますが)
(B)や(C)、つまりはOpenステートメントやADOを使った手法の方が速い
>2)
(A)はPasteSpecialメソッドを使ってるからシートへの書き込みは1回です
(B)(C)は1セルずつ書き込んでますからそれはそれは遅くなります
(B)はデータ読み込み用に配列を準備して、Loopしながら一旦配列にデータをセット
その配列からあとでまとめてシートに書き込めば1回です
(C)せっかくRecordsetオブジェクトを使っているのですから、
CopyFromRecordsetメソッドを使えば書き込みは1回です
#あとまあ、一昔前は、QueryTableを使うのもコード簡単でかつ速いですよ、なんて回答してましたが
#今はどうなんでしょうね
ありがとうございました。
「読込用配列」で速度アップしました。
補足追記しました。
ADOは本家Access入手後再トライしてみます。
No.1
- 回答日時:
こんにちは。
そのご相談というのは、特に、他人が口を挟む話ではないとは思いますが、自分の得意なもので処理すればよいのであって、処理時間について際立って、大きな違いが生じることはないはずです。
また、処理時間の優劣というのは、外部のオブジェクトを使う限りは、オーバーヘッド(間接的な時間のロス)を考慮しないというわけには行かず、内部コマンドのほうが、使っていて有利になるのは自明だと思います。
「奇を衒(てら)って」とは言いませんが、自分が得意ならともかく、マニュアル片手に、ADOを使うぐらいなら、Open FileName For Input As #1 を使えばよいと思います。
>速度向上の余地はあるのでしょうか?
この前の宮﨑駿さんのアニメの中で、主人公の豚さんが「レースじゃねえんだからな」という言葉がありましたが、数秒の差で優劣を付けるようなものでもないと思います。私のマクロデビューでは、退社時間にマクロを掛けて、朝出来上がっているが、それを縮められないか、という相談で、20分で出来たということで、大変に喜んでもらった覚えがあります。まだ、Lotus123もExcelも、市場には存在しない時代でしたが。
最近、CSVそのものが、Excelから発行されたものでないもので、文字コードはUnicodeだったり、UTF-8だったりして、CSVのインポートが失敗するという相談も見かけます。
私は、それをあえて手動で変換する方法をお勧めしてまいますが、プログラムで変更をする場合もあります。そういう時に、後で連結する場合は、自分で分かりやすい方のプログラムを使ったほうがよいのは言うまでもないことです。
Open FileName For Input As #1
は、データそのものの加工に優れています。Output と組み合わせると、Excelの出力することもなく、メモリー上で処理することも可能です。
DAO は一時代前のものですが、なかなか使い勝手は良かったと思います。いつの間にか、ADOに変わりました。今はもう、手をつける必要はないと思います。
>FSOも結局は・・・速度向上の余地
方法がないわけではないものの、Excel等がない状態で使うなら別ですが、選択の余地はあっても、ふつうは使わないはずです。
これらは、あくまでも私の個人的な印象で、それぞれの状況を正確に把握して、全体的な視点に立ってい言っているものではありません。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) vbaのエラー対応(実行時エラー7:メモリが不足しています) 4 2023/04/24 00:20
- Visual Basic(VBA) 配列の勉強をしています。使用する変数の意味、検索条件の書き方が難しいです。 2 2022/09/15 14:06
- Visual Basic(VBA) VBAが止まります。 2 2022/09/02 14:02
- Visual Basic(VBA) 複数のcsvファイルをExcelに一括変換したい 2 2023/03/03 12:44
- Visual Basic(VBA) access count数を変数に格納 2 2022/03/30 19:21
- Excel(エクセル) Excelにて、フォルダ内のTextファイルをマクロで統合すると文字化けしてしまう時の解消コード 4 2023/01/01 07:32
- Visual Basic(VBA) Excelで下記のようにマクロを作ったところ、一回目は実行できたのですが、二回目以降「実行時エラー1 1 2022/03/25 08:08
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
- Visual Basic(VBA) 別シートから年齢別の件数をカウントしたいの続き 5 2023/01/24 00:16
- Visual Basic(VBA) VBA 別ブックからの転記の高速化について VBA 別ブックからの転記の高速化についてご教授下さい。 19 2022/07/26 13:07
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel(VBA)でSetTimer関数を使...
-
【C言語 数独】 C言語で9×9の数...
-
C言語 時刻差分の算出方法
-
再帰呼び出しを使いますか?
-
Excelでのセル内容の高速消去方法
-
VBでの簡易電卓の作成(減算方...
-
C言語 再帰処理のメリットとデ...
-
DoEvents関数って何?
-
C言語:関数を使うメリットとデ...
-
小数点を含む数値かどうか判断...
-
SQLの速度をあげるには・・・
-
プログラム上のCPU稼働率低減に...
-
符号付きにすべきか、符号なし...
-
Excel VBA での処理時間計測結...
-
Excel VBAにて、2GB超の点群デ...
-
Cのプログラムに無性にイライラ...
-
VBS でプログラムを先頭から再試行
-
LINUX QT上でパソコンのシャッ...
-
Macターミナルで実行中のプログ...
-
ショッピング
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelでのセル内容の高速消去方法
-
DoEvents関数って何?
-
SQLの速度をあげるには・・・
-
小数点を含む数値かどうか判断...
-
win10で、正確な待ち時間の作り方
-
Excel(VBA)でSetTimer関数を使...
-
絶対パスの取得について
-
WebBrowserの読み込み待ちの処...
-
Excel VBAにて、2GB超の点群デ...
-
VBでの簡易電卓の作成(減算方...
-
テキストファイルの空行をスキ...
-
ノットイコールを教えて下さい
-
ナップザック問題?をエクセル...
-
If Not c Is Nothing Then ~延...
-
プログラム上のCPU稼働率低減に...
-
逆ポーランド記法における単項...
-
符号付きにすべきか、符号なし...
-
C言語:関数を使うメリットとデ...
-
C言語 時刻差分の算出方法
-
Excel VBA データ削除の高速化
おすすめ情報
●背景の背景
新規ラインMES展開/利用し解析/対策立案/予測・・等々で生産技術PDCA回し中ですが、新工法で試行錯誤中(処理が固まっていれば外注化とも考えますが現状では無理)で、更に扱うデータが増えた事もあり、解析/予測効率(多量/繰返で1秒でも効きます)を少しでも向上させたい。
●手法の比較(データはファイルサーバーに保管されています。通信負荷変動考慮N10)
A)エクセル呼出(9M) 10回平均:5秒/1回
B)Open * For Input As intFileNo(3M):10回平均:19秒/1回
C)ADO(3M)10回平均:6秒/1回
※B)以降はA)と同じファイルではハングアップ(待ちきれず)サイズ1/3化で実行。
●WindFallerさんのご指摘の
「Open FileName For Input As #1・・・Output と組み合わせると」とB)は同じ?
●手法の比較(その後1)
A)エクセル呼出(9M) 10回平均:5秒/1回
B改1)end_uさんご教示「データ読込用配列使用」でcsv(8.4M=前述A).xlsxを.csv保存)10回平均:5秒/1回。A)同程度。end_uさんありがとうございました。
C改1)end_uさんご教示「CopyFromRecordsetメソッド」Set RS = CN.Execute("SELECT * FROM ・・")のファイル名を変数で指定する方法で迷走中、固定ファイル名にした場合はOKですが1行目の不可解で迷走中、システム部に相談しAcsess購入手配しました、入手後の再挑戦。
現状ではやはり、エクセル直呼が一番評価です。
●手法の比較(その後2)
A)エクセル呼出(9M) 10回平均:5秒/1回
B改1)end_uさんご教示「データ読込用配列使用」(8.4M=前述A).xlsxを.csv保存)
(3M/10回平均:19秒/1回)⇒ 10回平均:5秒/1回。
C改1)end_uさんご教示「迷走回避策」準備中
D)WindFallerさんご教示「FSO+ReadAll」の事前バインディング版
(FSOはファイル/プロパティーリストで使用したことが有りましたが「ReadAll」は当初手法候補に挙げることが出来ていませんでした。)
同上8.4M/10回平均:4秒/1回。
■別の仕事を突っ込まれ、質問期間内に解決しきれるか・・?
再度の質問になるかもしれませんが、みなさん宜しくお願いします。
(所感)素のエクセルであればネックはサーバー通信(パケット含め)で手法側の差は意外と少ない?
●手法の比較(その後3)
C改2)end_uさんご教示 同上8.4M/10回平均:3秒/1回 ★ベストです。
(注)時間無くパス/ファイル名固定 Const wkPath As String = "****"、Const wkCSV As String = "***.CSV"で実行しています。
パス/ファイル名の変数指定「迷走」はネット記述を各種トライしていますが未だにクリアできず。
Const wkPath As String = 対象フォルダ、Const wkCSV As String = 対象ファイル の様な指定方法どなたかご教示いただければ助かります。
C改2)の件【CStr】で解決しました。
各位、ありがとうございました。