エクセルVBA 文字列領域が不足しています

http://oshiete.goo.ne.jp/qa/8769403.html
上記参照URLと同じ現象が発生しています。エラー発生場所もLine, 大容量CSVを開く際に発生します。

①改行コードがLFの場合に発生するとありますが、確認方法はどうすればいいのでしょうか？
②対策はあるのでしょうか？

No.4 ベストアンサー 回答者： tatsu99 回答日時： 2015/11/19 23:36

#3です。

前回処理時間がかかりすぎるので無理と回答しましたが、
約１０Kバイト単位で一気に読込、処理するように変えました。
１０００万行が約5分以内で処理できましたので、それでよければ試してみてください。
前提として、以下のサイトを参考にしています。
https://support.microsoft.com/ja-jp/kb/189981

標準モジュールとクラスモジュールがありますが、
クラスモジュール（Random）は、サイトの内容をそのままコピー＆ペーストして作成します。
ただし、読込のメソッドは、以下のメソッド(ReadBytes2)を追加し、それを使います。
-------------------------------
Public Function ReadBytes2(ByVal ByteCount As Long, ByRef ReadBuff() As Byte) As Long
Dim BytesRead As Long
If hFile = INVALID_HANDLE_VALUE Then
RaiseError W32F_FILE_ALREADY_CLOSED
End If
ReadFile hFile, ReadBuff(LBound(ReadBuff)), ByteCount, BytesRead, 0
ReadBytes2 = BytesRead
End Function

--------------------------------
標準モジュールは、以下のようになります。
--------------------------------------------
Option Explicit
Dim F As Random
Dim buff(10240) As Byte
Dim restLen As Long
Dim getLen As Long
Sub Macro2()
Dim readcount As Long
Dim st As Single
Dim et As Single
Dim lastline As Boolean
Dim line As String
Set F = New Random
F.OpenFile "c:\\goo\\File1000man.txt"
restLen = 0
st = Timer
readcount = 0

Do While True
line = MyReadLine(lastline)
If lastline = True Then Exit Do
readcount = readcount + 1
'Debug.Print line
Loop
F.CloseFile
Set F = Nothing
et = Timer
Debug.Print "時間（秒）=" + CStr(et - st)
Debug.Print "読込件数=" + CStr(readcount)
End Sub

Public Function MyReadLine(ByRef lastline As Boolean) As String
Dim rec As Byte
Dim recs() As Byte
Dim cnt As Long
Dim line As String
cnt = 0
line = ""
Do While True
rec = MyGetByte()
If rec = 0 Then
lastline = True
MyReadLine = ""
Exit Function
End If

' LF(0x0A)の場合、1行の区切りとし、ループ終了
If rec = &HA Then Exit Do
' CR(0x0D))以外は、バッファに保存
If rec <> &HD Then
ReDim Preserve recs(cnt)
recs(cnt) = rec
cnt = cnt + 1
End If
Loop
line = StrConv(recs, vbUnicode)

MyReadLine = line
End Function


Public Function MyGetByte() As Byte
If restLen = 0 Then
getLen = 0
restLen = F.ReadBytes2(10240, buff())
If restLen < 1 Then
'0x00のデータは無いことが前提
MyGetByte = 0
Exit Function
End If
End If
MyGetByte = buff(getLen)
getLen = getLen + 1
restLen = restLen - 1
End Function

---------------------------------------------
実行結果
時間（秒）=276.3906
読込件数=10000000


不明点があれば、補足してください。

この回答へのお礼

ありがとうございます。
クラスの使用は初めてです。Hello Worldだけ試しに作ってみて、実使用をどうしようか迷っていたところです。参考にさせて下さい。こちらの方法でも４～５分で終わるようですね。DOSプロンプトを使わないので終了監視せずに使えるのが便利そうです。

お礼日時：2015/11/28 09:00

No.5 回答者： WindFaller 回答日時： 2015/11/21 00:53

こんばんは。

#2の回答者です。

>ストリーム系のツールか、コマンド・プロンプトで考えなくてはなりません。

