大量のテキストデータの集計について

質問

毎度お世話になります。
今回、１００万件を越えるテキストデータのある数値項目の合計を
出したいのですが、ＥＸＣＥＬに貼り付けて集計しようにも
６万５千件しか読み込めないため、集計できずに困っています。
なにかいい方法あるいはツール等あればお教えいただけないでしょうか。
恥ずかしながらＥＸＣＥＬ以外のアプリは使ったことがありません。

回答No.8

imogasi · ベストアンサー

#7です。
結果を補足願います。結果の計数はアクチブシートA,B列に出ます。
下記に変えて実行。ファイル名は自分の物に修正のこと。
Sub test01()
Close #1
Dim tb(100), ks(100)
'--
k = 0
Open "C:\Documents and Settings\xxxx\My Documents\text13.txt" For Input As #1
While Not EOF(1)
Line Input #1, a
' MsgBox a
If k > 100000 Then GoTo end1 '10万レコードまで
k = k + 1
S = Split(a, " ")
For i = 1 To 100
If S(0) = tb(i) Then
ks(i) = ks(i) + Val(S(1))
GoTo p1
Else
End If
Next i
l = l + 1
tb(l) = S(0)
ks(l) = Val(S(1))
p1:
Wend
end1:
Close #1
'--
For i = 1 To l
Cells(i, "A") = tb(i)
Cells(i, "B") = ks(i)
Next i
MsgBox "処理終了"
End Sub
に変えてやってみてください。
１０万件にとりあえず制限してます。
●途中で止まらないか。
aaaについて、１００種類の配列オーバーやその他の原因などで。
●実行時間は何時間何分ぐらいかかかるか。
●上記２点うまくいくなら
If k > 100000 Then GoTo end1 '10万レコードまで、の行
を削除してみての実行で上記２点はどうか。

お礼

ありがとうございます。
処理終了のメッセージが出て、それぞれＡ列毎の合計がＢ列に出ました。そのあと、少ない件数でテストして、合計の数字が正しいことを確認しました。
実行時間は、１０万レコードの制限を付けてもはずしても一瞬でした。
長々とお付き合いいただきありがとうございました。
今回お教えいただいたコードをベースに、勉強に励みたいと思います。

回答No.7

imogasi

＃４、＃６です。
補足依頼
下記●の部分に答えてください
ーーー
第１レコードが
aaa 1
であるとして、１の部分はレコードごとに当然変わるとして、
●aaaの部分はレコードごとに変わるのですか。＜＝下記VBA実行して
みての結果
●予想でaaaの変化は何種類ぐらいあるのですか。
●ついでにC列以右も含めて、何列にデータが出てきましたか。
ーー
＃４のコードを下記に変えて実行してaaaの列の変化を見てください。
そして適当数やったら、CTRLキー+PausBreakキーの２つを同時押ししてください。中断します。
下記コードのファイル名は自分のものに置き換えることを忘れないように。
Sub test01()
Close #1
Open "C:\Documents and Settings\xxxx\My Documents\test01.txt" For Input As #1
While Not EOF(1)
Line Input #1, a
s = Split(a, " ")
Cells(1, "A") = a
MsgBox s(0) & " " & (1)
Wend
Close #1
End Sub
--
●aaa列はまとまっていて、変化するようですか。ばらばらに出てきますか。いわゆるソート後（並べ替えてある）らしいかどうか。
aaa 12
aaa 23
aaa 11
bbbb 34
bbbb 54
cc 25
cc 76
のようにまとまっているとやりやすい。

お礼

ありがとうございます。やってみました。
aaa部分はレコードごとに変わりました。
aaa部分は４，５種類です。件数の割には少ないです。
コードを実行したところ、
Ａ列に　ＡとＢ列の値が、Ｂ列には１と表示されました。C～右は何もないです。
メッセージボックスで、
aaa 1
bbb 1
ccc　1
といった具合に表示されました。
どうやらデータはソートされていません。

回答No.6

imogasi

#4です。
だいぶん、私の意図に近づいてきたようです。
（問１）A1の文字列・数字、またはA,B,C列の値の内容を補足してみてください。それを知りたい。どこ（どの列）に合計する数値があるか、それが判らないとプログラムが組めない。
例　aa 123 2345 cv など。これだと１レコードに４項目あることになる。
（問２）区切りは何文字（スペースかコンマか）でしたか。

お礼

お付き合いいただいてありがとうございます。
レコード構成は、
（Ａ１）aaa 1 で、Ｂ列の合計を出したいです。
（Ａ２）区切りは１文字でスペースです。Ｃ列以降は使用しません。
よろしくお願いいたします。

回答No.5

KenKen_SP

Access にインポートするのが簡単ですが、Access が無くても Excel VBA
でも計算できます。

標準モジュールに下記ソースコードを貼り付け、

　 Microsoft ActiveX 2.x Object Library

