FC2ブログ

スポンサーサイト 


上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

--/--/--

Category: スポンサー広告

TB: --  /  CM: --

top △

大量なデータはどうする 


08.TXT(0): te001 20110801000000 0
08.TXT(1): te001 20110801000001 0
08.TXT(2): te001 20110801000002 1
08.TXT(3): te001 20110801000003 0
08.TXT(4): te001 20110801000004 3
08.TXT(5): te001 20110801000005 4

の様に1秒に1行記録されるテキストファイルがあります。
1ファイル1カ月の単位となっています。
空白区切りの3つ目は、yyyymmddhhnnss となっています。
空白区切りの4つ目の値を1日単位で集計したいのですが・・・・
60 * 60 * 24 * 31 = 2678400 レコードですか・・・・
Excel2007 でも、そのまま読み込めないみたい・・・・(1048576行まででエラー)

う~~ん。どうすればいいんですかね。

テーブル「T1」にフィールド「F1」~「F4」作って、ダミーデータを作ってみました。
2011/8 ~ 2011/10 の3カ月分を作ったところで accdb のファイルサイズは 460MB 越え。
しかも結構時間かかるよね・・・・

やりたいんだから、時間かかってもいいよね・・・ってことで。
 
まず、確認用のダミーデータを作りましょうかってんで
Private Sub test11()
  Dim rs As New ADODB.Recordset
  Dim i1 As Integer
  Dim i2 As Integer
  Dim i3 As Integer
  Dim i4 As Integer
  Dim i5 As Integer
  Dim i6 As Integer
  Dim j As Long

  CurrentProject.Connection.Execute "DELETE * FROM T1;"
  rs.Open "T1", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

  Randomize
  j = 0
  For i1 = 2011 To 2011
    For i2 = 8 To 10
      For i3 = 1 To 31
        Select Case i2
          Case 1, 3, 5, 7, 8, 10, 12
          Case 4, 6, 9, 11
              If (i3 = 31) Then Exit For
          Case 2
              If (i3 > 29) Then Exit For
              If ((i3 = 29) And ((i1 Mod 4) <> 0)) Then Exit For
        End Select
        For i4 = 0 To 23
          For i5 = 0 To 59
            For i6 = 0 To 59
              rs.AddNew
              rs("F1") = Format(i2, "00") & ".TXT(" & j & "):"
              rs("F2") = "te001"
              rs("F3") = Format(i1, "0000") & Format(i2, "00") & Format(i3, "00") _
                   & Format(i4, "00") & Format(i5, "00") & Format(i6, "00")
              rs("F4") = Int(Rnd() * j) Mod 80
              rs.Update
              j = j + 1
            Next
          Next
        Next
      Next
    Next
  Next
  rs.Close
End Sub

 
これで、データは作れました。
でも、テキストファイルに書き出さないと・・・・・で、
Private Sub test12()
  Dim rs As New ADODB.Recordset
  Dim vQ As Variant
  Dim vF As Variant
  Dim sS As String
  Dim ffn As Integer
  Dim i As Integer
  Const cPath As String = "E:\Hoge\"

  vQ = Array("SELECT * FROM T1 WHERE F1 Like '08%' ;" _
       , "SELECT * FROM T1 WHERE F1 Like '09%' ;" _
       , "SELECT * FROM T1 WHERE F1 Like '10%' ;")
  vF = Array("ts2_8.txt", "ts2_9.txt", "ts2_10.txt")

  For i = 0 To UBound(vQ)
    ffn = FreeFile
    Open cPath & vF(i) For Output As #ffn
    rs.Open vQ(i), CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    While (Not rs.EOF)
      sS = rs("F1") & " " & rs("F2") & " " & rs("F3") & " " & rs("F4")
      Print #ffn, sS
      rs.MoveNext
    Wend
    rs.Close
    Close #ffn
  Next
End Sub