#2のこの自分の言葉に引っかかっていていたのですが、最初、sed (ストリームエディタ）やPerl が浮かびましたが、探していれるのも面倒ですから、nkf.exe のことを思い出しました。会社で、これを使えるのかどうかは別として、変換には、1,000万行でも、せいぜい十秒程度です。

nkf -Lw myLF.csv > myCrLf.csv
とすればよいです。これは、VBAでも、Shell で取り込めます。nkf が使えればですが。
command prompt というよりも、DOSでは、more を通せば、LF は、CRLF になるそうですが、あくまでも、s_jis のファイルに限るそうです。

http://www.atmarkit.co.jp/ait/articles/1301/25/n …
http://osksn2.hep.sci.osaka-u.ac.jp/~taku/osx/cr …

ちなみに、 "ADODB.Stream" で、やってみましたが、20分経っても終わらないです。それに、一度全部読んでいますから、これではダメでしょうね。

http://winofsql.jp/VA003334/infoboard_page.php?m …

この回答へのお礼

ありがとうございます。
moreを使ってみましたが、１時間で1%程度しか終わらず中断しました。
nkd, sedはどちらも無いため、使用可否を調べてます。正直言って10秒は魅力です。

お礼日時：2015/11/26 15:35

No.3 回答者： tatsu99 回答日時： 2015/11/19 17:56

#1です。

＞VBAとしての対処は無理でしょうか。①はともかく、②は作成中のVBAに組み込むことになります。サイズは5列×1000万行程度です。

