テキストファイルの列を抜き出しての連結方法

Question

お世話になります。複数の連番テキストファイルの処理方法にて教えていただきたいです。
現在スペース区切りのテキストファイルで1番から～最終番まで連番で名前が付けられたファイルがあります。(data1.txt   data2.txt   data3.txt・・・・・data1000.txt…場合によってはdata10000.txtぐらいまで)
そのファイルの中身は、data1.txtを開くと
         
           1　　　dataA　   dataB      dataC     dataD      dataE　・・・　  data 
           2　　　dataA　   dataB      dataC     dataD      dataE　・・・　  data 

1000000　　　dataA　   dataB      dataC     dataD      dataE　・・・　  data 

といった具合に行列形式にて格納される形となっております。実験区の数（列数）は不定であり、そのデータの行数は不定でありますが、全部のtxtファイルに同じ行数および同じ列数で入っております。(ファイル間でのデータの抜けはありません）

今回やりたい事は、エクセルに指定した列のデータを連番順に列に張り付けたいという事です。最終的処理したいデータはエクセル上に展開してグラフを作成するため、できればVBAを使い行えればと思っております。
例えば、二列目のデータのみをエクセルに抽出しろという指示を出すと
A列には必ずdata1.txtの1列目を張り付けて、B列にはdata1.txtの２列目を張り付けて、C列にはdata2.txtの２列目を張り付けて、D列にはdata3.txtの２列目を張り付けてといった具合です。
エクセルにこだわる理由としては、使用するコンピュータのデータは持ちだすことは出来ず、新規ソフトのインストールも禁止のPCであるためエクセル２０１０内にあるVBAしかプログラム作成ツールがないからです。(cmdはユーザーからは使えないように設定されています。)

現在は年内中にどうにかしなければならないために手作業で展開して張り付けてるのですが、いかんせんファイルも大きく、はかどっていないのが現状です。
当方、エクセル内に展開したデータの抽出などに関してのVBAは組めるのですが、外部データ参照した経験が無く、作成方法が思いつきませんでした。勉強しておけばと後悔中です。

今回は一応１００万行以内に収まっているためいきなりエクセルが対応できると思うのですが、今後200万行を超えてくるのが確定しているため、上記でエクセルに展開というところは直接新規テキストファイルに並べるという方がありがたいですが、こちらは可能なのでしょうか？(その集計ファイルの名前は任意でつけれるともっと嬉しいです）

わがままなお願いなのはわかっておりますが、受け入れてくださるとありがたいです。

tatsu99 · Accepted Answer

No11の改善版です。
No11で大きなファイルを読み込んだとき、思ったより時間がかかったので、見なおしました。4倍ほど速くなっています。
-----------------------------------------------------
1:# coding:WINDOWS-31J
2:#
3:def main()
4:  if ARGV.size != 5
5:    spname = $0.sub(/^.*//,"")
6:    print "#{spname} P1 P2 P3 P4 P5
"
7:    print "P1:データファイル格納フォルダ
"
8:    print "P2:最小データファイル番号
"
9:    print "P3:最大データファイル番号
"
10:    print "P4:抽出列番号
"
11:    print "P5:作成ファイル名
"
12:    exit 10
13:  end
14:  dataDir = ARGV.shift
15:  minFileNo = (ARGV.shift).to_i
16:  maxFileNo = (ARGV.shift).to_i
17:  trgColNo = (ARGV.shift).to_i
18:  outFile = ARGV.shift
19:  if File.directory?(dataDir) == false
20:    print "#{dataDir} は存在しません
"
21:    exit 10
22:  end
23:  if minFileNo < 1
24:    print "最小データファイル番号不正
"
25:    exit 10
26:  end
27:  if maxFileNo < 1
28:    print "最大データファイル番号不正
"
29:    exit 10
30:  end
31:  if minFileNo > maxFileNo
32:    print "最小データファイル番号が最大データファイル番号より大きい
"
33:    exit 10
34:  end
35:  if trgColNo < 2
36:    print "抽出列番号不正
"
37:    exit 10
38:  end
39:  $lines = Array.new
40:  t1 = Time.now
41:  #ファイル読み込み
42:  (minFileNo..maxFileNo).each do |fno|
43:    dt1 = Time.now
44:    if readFile(dataDir,fno,trgColNo,minFileNo) == false
45:      exit 10
46:    end
47:    dt2 = Time.now
48:    print "ファイルNo.#{fno} 読込完了 時間(秒)=#{dt2-dt1}
"
49:  end
50:  #ファイル出力
51:  ofname = dataDir + "/" + outFile
52:  File.open(ofname,"w"){|fs|
53:    (0...$lines.size).each do |i|
54:      fs.print $lines[i],"
"
55:    end
56:  }
57:  t2 = Time.now
58:  print "処理完了 所要時間(秒)=#{t2-t1}
"
59:end
60:#ファイル読み込み
61:def readFile(datadir,fno,col,stno)
62:  fname = datadir + "/" + "data" + fno.to_s + ".txt"
63:  if File.exist?(fname) == false
64:    print "#{fname} は存在しません
"
65:    return false
66:  end
67:  File.open(fname,"r"){ |fs|
68:    lno = 0
69:    while line = fs.gets
70:      elm = line.split(nil)
71:      if fno == stno
72:        $lines[lno] = elm[0]
73:      end
74:      $lines[lno] += " " + elm[col-1]
75:      lno += 1
76:    end
77:  }
78:end
79:main()
--------------------------------------------------
もし、既にNo11を使われている場合は、
以下の2か所を修正してください。
1)67行相当の以下の箇所
変更前
File.open(fname,"rt"){ |fs|
変更後
File.open(fname,"r"){ |fs|

2)70行相当の以下の箇所
変更前
      elm = line.split(/\s+/)
変更後
elm = line.split(nil)

---------------------------------------
ちなみにVBAとの処理時間の比較ですが、こちらの環境では
180Mバイト（150万行）の１つのファイルを読み込むとき、
VBA：１６～１７秒
ruby改善版：５～６秒
となっています。
rubyのほうが約2～３倍速いです。
ruby版を使用したほうが、効率は良いと考えます。

tatsu99 · Answer

ruby版で最小ファイル番号を指定可能にしました。
-----------------------------------------
# coding:WINDOWS-31J
#
def main()
  if ARGV.size != 5
    spname = $0.sub(/^.*//,"")
    print "#{spname} P1 P2 P3 P4 P5
"
    print "P1:データファイル格納フォルダ
"
    print "P2:最小データファイル番号
"
    print "P3:最大データファイル番号
"
    print "P4:抽出列番号
"
    print "P5:作成ファイル名
"
    exit 10
  end
  dataDir = ARGV.shift
  minFileNo = (ARGV.shift).to_i
  maxFileNo = (ARGV.shift).to_i
  trgColNo = (ARGV.shift).to_i
  outFile = ARGV.shift
  if File.directory?(dataDir) == false
    print "#{dataDir} は存在しません
"
    exit 10
  end
  if minFileNo < 1
    print "最小データファイル番号不正
"
    exit 10
  end
  if maxFileNo < 1
    print "最大データファイル番号不正
"
    exit 10
  end
  if minFileNo > maxFileNo
    print "最小データファイル番号が最大データファイル番号より大きい
"
    exit 10
  end
  if trgColNo < 2
    print "抽出列番号不正
"
    exit 10
  end
  $lines = Array.new
  t1 = Time.now
  prev_t = t1
  #ファイル読み込み
  (minFileNo..maxFileNo).each do |fno|
    if readFile(dataDir,fno,trgColNo,minFileNo) == false
      exit 10
    end
    now_t = Time.now
    print "ファイルNo.#{fno} 読込完了 時間(秒)=#{now_t-prev_t}
"
    prev_t = now_t
  end
  #ファイル出力
  ofname = dataDir + "/" + outFile
  File.open(ofname,"w"){|fs|
    (0...$lines.size).each do |i|
      fs.print $lines[i],"
"
    end
  }
  t2 = Time.now
  print "処理完了 所要時間(秒)=#{t2-t1}
"
end
#ファイル読み込み
def readFile(datadir,fno,col,stno)
  fname = datadir + "/" + "data" + fno.to_s + ".txt"
  if File.exist?(fname) == false
    print "#{fname} は存在しません
"
    return false
  end
  File.open(fname,"rt"){ |fs|
    lno = 0
    while line = fs.gets
      elm = line.split(/\s+/)
      if fno == stno
        $lines[lno] = elm[0]
      end
      $lines[lno] += " " + elm[col-1]
      lno += 1
    end
  }
end
main()
-------------------------------------
これをsample2.rbとして
ruby sample2.rb　と入力すると下記Usageが表示されます。
sample2.rb P1 P2 P3 P4 P5
P1:データファイル格納フォルダ
P2:最小データファイル番号
P3:最大データファイル番号
P4:抽出列番号
P5:作成ファイル名

usageに従って、パラメータを入力してください。

tatsu99 · Answer

>上記のとおり修正したのちは、最小ファイルナンバーの指定は、必要なのでしょうか？
>必要ならば、私が前回質問したような形でminfileNoを設定しておけばよいのでしょうか？

はい、以下の作業が行われた前提に対する回答です。
>Dim minffileNo As Long '最小データファイル番号
>を追加してE2セルに最小ファイル番号を入力するようにして、
>minfileNo=CLng(sh.Cells(2, "E").Value)
>を追加しました。
>そしてFor i = 1 To maxfileNo部分を
>For i = minfileNo To maxfileNo
>に書き直して

従って、実行時は、必ず、E2に最小ファイル番号を設定しておいてください。

出来れば、以下のチェックも行っておいたほうが、運用上間違いが無いでしょう。
１）最小ファイル番号が１未満ならエラー
２）最小ファイル番号＞最大ファイル番号ならエラー

tatsu99 · Answer

>さらに解析機はネットワークにつながっていないPCなので手打ちで入力しているため、回答が遅くなり申し訳ありません

そのような事情であれば、どうぞゆっくりと返信ください。
なお、ruby版でも開始ファイル番号の指定を行うようにしたい場合は、その旨、補足ください。
開始ファイル番号で指定されたファイルのみ、１列目と指定列目のデータを抽出するようにします。

tatsu99 · Answer

>最小ファイル番号と最大ファイル番号で制御する場合、どのようにしたら良かったのでしょうか？
例えば、最小ファイル番号＝７としたとき、
data7.txt～data○○.txtまで処理しますが（○○は最大ファイル番号）
data7.txtは、
「1列目のデータと指定された列のデータを抽出する」ということで良いのですか。
そうであれば、readFileに開始番号が何番かを判らせる必要があります。
readFileを以下のようにします。
Private Function readFile(ByVal datadir As String, ByVal fno As Long, ByVal col As Long, ByVal stno As Long) As Boolean

If fno = 1 Then
            '最初のファイルのみ、1番目の列を設定
            lines(lno) = data(0)
        End If

を
        If fno = stno Then
            '最初のファイルのみ、1番目の列を設定
            lines(lno) = data(0)
        End If
のようにします。
（If fno = 1 Then をIf fno = stno Thenに変えます）

呼び出し側は、
        If readFile(datadir, i, trgColNo) = False Then Exit Sub
を
        If readFile(datadir, i, trgColNo,minfileNo) = False Then Exit Sub

に変えてください。

tatsu99 · Answer

ruby 2.0であれば、以下のスクリプトが動作するはずです。
------------------------------------------------
# coding:WINDOWS-31J
#
def main()
  if ARGV.size != 4
    spname = $0.sub(/^.*//,"")
    print "#{spname} P1 P2 P3 P4
"
    print "P1:データファイル格納フォルダ
"
    print "P2:最大データファイル番号
"
    print "P3:抽出列番号
"
    print "P4:作成ファイル名
"
    exit 10
  end
  dataDir = ARGV.shift
  maxFileNo = (ARGV.shift).to_i
  trgColNo = (ARGV.shift).to_i
  outFile = ARGV.shift
  if File.directory?(dataDir) == false
    print "#{dataDir} は存在しません
"
    exit 10
  end
  if maxFileNo < 1
    print "最大データファイル番号不正
"
  end
  if trgColNo < 2
    print "抽出列番号不正
"
  end
  $lines = Array.new
  t1 = Time.now
  #ファイル読み込み
  (1..maxFileNo).each do |fno|
    if readFile(dataDir,fno,trgColNo) == false
      exit 10
    end
  end
  #ファイル出力
  ofname = dataDir + "/" + outFile
  File.open(ofname,"w"){|fs|
    (0...$lines.size).each do |i|
      fs.print $lines[i],"
"
    end
  }
  t2 = Time.now
  print "処理完了 所要時間(秒)=#{t2-t1}
"
end
#ファイル読み込み
def readFile(datadir,fno,col)
  fname = datadir + "/" + "data" + fno.to_s + ".txt"
  if File.exist?(fname) == false
    print "#{fname} は存在しません
"
    return false
  end
  File.open(fname,"rt"){ |fs|
    lno = 0
    while line = fs.gets
      elm = line.split(/\s+/)
      if fno == 1
        $lines[lno] = elm[0]
      end
      $lines[lno] += " " + elm[col-1]
      lno += 1
    end
  }
end
main()
--------------------------------------------
このスクリプトを適当なファイル名で保存してください。（sample.rb）とします。
コマンドプロンプトで、その保存したディレクトリに移動し
ruby sample.rb
と入力すると、
sample.rb P1 P2 P3 P4
P1:データファイル格納フォルダ
P2:最大データファイル番号
P3:抽出列番号
P4:作成ファイル名
のようにUsageが表示されます。
（rubyはパスが通っていることが前提です）

ruby sample.rb d:\goo\data 3 3 outfile.txt
のように入力すると、ＶＢＡと同様に処理を行い、outfile.txtへ結果を
出力します。
VBAと違って、ファイルサイズの制限はありません。
メモリの許す限り巨大なファイルも扱えます。
少しずつファイルサイズを大きくして、確認してください。

tatsu99 · Answer

>cmdはwinキー＋Rを押して、ファイル名指定からの実行では起動しないようにされているという状態です。
ということは、コマンドプロンプトは使用できますね。
コマンドプロンプトで
ruby -v
と入力すると以下のようなバージョンが表示されます。

ruby 2.3.1p112 (2016-04-26 revision 54768) [i386-mingw32]

rubyのバージョンは、いくつでしょうか？
1.8以下だと、まずいですが、1.9以上なら問題ありません。

tatsu99 · Answer

以下のソースは、ファイルサイズが小さい前提でしか動作しませんが、
あなたの要件を実現するＶＢＡのサンプルです。
---------------------------------------------------------
Option Explicit

Dim lines() As String       'データ
Dim maxlineNo               '最大行番号
Public Sub データファイル作成()
    Dim datadir As String       'データファイル格納フォルダ
    Dim maxfileNo As Long       '最大データファイル番号
    Dim trgColNo As Long        '抽出列番号
    Dim filename As String      '作成ファイル名
    Dim i As Long
    Dim sh As Worksheet
    Dim stime As Date
    Dim etime As Date
    If MsgBox("データファイルを作成します", vbOKCancel) = vbCancel Then Exit Sub
    Set sh = Worksheets("データ管理")
    datadir = sh.Cells(1, "B").Value
    maxfileNo = CLng(sh.Cells(2, "B").Value)
    trgColNo = CLng(sh.Cells(3, "B").Value)
    filename = sh.Cells(4, "B").Value
    If dir(datadir, vbDirectory) = "" Then
        MsgBox (datadir & "は存在しません。")
        Exit Sub
    End If
    If maxfileNo < 1 Then
        MsgBox ("最大データファイル番号不正")
        Exit Sub
    End If
    If trgColNo < 2 Then
        MsgBox ("抽出列番号不正")
        Exit Sub
    End If
    stime = Time
    maxlineNo = 0
    For i = 1 To maxfileNo
        If readFile(datadir, i, trgColNo) = False Then Exit Sub
    Next
    If writeFile(datadir, filename) = False Then Exit Sub
    etime = Time
    MsgBox (filename & "作成完了。所要時間(秒)=" & Second(etime - stime))
End Sub

'データファイル読み込み
Private Function readFile(ByVal datadir As String, ByVal fno As Long, ByVal col As Long) As Boolean
    Dim fname As String
    Dim fileNo As Long
    Dim text As String
    Dim lno As Long
    Dim data As Variant
    readFile = False
    'ファイル名の完全パスを作成
    fname = datadir & "\" & "data" & fno & ".txt"
    If dir(fname) = "" Then
        MsgBox (fname & "は存在しません。")
        Exit Function
    End If
    fileNo = FreeFile   '空き番号取得
    'ファイルオープン
    Open fname For Input As #fileNo
    'ファイル終端まで読み込む
    lno = 0
    Do Until EOF(fileNo)
        Line Input #fileNo, text
        lno = lno + 1
        '最大行番号を超えた場合は、配列を拡張する
        If lno > maxlineNo Then
            maxlineNo = lno
            ReDim Preserve lines(maxlineNo)
        End If
        '空白で分割
        data = Split(text, " ")
        If fno = 1 Then
            '最初のファイルのみ、1番目の列を設定
            lines(lno) = data(0)
        End If
        'ファイルは空白を付けて該当列データを付け足す
        lines(lno) = lines(lno) & " " & data(col - 1)
    Loop
    Close #fileNo
    '成功終了
    readFile = True
End Function
'ファイル書き込み
Private Function writeFile(ByVal datadir As String, ByVal filename As String) As Boolean
    Dim fname As String
    Dim fileNo As Long
    Dim text As String
    Dim lno As Long
    writeFile = False
    'ファイル名の完全パスを作成
    fname = datadir & "\" & filename
    fileNo = FreeFile   '空き番号取得
    'ファイルオープン
    Open fname For Output As #fileNo
    '1行から最終行まで書き込む
    For lno = 1 To maxlineNo
        Print #fileNo, lines(lno)
    Next
    Close #fileNo
    writeFile = True
End Function
-----------------------------------------------------
使用方法ですが、添付の図のように、シート名「デー管理」のシートに
Ｂ１にデーファイルが化のうされているフォルダ名
Ｂ２に最大データファイルの番号
Ｂ３に抽出列の番号
Ｂ４に作成ファイル名
を記述し、本マクロを実行します。
例では、
d:\goo\dataのフォルダから
data1.txt～data3.txt
の３列目を抽出し、out.txtに出力しています。
（out.txtはデータファイル格納フォルダと同じフォルダに格納されます）

tatsu99 · Answer

返信、ありがとうございました。
>ファイルあたり制限が2GBということならば100ファイルずつまでは何とかなるという計算ですか。
はい、それであれば、なんとか動くと思われます。

せめて、perlかrubyがインストールされていれば、２Ｇバイトの制限もなく、テキストファイルの扱いも簡単なので、
なんとかなるかと思うのですが、それらはインストールされていないのでしょうか？
又、「cmdはユーザーからは使えないように設定されています。」とは、コマンドプロンプト
が使えないようになっているということでしょうか？

tatsu99 · Answer

１つのデータファイルは２Ｇバイト以内でしょうか。
又、作成するデータファイルも２Ｇバイト以内でしょうか。
ＶＢＡで標準で提供されている、open,read,write,closeの関数はたしか２Ｇバイトを超えると正常に動作しないはずです。
１つのデータの桁数は何文字でしょうか。
例えば、水平速度が、32.50 という数字なら５バイトを使います。更に空白が１バイト付加されるため６バイトになります。
もし、２００万行でdata1.txtからdata10000.txtを水平速度について集計すると
概算で１行のサイズ＝6バイト×10000=60000バイト
２００万行なので
計＝120000000000バイト=120,000,000,000バイト＝120Ｇバイトになります。
この為ＶＢＡ標準のファイル入出力関数は使用できません。
もし、何らかの方法でこの１２０Ｇバイトのテキストファイルを作成できたとしても、
このテキストファイルをexcelに取り込めるかどうかは非常に疑問です。
通常のパソコンのメモリは多くても１０Ｇバイト程度かと思いますので、その上限をはるかに超えています。

一度、最大のサイズのデータファイルをつくるとすると、それがどの程度のサイズになるのかを
概算で計算していただけませんでしょうか。

又、処理の方法ですがＶＢＡか他の言語かに関係なく、
出力用のデータファイルを一旦内部メモリに作り上げ、それを一気にファイルへ出力するのが、一番簡単ですが
今回は、メモリの使用量が多すぎます。
その為、現実的には、
１）data1.txtを読み込み、２列の２００万行の出力ファイルを作成し、
２）次に、その出力ファイルとdata2.txtを読み込み、３列の２００万行の出力ファイルを作成し、
・・・
10000)最終的に1000１列200万行の出力ファイルを作成。
になるような気がします。
その為、ファイルＩＯに多量の時間を要することが考えられます。

テキストファイルの列を抜き出しての連結方法

No11の改善版です。

ruby版で最小ファイル番号を指定可能にしました。

>上記のとおり修正したのちは、最小ファイルナンバーの指定は、必要なのでしょうか？

>さらに解析機はネットワークにつながっていないPCなので手打ちで入力しているため、回答が遅くなり申し訳ありません

>最小ファイル番号と最大ファイル番号で制御する場合、どのようにしたら良かったのでしょうか？

ruby 2.0であれば、以下のスクリプトが動作するはずです。

>cmdはwinキー＋Rを押して、ファイル名指定からの実行では起動しないようにされているという状態です。

以下のソースは、ファイルサイズが小さい前提でしか動作しませんが、

返信、ありがとうございました。

１つのデータファイルは２Ｇバイト以内でしょうか。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング