[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VBAでCSVを開く際の速度』（ポム）
お世話になります。 社内のシステムからCSVファイルを抽出し、エクセルVBAで そのCSVを開き、処理を行っています。
このCSVファイルはおよそ100列×30000行のデータなのですが、ダブルクリックで 手動でファイルを開くのに比べ、Openメソッドで開くと非常に重いです。
下記のコードで処理を行っているのですが、何か別の手法があればご教授頂けませんか？
宜しくお願い致します。
Sub マスタシート更新()
Dim pt As String, tgt_file As String
pt = Left(ThisWorkbook.Path, 1) & (":\AAA\BBB\CSV格納\")
tgt_file = Dir(pt & "*.CSV")
If tgt_file = "" Then
MsgBox "CSVデータが格納されていません。"
Exit Sub
ElseIf vbNo = MsgBox("マスタシートの内容を更新します。",vbYesNo) Then
Exit Sub
End If
Application.ScreenUpdating = False
Workbooks.Open pt & tgt_file
'〜〜〜〜処理〜〜〜〜
Application.ScreenUpdating = True
End Sub
>手動でファイルを開くのに比べ、Openメソッドで開くと非常に重いです。 Openメソッドだと遅いですね。 とりあえず、csvファイルの拡張子をtxtにしてみるとか。 それでも遅く感じるのなら、全て文字列として読み込んでみるとか。 もしくは、 データ→外部データの取り込みで取り込んでみるとか。 BJ
BJさん、ありがとうございます。
拡張子をtxtに変え、Openメソッドで開くと変更前と比べかなり速くなりました。 しかし、Openするとカンマ区切りになっていたため、TextToColumnsメソッドで各列に分解しようと 試みました。この後、各不要列を削除する処理を行っているためです。
下記コードなのですが、これがまた非常に重くなってしまいました…。
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array( _
72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), _
Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array( _
85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(89, 1), Array(90, 1), Array(91, 1), _
Array(92, 1), Array(93, 1), Array(94, 1), Array(95, 1), Array(96, 1), Array(97, 1), Array( _
98, 1), Array(99, 1), Array(100, 1), Array(101, 1), Array(102, 1), Array(103, 1), Array(104 _
, 1), Array(105, 1), Array(106, 1), Array(107, 1), Array(108, 1), Array(109, 1), Array(110, _
1), Array(111, 1), Array(112, 1), Array(113, 1), Array(114, 1), Array(115, 1)), _
TrailingMinusNumbers:=True
カンマ区切りで開かれてしまっているファイルから、必要な列のデータのみ取り出したい場合は TextToColumnsメソッドで一度分解する以外の手法などあるのでしょうか？
また、 >全て文字列として読み込んでみるとか。 こちらについては具体的な方法が見当も付かない状態です…。ヒントを頂けませんか？
>データ→外部データの取り込みで取り込んでみるとか。 こちらは、これから試させて頂きます！
(ポム)
> カンマ区切りで開かれてしまっているファイルから、必要な列のデータのみ取り出したい場合は > TextToColumnsメソッドで一度分解する以外の手法などあるのでしょうか？
こんな感じでどうでしょう？ テキストファイルから直接読み込みます。
Sub test()
Dim fn As String, delim As String, temp As String, myColumns
Dim x, y, a() As String, i As Long, ii As Long
myColumns = VBA.Array(1,3) '<- 取り出したい列
fn = "c:\test.csv" '<- ファイルパス
delim = "," '<- 区切り文字
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To UBound(myColumns) + 1)
For i = 0 To UBound(x)
y = Split(x(i), delim)
For ii = 0 To UBound(myColumns)
If myColumns(ii) - 1 <= UBound(y) Then
a(i + 1, ii + 1) = y(myColumns(ii) - 1)
End If
Next
Next
ThisWorkbook.Sheets(1).Cells(1).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub
(seiya)
seiyaさん、素晴らしいコードをありがとうございます！ 実際に下記のように試してみたところ、飛躍的に速度が上昇し感激しました。
Sub test() Dim fn As String, delim As String, temp As String, myColumns Dim x, y, a() As String, i As Long, ii As Long, pt As String, op_file As String
pt = ThisWorkbook.Path & "\システムデータ格納\" op_file = Dir(pt & "*.CSV")
myColumns = Array(4, 5, 6, 18, 19, 20, 21, 28, 34, 55)
fn = pt & op_file
delim = ","
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To UBound(myColumns) + 1)
For i = 0 To UBound(x)
y = Split(x(i), delim)
For ii = 0 To UBound(myColumns)
If myColumns(ii) - 1 <= UBound(y) Then
a(i + 1, ii + 1) = y(myColumns(ii) - 1)
End If
Next
Next
ThisWorkbook.Sheets(3).Cells(1).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub
もしや、BJさんの言っておられた「全て文字列として読み込んでみる」というのも、
CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAllの部分にあたるのでしょうか？
配列に関しては一次元をほんの少ししか扱った事がないので解読に四苦八苦しておりますが、
本当に勉強になります。
>データ→外部データの取り込みで取り込んでみるとか。 ただいま自動記録でデータ→外部データの取り込みより記録されたコードを実行しましたが、 こちらも早いですね！どちらも大切に保存させて頂きます…m(__)m
(ポム)
ボムさん
一度テキストファイルの全文を変数 (temp) に格納してから処理しています。 そこから、Split(temp, vbCrLf) で各行の文字列を配列変数 x の各要素として 取り込んでいます。 そこから、x の各要素を区切り文字で分割した配列変数 y の必要列の要素を、 最終二次元配列 a() に取り込み、一気に吐き出しています。
上記コードの使用注意点は、 もしCSVで "," が桁区切りで使用されている数字、または単に文字列中に カンマが使用されていて、" " の中にある場合は、もう一工夫必要です。 (seiya)
>もしや、BJさんの言っておられた「全て文字列として読み込んでみる」というのも、 違います。 テキストを開く時に列のデーター形式を指定できるところがあります。 そこで、列ごとに文字として読み込むように指定できます。
それを、マクロ記録すると Array(1, 1), Array(2, 1), Array(3, 1),・・・・ ↓ Array(1, 2), Array(2, 2), Array(3, 2),・・・・ となります。
配列を使うとこうとも書けます。 BJ
Dim Fildinf(1 To 115)
For i = 1 To 115
Fildinf(i) = Array(i, 2)
Next
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True, FieldInfo _
:=Fildinf, TrailingMinusNumbers:=True
Erase Fildinf
seiyaさん
解説頂きありがとうございます。 なるほど…Split(temp, vbCrLf)のvbCrLfは何かと思っていましたが、改行コードを用いる事で 行単位で格納できるのですね…。
>そこから、x の各要素を区切り文字で分割した配列変数 y の必要列の要素を、 >最終二次元配列 a() に取り込み、一気に吐き出しています。
どこでどのような処理をしているのか、見えてきました。 ご丁寧に感謝です！
>上記コードの使用注意点
はい私も、カンマ区切りのファイルをいじるのかー、と思った時にそれを懸念しましたが、 今回の件ではファイル内のデータにカンマは存在しませんでしたので、こちらのコードで 問題なく稼動させられそうですm(__)m
BJさん
コード提示頂きありがとうございます！ 試させて頂いたところ、私が二番目に示した冗長なコードより、 TextToColumnsメソッドが遥かに高速で動きました。
この手法でしたら、冒頭で示したコード(拡張子はtxtに変更して)でも実用に耐えられそうです！
様々なアドバイス、本当にありがとうございました。
(ポム)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.