や~~、テキストファイルもでかいですね。
8月分「ts2_8.txt」のサイズは 108,445 KB になりました。

テキストファイルを読み込んで・・・・もうこれはあきらめた方が良くなかろうか・・・
ちゃんとしたCSVファイルに整形して ADODB で繋いでクロス集計・・・・ってか?

前3つは "" で囲んで、区切りを ","(カンマ)にして・・・・
ベタで書き出してみるとこんな感じかなぁ
Private Function MakeStrFromRs(rs As ADODB.Recordset, Optional sJoin As String = ",") As String
  Dim sAry() As String
  Dim i As Integer

  ReDim sAry(rs.Fields.Count - 1)

  For i = 0 To UBound(sAry)
    Select Case rs.Fields(i).Type
      Case adChar, adWChar, adVarChar, adVarWChar
          sAry(i) = """" & rs.Fields(i) & """"
      Case Else
          sAry(i) = rs.Fields(i)
    End Select
  Next
  MakeStrFromRs = Join(sAry, sJoin)
End Function

Private Sub test12A(a)
  Dim rs As New ADODB.Recordset
  Dim vQ As Variant
  Dim vF As Variant
  Dim sS As String
  Dim ffn As Integer
  Dim i As Integer
  Const cPath As String = "E:\Hoge\"

  vQ = Array("SELECT * FROM T1 WHERE F1 Like '08%' ;" _
       , "SELECT * FROM T1 WHERE F1 Like '09%' ;" _
       , "SELECT * FROM T1 WHERE F1 Like '10%' ;")
  vF = Array("ts2_8s.csv", "ts2_9s.csv", "ts2_10s.csv")

  For i = 0 To UBound(vQ)
    ffn = FreeFile
    Open cPath & vF(i) For Output As #ffn
    rs.Open vQ(i), CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    While (Not rs.EOF)
      Print #ffn, MakeStrFromRs(rs)
      rs.MoveNext
    Wend
    rs.Close
    Close #ffn
  Next
End Sub

 
いや・・・・
元々はテキストファイルだよね・・・
テキストファイルを読み込んで整形したものを書き出す・・・これ、以前の記事にありました。
テキストファイル(CSV等)の項目変換
ただ、いろんなことが出来るように機能を組み込んでいたので、時間がかかりすぎ・・・
(以下に記述する方法での時間の3倍以上)
やはり専用にした方が速いよねってことで、
Private Sub test13()
  Dim vQ As Variant
  Dim vF As Variant
  Dim sS As String
  Dim ffnI As Integer
  Dim ffnO As Integer
  Dim i As Integer
  Const cPath As String = "E:\Hoge\"

  vQ = Array("ts2_8.txt", "ts2_9.txt", "ts2_10.txt")
  vF = Array("ts2_8.csv", "ts2_9.csv", "ts2_10.csv")

  For i = 0 To UBound(vQ)
    ffnI = FreeFile
    Open cPath & vQ(i) For Input As #ffnI
      ffnO = FreeFile
      Open cPath & vF(i) For Output As #ffnO
        While (Not EOF(ffnI))
          Line Input #ffnI, sS
          sS = """" & Replace(sS, " ", """,""")
          Mid(sS, InStrRev(sS, """"), 1) = " "
          Print #ffnO, Replace(sS, " ", "")
        Wend
      Close #ffnO
    Close #ffnI
  Next
End Sub

いや、でもね・・・変換だけに Access 使うのも何ですよねってことで、
VBS ファイルを作っちゃいましょう。
で、エクスプローラで元のテキストファイルを複数なりドラッグして、
このVBSファイルにドロップしませんか・・・・と、
*.txt ファイルが *.csv として生成されます。
VBSのファイル名を、ts2.vbs としておきましょうか・・・・
Dim FSO
Dim InFile, OutFile
Dim sF, vF
Dim sS, sA
Dim i

Set FSO = CreateObject("Scripting.FileSystemObject")

For Each vF In WScript.Arguments
  sF = Left(vF, InStrRev(vF, "\"))
  vF = Mid(vF, InStrRev(vF, "\") + 1)
  Set InFile = FSO.OpenTextFile(sF & vF)

  vF = Left(vF, InStrRev(vF, ".")) & "csv"
  Set OutFile = FSO.OpenTextFile(sF & "_" & vF, 2, True)

  Do Until InFile.AtEndOfStream
    sS = InFile.ReadLine
    sA = Split(sS, " ")
    For i = 0 To UBound(sA) - 1
      sA(i) = """" & sA(i) & """"
    Next
    sS = Join(sA, ",")
    OutFile.WriteLine sS
  Loop
  InFile.Close
  OutFile.Close
  Set OutFile = Nothing
  Set InFile = Nothing
Next

Set FSO = Nothing

※ インデントを付けるために全角スペースが先頭に埋まってます
  VBAに貼り付ける時には問題ありませんが、VBS ファイルではエラーになります


"08.TXT(0):","te001","20110801000000",0
"08.TXT(1):","te001","20110801000001",0
"08.TXT(2):","te001","20110801000002",1
"08.TXT(3):","te001","20110801000003",0
"08.TXT(4):","te001","20110801000004",3
"08.TXT(5):","te001","20110801000005",4

のように整形しておきます。
整形した8月のファイルサイズは 124,138 KB にチョッと膨れます。

さて、ここからです。
ADODB で繋いでやるんだったら、別に Access でなくてもいいんじゃない?
Excel でクロス集計して、結果を張り付ければ・・・その後いろいろ加工できるよねって。

以下、Excel2007 に記述したもの
Public Sub Sample1()
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim vF As Variant
  Dim sSql As String
  Dim i As Long, j As Integer
  Const PAS As String = "E:\Hoge" ''' ファイルまでのパス

  vF = Array("ts2_8.csv", "ts2_9.csv", "ts2_10.csv")

  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
      & "Data Source=" & PAS & ";" _
      & "Extended Properties='text;HDR=No;FMT=Delimited'"

  For i = 0 To UBound(vF)
    ActiveCell = vF(i)
    sSql = ""
    sSql = sSql & "" & "TRANSFORM CDbl(Format(Sum(F4)/(24*60*60),'0.00')) AS 値"
    sSql = sSql & " " & "SELECT CLng(LEFT(F3,6)) AS 年月"
    sSql = sSql & " " & "FROM " & vF(i)
    sSql = sSql & " " & "GROUP BY LEFT(F3,6)"
    sSql = sSql & " " & "PIVOT Mid(F3,7,2);"
    rs.Source = sSql
    rs.Open , cn, adOpenForwardOnly, adLockReadOnly
    If (Not rs.EOF) Then
      For j = 0 To rs.Fields.Count - 1
        ActiveCell.Offset(1, j) = rs.Fields(j).Name
      Next
      ActiveCell.Offset(2, 0).CopyFromRecordset rs
    End If
    rs.Close
    Cells(Cells(32767, ActiveCell.Column).End(xlUp).Row + 3, ActiveCell.Column).Select
  Next
  Set cn = Nothing
End Sub

Public Sub Sample2()
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim vF As Variant
  Dim sSql As String
  Dim i As Long, j As Integer
  Const PAS As String = "E:\Hoge" ''' ファイルまでのパス

  vF = Array("ts2_8.csv", "ts2_9.csv", "ts2_10.csv")

  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
      & "Data Source=" & PAS & ";" _
      & "Extended Properties='text;HDR=No;FMT=Delimited'"

  For i = 0 To UBound(vF)
    ActiveCell = vF(i)
    sSql = ""
    sSql = sSql & "" & "TRANSFORM CDbl(Format(Sum(F4)/(60*60),'0.00')) AS 値"
    sSql = sSql & " " & "SELECT CLng(LEFT(F3,6)) AS 年月, CInt(Mid(F3,7,2)) AS 日"
    sSql = sSql & " " & "FROM " & vF(i)
    sSql = sSql & " " & "GROUP BY LEFT(F3,6), Mid(F3,7,2)"
    sSql = sSql & " " & "PIVOT Mid(F3,9,2);"
    rs.Source = sSql
    rs.Open , cn, adOpenForwardOnly, adLockReadOnly
    If (Not rs.EOF) Then
      For j = 0 To rs.Fields.Count - 1
        ActiveCell.Offset(1, j) = rs.Fields(j).Name
      Next
      ActiveCell.Offset(2, 0).CopyFromRecordset rs
    End If
    rs.Close
    Cells(Cells(32767, ActiveCell.Column).End(xlUp).Row + 3, ActiveCell.Column).Select
  Next
  Set cn = Nothing
End Sub

Public Sub Sample3()
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim vF As Variant
  Dim sSql As String
  Dim i As Long, j As Integer
  Const PAS As String = "E:\Hoge" ''' ファイルまでのパス

  vF = Array("ts2_8.csv", "ts2_9.csv", "ts2_10.csv")

  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
      & "Data Source=" & PAS & ";" _
      & "Extended Properties='text;HDR=No;FMT=Delimited'"

  For i = 0 To UBound(vF)
    ActiveCell = vF(i)
    sSql = ""
    sSql = sSql & "" & "TRANSFORM CDbl(Format(Sum(F4)/(60),'0.00')) AS 値"
    sSql = sSql & " " & "SELECT CLng(LEFT(F3,6)) AS 年月, CInt(Mid(F3,7,2)) AS 日, CInt(Mid(F3,9,2)) AS 時"
    sSql = sSql & " " & "FROM " & vF(i)
    sSql = sSql & " " & "GROUP BY LEFT(F3,6), Mid(F3,7,2), Mid(F3,9,2)"
    sSql = sSql & " " & "PIVOT Mid(F3,11,2);"
    rs.Source = sSql
    rs.Open , cn, adOpenForwardOnly, adLockReadOnly
    If (Not rs.EOF) Then
      For j = 0 To rs.Fields.Count - 1
        ActiveCell.Offset(1, j) = rs.Fields(j).Name
      Next
      ActiveCell.Offset(2, 0).CopyFromRecordset rs
    End If
    rs.Close
    Cells(Cells(32767, ActiveCell.Column).End(xlUp).Row + 3, ActiveCell.Column).Select
  Next
  Set cn = Nothing
End Sub

※ これ一応動いたのだけれども、
Provider=Microsoft.ACE.OLEDB.12.0 は、Accessが入っていたから使えたのか、どうなのか??
Access が入っていない PC がなかったので確認できず(なので、わかりません)

もちろんのこと、VBEの「ツール」→「参照設定」で
Microsoft ActiveX Data Objects 2.5 Library
を追加しておきます。(ADODBを使うので)
(2.5 としたのは、Access2007 → 2000用mdb にするのに楽なので、それを適用してみました)

で、出力されたものの一部は以下のような感じ。(データ削ってます)
kEnt103
横軸の数値は、
「Sample1」では、何日
「Sample2」では、何時
「Sample3」では、何分
違いは、クロス集計をチョッと変えるだけですね。


今回サンプルファイルはありません。

1ファイル処理に2~3分(Vista: Core(TM)2 Duo T7250 2.00GHz)

今回 Connection で CSV ファイルを直接指定したけど・・・
リンクっていう方法もあるよね・・・

後でまとめておこう・・・・・・・・
関連記事

2011/11/26

Category: サンプルかな

TB: 0  /  CM: 0

top △

この記事に対するコメント

top △

コメントの投稿

Secret

top △

トラックバック

トラックバックURL
→http://kikutips.blog13.fc2.com/tb.php/103-8f388d95
この記事にトラックバックする(FC2ブログユーザー)

top △


上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。