を参照設定します。

マクロ「CreateSampleData」を実行すると、C:\ に test.txt という
100万レコードのサンプルデータファイルを作成します。

次に、マクロ「Calc」を実行すると、金額合計や、レコード件数を
計算し、結果を表示します。

　# Excel2000 以降で動作すると思います

多少 SQL に関する知識が必要になりますが、高速（～数秒程度）で
計算できますよ。（ただし、PC スペックに依存します）

Option Explicit

' // テキストファイルのフォルダパス
Private Const DIR_PATH As String = "C:\"
' // テキストファイル名
Private Const FILENAME As String = "test.txt"
' // 一行目が「見出し」かどうか
Private Const HEADER　 As String = "True"

' // 100万レコードのサンプルデータ[1行目見出し]作成
Sub CreateSampleData()

　　Dim Sex(1)　 As String
　　Dim Addr(4)　As String
　　Dim vItem(4) As Variant
　　Dim vLine(1 To 1000) As String
　　Dim fn　　　 As String
　　Dim n　　　　As Integer
　　Dim i　　　　As Long
　　Dim j　　　　As Long
　　Dim k　　　　As Long

　　fn = DIR_PATH & "\" & FILENAME

　　' // 性別
　　Sex(0) = "男": Sex(1) = "女"
　　' // 住所
　　Addr(0) = "東京": Addr(1) = "名古屋"
　　Addr(2) = "京都": Addr(3) = "大阪"
　　Addr(4) = "福岡"

　　n = FreeFile()
　　Open fn For Output As #n
　　If CBool(HEADER) Then
　　　　Print #n, "ID,性別,住所,年齢,金額"
　　End If
　　k = 1
　　For j = 1 To 1000
　　　　For i = 1 To 1000
　　　　　　Randomize
　　　　　　vItem(0) = k　　　　　　　　　　　　　　　　 ' // ID連番
　　　　　　vItem(1) = Sex(Int(2 * Rnd))　　　　　　　　 ' // 性別ランダム
　　　　　　vItem(2) = Addr(Int(5 * Rnd))　　　　　　　　' // 住所ランダム
　　　　　　vItem(3) = Int((80 - 15 + 1) * Rnd + 15)　　 ' // 年齢15-80歳ランダム
　　　　　　vItem(4) = Int((200000 - 10 + 1) * Rnd + 10) ' // 金額10円-20万円ランダム
　　　　　　vLine(i) = Join$(vItem, ",")
　　　　　　k = k + 1
　　　　Next i
　　　　Print #n, Join$(vLine, vbCrLf)
　　　　Erase vLine
　　Next j
　　Close #n
　　MsgBox "サンプルファイル作成完了", vbInformation

End Sub

' // 集計
Sub Calc()

　　Dim cn　　　　　As ADODB.Connection
　　Dim rs　　　　　As ADODB.Recordset
　　Dim Sql　　　　 As String

　　' // テキストファイルに接続します
　　Set cn = New ADODB.Connection
　　cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
　　　　　　　　　　　　　"Data Source=" & DIR_PATH & ";" & _
　　　　　　　　　　　　　"Extended Properties=""text;" & _
　　　　　　　　　　　　　"HDR=" & HEADER & ";FMT=Delimited;"";"
　　cn.Open

　　' // サンプル1： 住所が「東京」の金額を集計
　　Sql = ""
　　Sql = Sql & "SELECT SUM([金額]) AS [金額集計]"
　　Sql = Sql & " FROM " & FILENAME
　　Sql = Sql & " WHERE [住所] = '東京'"
　　Set rs = New ADODB.Recordset
　　rs.Open Sql, cn, adOpenForwardOnly
　　MsgBox "住所が「東京」であるレコードの金額集計額:=" & _
　　　　　 CStr(rs.Fields("金額集計").Value)
　　rs.Close: Set rs = Nothing

　　' // サンプル2： 住所が「東京」かつ性別が「男」のレコード件数
　　Sql = ""
　　Sql = Sql & "SELECT COUNT(*) AS [件数]"
　　Sql = Sql & " FROM " & FILENAME
　　Sql = Sql & " WHERE ([住所] = '東京' AND [性別] = '男')"
　　Set rs = New ADODB.Recordset
　　rs.Open Sql, cn, adOpenForwardOnly
　　MsgBox "住所が「東京」かつ「男」であるレコード件数:=" & _
　　　　　 CStr(rs.Fields("件数").Value)

　　' // データベース接続を切ります
　　rs.Close: Set rs = Nothing
　　cn.Close: Set cn = Nothing

End Sub

お礼

回答ありがとうございます。
すみません。ＥＸＣＥＬがあるといってもＶＢＡを使ったことがなく、
標準モジュールに下記ソースコードを貼り付け、
　 Microsoft ActiveX 2.x Object Library
を参照設定します。
のところで挫折しました(^^;)

回答No.4

imogasi

>ツール等あればお教えいただけないでしょうか
これだけの目的だけをツールとしてソフトを発表するのは、おこがましい内容ですから,多分無いでしょう。（単一目的で、プログラムを組める人にとっては数行のものだから。）
ーー
エクセル（オフィス）２００７では１００万ちょっとまで読み込める設計になってますが、エクセルで使えるメモリとの兼ね合いで、経験しないと、開く途中で止まらないか、わからない。
ーー
＞テキストデータのある数値項目
これが具体的に判らないとVBAが組めない。
--
やる気があるなら、下記をやってみてください。
その前にテキストデータのある場所のフルパス、（ファイル名はわかるだろうから）拡張子を正確に1字1字書き留めること。
このやり方も判らないと、下記をやるのは難しいレベルかと思うが。
ーー
ツールーマクローVBEー＞VBE画面が出る
そのVBE画面のメニューで、
挿入ー標準モジュールー＞白紙の画面が出る
そこへ下記のコードをコピペ。
ーー
そして
C:\Documents and Settings\xxxx\My Documents\test01.txt
の部分を自分にあわせて書き換える。
ーー
そして、コピペしたコードの（どこでも良い）途中にカーソルを持っていってクリックし、F5キー（実行の意味）を押す。
ーー
結果シート（アクチブシート）のA1セル（1セルだけしかでないはず）にどう出るか、報告してみてください。
エラーが出て動かない場合も報告してみてください。その場合は
このやり方はあきらめましょう。
ーー
うまく読み込んでおれば、
データー区切り位置ーカンマやタブ・・ー次ぎへースペースー完了
でどうなるか。カンマで区切りなら、スペースの変わりにカンマを指定してみてください。
各列にどう分離されたか。
ーー
コード
Sub test01()
Open "C:\Documents and Settings\xxxx\My Documents\test01.txt" For Input As #1
Line Input #1, a
Cells(1, "A") = a
Close #1
End Sub
もしうまくいけば、残る課題である、１００万回の繰り返し方法と集計方法の数行のコードを回答します。ただ100万レコード（100万行）を読む時間は、相当かかるかもしれないので覚悟が必要です。
ーー
また上記の方法では、明細はエクセルには出ません（出せません）合計が出せるだけです。それでよければのはなし。
ーー
ちょっとさらに勉強すれば6万レコードずつに分割するプログラムに変えられる。

お礼

回答ありがとうございます。
お教えいただいた方法で、セルの区切りまでは出来ました。
Ａ１に文字列、Ａ２に数値が表示されました。

補足

すみません。Ａ１に文字列、Ｂ１に数値の間違いです。

回答No.3

ultraCS

EXCELは2007から、行数の制限が約100万行になりました(2の20乗です)。Accessを使ってはいかがですか

http://www.relief.jp/itnote/archives/001844.php

OpenOfficeのCALCはVer2.0で行数制限が65000行(2の16乗)ですから、現在の2.3でも多分同じでしょう、Googleのスプレッドシートもこの偽十を使っていますから無理ではないかと想像します。
ということで、OpenOfficeBase(データベース)しか対応策はなさそうです。

http://www.excel7.com/calc/calc_basic1.htm

表計算ソフトで確実に100万行扱えたのはCorelのQuattroProですが、現在、日本語版は現在発売されていませんので、英語版のWordPerfect Officeに含まれています。

http://www.corel.com/servlet/Satellite?c=Product_C1&cid=1162590899560&lc=en&pagename=CorelCom%2FLayout

他に三四郎や1-2-3がありますが、確か同じくらいの制限(65000行程度)です。
100万行のデータとなると、データベースを使うか簡単なプログラムで集計しないと厳しいと思いますよ(ファイルサイズ的にも)。

お礼

ご丁寧な回答ありがとうございます。
がんばります。

回答No.2

Evreux

フリーのオフィスソフトであるOpenOfficeや、無料のGoogleアカウントを取得すれば使えるGoogleSpreadSheetなどではだめでしょうか？
試していないので、１００万件程度のデータに対応できるかどうかわかりませんが。

http://ja.openoffice.org/
http://www.google.com/google-d-s/hpp/hpp_co_jp.html

お礼

ご回答ありがとうございます。
データがまだ手元にないため、入手次第試してみようかと
思います。

回答No.1

Evreux

Excel2007では今までよりも大量のセルが使えると聞いたことがあります。
調べてみるといいかもしれません。

また、合計を計算する程度のアプリケーションならば、Java等のプログラミング経験のあるひとに頼めば数十分程度で作ってもらえると思います。

お礼

早速のご回答ありがとうございました。
当方の環境はＥＸＣＥＬ２００２で、それ以外はありません。
ＪＡＶＡが出来る人も周りにはいません。
こんなときのために日ごろから勉強していればよかった・・・(:_;)