エクセルVBAで65536レコードを超えるCSVファイルの読み込み

質問

エクセル２０００です。
現在CSV形式のファイルをもらい、エクセルVBAで1行づつエクセルに取り込み、加工しています。（CODEは、かなり省略していますが下記の通り）
データは将来的には何万件におよぶことも考えられます。
ためしに65536を超えるデータを読み込ませたところ65536を超えたところでやはりエラーになりました。Workbooks.Openでエクセル形式で開いているので65536を超える部分は無視されるからだと思います。
このような場合には、どうやってCSVファイルからデータを読み込めばよいのでしょうか？（なお、エクセルは当分2007にはなりそうもありません。アクセスはまったく使えません。）

Sub TEST01()
Set cf = Workbooks.Open(Filename:=ThisWorkbook.Path & "\test.csv")
Set zerro = cf.Sheets(1).Range("A1:AX1")
ThisWorkbook.Sheets("Sheet1").Activate
Do Until zerro.Cells(1).Value = ""
ThisWorkbook.Sheets("Sheet1").Range("A1:AX1").Value = zerro.Value
'処理マクロ省略
Set zerro = zerro.Offset(1)
Loop
End Sub

回答No.1

noname#62235 · ベストアンサー

一括で読み込もうとするからダメなのであって、１行ずつ読み込むようにすれば何行にでも対応できると思います。

こんなページを見つけました：
http://www.k1simplify.com/vba/tipsleaf/leaf291.html

上記ページのプログラムは全行読み込んでいますが、ループのところをちょっと加工すれば（行数をカウントして６５５３６を超えたら新しいシートを作ってそっちに書き込むなど）、長大なレコード数に対応できると思います。

ただ、処理の内容にもよりますがＥｘｃｅｌでは６５５３６行を超えるデータを１枚のワークシートに保持できませんから、たとえばピボットテーブルなどを作りたい場合はどうしようもありません。
合計を集計するだけとかなら問題ないですけど。

やはり最終的にはＡｃｃｅｓｓを導入すべきかと思います。

お礼

ありがとうございます。
やりたいことは、いわば差込印刷に毛の生えたようなものなので、１行ずつ読みこみ、その分の処理が終われば消去して、また次の行を読み込んでくればいいのです。
ご教示のページ、参考にしてみます。

補足

ご教示のページ、参考にしてなんとか出来ました。
有難うございました。

Sub CSV_Read3()

Dim FileNamePath As String
Dim textline, csvline() As String
Dim Rowcnt, ColumNum As Integer
Dim ch1 As Long

MsgBox "作業開始後、中断したい場合には、Ctrlキー+Breakキーを押してください。" _
, vbInformation, " (´^∇^)σ"

FileNamePath = ThisWorkbook.Path & "\test.csv"
ch1 = FreeFile '空いているファイル番号を取得

Open FileNamePath For Input As #ch1 'FileNamePath のファイルをオープン

On Error GoTo erLine 'エラーが発生したらファイルを閉じる
Application.EnableCancelKey = xlErrorHandler

Do While Not EOF(ch1) 'ファイルの終端かどうかを確認
Line Input #ch1, textline '1行読み込み
textline = Replace(textline, """", "") 'ダブルクォーテーションを削除
csvline() = Split(textline, ",") 'カンマで分離
Range(Cells(1, 1), _
Cells(1, UBound(csvline()) + 1)) = csvline() '配列渡しでセルに代入

'VBA処理省略

Application.StatusBar = Cells(1, 1)
Loop

erLine:

If Err.Number = 18 Then

MsgBox "中断キーが押されました。" & Chr(10) & _
"終了します。" _
& Chr(10) & "バイバイ。", vbCritical, "　( ; ゜Д゜)"
ElseIf Err.Number <> 0 Then
MsgBox Err.Number & ":" & Err.Description
End If

Close #ch1 'ファイルを閉じる
Application.StatusBar = ""
Application.EnableCancelKey = xlInterrupt

End Sub

回答No.10

noname#79209

こうなると、もうACCESSの世界では？

お礼

そうですよね。
ただわたしはまだアクセスは皆目分からないんです。
有難うございました。
お礼が遅くなり済みませんでした。

回答No.9

Wendy02

#2 です。

>だから65,536行よりも大きいCSVをまるごとインポートすることではありません。

サイトを紹介しただけで、そのものズバリではありません。

細かな事情は分かりませんが、それをヒントにして、インポートして、Excelに表示しなくても、そのままOutput を組み合わせて出力してしまえば、ワークシートに出す必要もありませんね。また、外部オートメーションの必要もありませんね。

merlionXX さんなら、インポートが出来れば、エクスポートもお分かりになるかと思います。後は、ご自身でお考えください。

お礼

いつもお世話になり、有難うございます。
お礼が遅くなり済みませんでした。

回答No.8

zap35

merlionXXさまのことですのでご存じとは思いますが、FileSystemObjectを利用する方法もありますね。
UNICODEなら　OpenAsTextStream(1, 0)　だったと思います

Sub TextImport()
　Dim FSO, buf As String
　Dim idx As Long
　Set FSO = CreateObject("Scripting.FileSystemObject")
　With FSO.GetFile("z:\OnDemandScanLog.txt").OpenAsTextStream(1, -2)
　　Do
　　　buf = .ReadLine
　　　Cells(1, 1).Value = buf
'　　　　差し込み印刷（？）処理　挿入
　　Loop Until .AtEndOfStream
　　.Close
　End With
　Set FSO = Nothing
End Sub

お礼

FileSystemObjectですか？
初めて聞きました。
有難うございました。
お礼が遅くなり済みませんでした。

回答No.7

noname#140971

補足:

中途で読み込みを強制終了したい場合は、

FileRead "", True

でOKです。

お礼

補足まで有難うございました。

回答No.6

imogasi

エクセルの根本的制約事項として、不可能とはっきりしていることを、いまさら聞いても無駄でしょう。
＞2007にはなりそうもありません
上司でも相談したの。新製品も出て、２万円前後の金で解決することではないの。ただ今のパソコンのスペックは問題だが。それに設計思想が１００万行になっただけで、処理反応時間が快適かどうかはべつですが。
情報処理に、コストがかかることは認識されているのでしょうかね。
ーーー
もうひとつ
（１）６万の続きを、同一シート別列に読み込むと、後の処理の重大な障害になるか。
（２）６万の続きを、別シートに読み込むと、重大な障害になるか。
そういうことが、言及されてないが。
（１）（２）をやる場合、VBAでCSVファイルをよむプログラムを探すか作らないといけないが、旧BASICのころから行われている簡単なコードなので、WEBを照会すれば、沢山転がっている。

お礼

有難うございました。
お礼が遅くなり済みませんでした。

回答No.5

noname#140971

<Book2.csv>

1,A
2,B
3,C
4,D
5,E
6,F
7,G

このような csv ファイルを１行づつ読み込んで、Sheet(1)、Sheet(2)、Sheet(3）等に２行づつ転記。

Private Sub CommandButton1_Click()
　　Dim I　　　　　　As Integer
　　Dim N　　　　　　As Integer
　　Dim R　　　　　　As Integer
　　Dim intCount　　 As Integer
　　Dim intSheet_New As Integer
　　Dim intSheet_Now As Integer
　　Dim Datas　　　　As String
　　Dim strValues()　As String

　　Do
　　　　Datas = FileRead("C:\temp\Book2.csv")　　' １行だけ読み込む
　　　　If Len(Datas) > 2 Then　　　　　　　　　　　　　' 終端の判断
　　　　　　intCount = intCount + 1　　　　　　　　　　' カウントアップ
　　　　　　intSheet_New = (intCount + 1) \ 2　　　　' シートインデックス確定
　　　　　　If intSheet_New <> intSheet_Now Then　' ニューシート移行時の行インデックス(R)の初期化
　　　　　　　　R = 0
　　　　　　　　intSheet_Now = intSheet_New
　　　　　　End If
　　　　　　strValues() = Split(Datas, ",")　　　　　　　' 各セルデータに分割
　　　　　　R = R + 1　　　　　　　　　　　　　　　　　　　' 行カウントアップ
　　　　　　N = UBound(strValues())　　　　　　　　　　' 列数取得
　　　　　　For I = 0 To N
　　　　　　　　Worksheets(intSheet_Now).Activate　　　　　' シートアクティブ
　　　　　　　　ActiveSheet.Cells(R, I + 1) = strValues(I)　　' データ転記
　　　　　　Next I
　　　　Else
　　　　　　Exit Do
　　　　End If
　　Loop Until (0)
End Sub

ポイントは、1行づつ順次に読み込む FileRead関数を利用していることです。

＜標準モジュール＞

Option Explicit

Public Function FileRead(ByVal FileName As String, Optional isStop As Boolean = False) As String
On Error GoTo Err_FileRead
　　Static isOpen As Boolean
　　Static fso　　As FileSystemObject
　　Static fil　　As File
　　Static txs　　As TextStream

　　If Not isOpen Then
　　　　isOpen = True
　　　　Set fso = New FileSystemObject
　　　　Set fil = fso.GetFile(FileName)
　　　　Set txs = fil.OpenAsTextStream(ForReading, TristateUseDefault)
　　End If
　　FileRead = txs.ReadLine
Exit_FileRead:
　　If Len(FileRead) = 0 Or isStop Then
　　　　isOpen = False
　　　　Set txs = Nothing
　　　　Set fil = Nothing
　　　　Set fso = Nothing
　　End If
　　Exit Function
Err_FileRead:
　　Resume Exit_FileRead
End Function

※Microsoft scripting runtime 参照設定が必要。

お礼

FileRead関数ですか、勉強になります。
有難うございました。
お礼が遅くなり済みませんでした。

回答No.4

WWolf

基本的な考え方は＃３さんと同意見です（OPENなど）
ただ、方法として次の方法も考えられます。
１．1行ずつ処理（加工）しているならその都度ｃｓｖへ格納する。
２．フォームを作りリストボックスなどにAddして選んだものをシートへ返し加工する。リストボックスなどでは65536の制限はありません。

現在の内容が明確ではないのでハッキリと言えませんが、将来を見込んだロジックであるなら考えてみてください。

お礼

有難うございました。
お礼が遅くなり済みませんでした。

回答No.3

hana-hana3

エクセルにお任せで読み込む方法は無いので、全てをＶＢＡで制御する必要があります。

ファイルの OPEN 文を使って指定ファイルを開き、
INPUT/LINEINPUT 文を使って１行ずつ読み込みます。
65536行を超える場合は、カウンターを使って設定した読込範囲をチェックしながら読み込む必要があります。

http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_110_020.html

お礼

ありがとうございました。
勉強になります。

回答No.2

Wendy02

こんにちは。

以下でお分かりになると思いますが、

65,536行よりも大きいテキストファイルは、Excel97,Excel2000, Excel2002, Excel2003
では、インポートできません。
http://support.microsoft.com/default.aspx?scid=kb;ja;120596

Open FileName For Input As #FileNum

で、65,536行を越えたら、Worksheets.Add して、再びインポートしていきます。

お礼

ありがとうございます。
やりたいことは、いわば差込印刷に毛の生えたようなものなので、１行ずつ読みこみ、その分の処理が終われば消去して、また次の行を読み込んでくればいいのです。
だから65,536行よりも大きいCSVをまるごとインポートすることではありません。