VBAで行うことは、難しいでしょう。
要望としては、改行＝CR+LF　の場合と　改行＝LF　の場合のどちらでも、処理できるようにしたい。
ということかと思います。
処理の方法としては、1行単位での読み込みを既存の関数(Line）に任せるのではなく、自前でこれを出来るように
改造することになります。
つまり、バイナリーモードで読込、１バイトずつ処理し、LF検知時に、１行として扱うことになります。（CRは読み捨てる）
原理的には可能ですが、１０００万行の場合、時間がかかりすぎます。
以下のソースは、上記の実装例です。（MyReadLineで1行読込）
1行約６０バイトで１０万行のデータ読み込むと私の環境で約27秒かかりました。
１０００万行だと約４５分になります。
----------------------------------------------
Option Explicit

Sub MacroRead()
Dim st As Single
Dim et As Single

Dim n As Integer
Dim line As String
Dim lastline As Boolean
Dim readcount As Long

st = Timer
n = FreeFile
Open "C:\\goo\\file1000.txt" For Binary As #n
readcount = 0
Do While True
line = MyReadLine(n, lastline)
If lastline = True Then Exit Do
readcount = readcount + 1
'Debug.Print line
Loop
Close #n
et = Timer
Debug.Print "時間（秒）=" + CStr(et - st)
Debug.Print "読込件数=" + CStr(readcount)
End Sub


Public Function MyReadLine(ByVal n As Integer, ByRef lastline As Boolean) As String
Dim rec As Byte
Dim recs() As Byte
Dim cnt As Long
Dim line As String
cnt = 0
line = ""
Do While True
Get #n, , rec
'読み込み完了の場合はループ終了
If EOF(n) Then
lastline = True
Exit Do
End If
' LF(0x0A)の場合、1行の区切りとし、ループ終了
If rec = &HA Then Exit Do
' CR(0x0D))以外は、バッファに保存
If rec <> &HD Then
ReDim Preserve recs(cnt)
recs(cnt) = rec
cnt = cnt + 1
End If
Loop
line = StrConv(recs, vbUnicode)

MyReadLine = line
End Function
--------------------------------------------------------
実行結果
時間（秒）=27.48828
読込件数=100000

従って、（45分以上かかっても問題ないなら別ですが）他のツールでLFをCR+LFに変換し、そのデータを
現行のVBA処理で行うほうが、現実的かと思います。

No.2 回答者： WindFaller 回答日時： 2015/11/19 15:59

こんにちは。

今回、少し甘く考えていましたが、
http://www.moug.net/tech/exvba/0060089.html
LFコードで改行したファイルを読み込む

ここを読んで気が付きました。(別に開かなくて話だけでも良いです）

Sample2, Sample3 をやってみて、両方共ほとんど一括で読み込んでいます。Variant 型やString型は、限界が、2GB ですが、それを越えているのかもしれません。

http://d.hatena.ne.jp/tobiaki/20061107/p1
■[コンピュータ][VBA]「Line Input # ステートメント」の仕様

》ファイルが巨大だった場合、メモリに収まらずにエラーとなってしまうのです。*1
》こうなるとVBAだけで対応するのは難しく、事前にファイルの改行コードを変換しておくしかありません。
ここらは、純粋にVBAでは無理かもしれません。

VBAでというお望みは、常時、それが発生するからではないでしょうか。書き込みの中では、Unix/Linux 系のデータは、全部そういう方式ですから、本来は出力側から直してもらう、というのも一案かもしれません。

もしくは、ストリーム系のツールか、コマンド・プロンプトで考えなくてはなりません。

>5列×1000万行程度です。
ということは、まず、Excelでは、オーバーしてしまうようですが、処理だけにしますし、行の認識があれば、切り取りもできます(LFは行の認識がWin側ではできません）。

http://58391.diarynote.jp/200805101826280000/
ここを参考にして作ってみました。

'//
Sub ReplaceLfTest()
Dim objFS As Object
Dim oFile As Object
Dim tmpFile As Object
Dim buf As Variant
Dim fileName As String
Dim tmpF As String
　Const ForReading As Long = 1
　Const ForWriting As Long = 2
　fileName = xFILENAME
　tmpF = "$Tmp.csv"　'臨時ファイル名
　Set objFS = CreateObject("Scripting.FileSystemObject")
　Set oFile = objFS.OpenTextFile(fileName, ForReading)
　Set tmpFile = objFS.OpenTextFile(tmpF, ForWriting, True)
　Do While Not oFile.AtEndOfStream
　　buf = oFile.ReadLine
　　buf = Replace(buf, vbLf, "", , , vbBinaryCompare)
　　buf = buf & vbCrLf
　　tmpFile.Write buf
　Loop
　oFile.Close
　tmpFile.Close
　MsgBox "終了"
End Sub
'///

しかし、場合によっては、タイトルのように文字領域が不足するという結果になるかもしれません。
念の為に、添付画像は、今回のバイナリエディタで確認した結果です。

この回答へのお礼

後追いですが、動作確認しました。
このマクロとNo4がほぼ同等の速さで3～4分でした。No5が最速ですが、マクロ全体の所要時間が長いため今回はnkfは見送りました。

お礼日時：2015/12/02 22:21

No.1 回答者： tatsu99 回答日時： 2015/11/19 00:36

>①改行コードがLFの場合に発生するとありますが、確認方法はどうすればいいのでしょうか？

方法１：秀丸（テキストエディタ）で、そのファイルを開き、
ファイルー＞エンコードの種類
をクリックします。
改行＝CR+LFと表示されるか、
改行＝LFと表示されるかのどちらかかと思います。
改行＝LFと表示されれば、改行コードがLFです。
秀丸がない場合は下記からダウンロードしてください。（お試し版が使用可能）
http://hide.maruo.co.jp/software/hidemaru.html

方法2：バイナリエディタでCSVファイルを開き
改行位置に０D０A（16進数）があれば改行コードはCR+LF
改行位置に０A（16進数）があれば改行コードはLF
となる。
バイナリエディタの1例としてstirlingがあります。下記からダウンロード可能
http://www.vector.co.jp/download/file/win95/util …

>②対策はあるのでしょうか？
改行コードがLFのであれば、改行コードをCR+LFに変える。
変える方法としては、
方法1：秀丸で、ファイルー＞エンコードの種類をクリックして
改行コードが表示されますが、そこで改行コード＝CR+LF
を選択し、保存すると、改行コードがCR+LFに変換されます。
方法２：改行コード　変換　で検索を行ってください。
改行コードを変換するツールがいくつか表示されますので、
それを使って下さい。（以下は参考です）
http://www.vector.co.jp/soft/winnt/util/se406512 …

この回答へのお礼

ありがとうございます。
VBAとしての対処は無理でしょうか。①はともかく、②は作成中のVBAに組み込むことになります。サイズは5列×1000万行程度です。

お礼日時：2015/11/19 07:03