段々とVBAのお勉強も深まってくると、シートに書けないぐらいのデータを処理したくなるもんだ - CSVサンプルファイル（そのほか）

  
実際、エクセルのワークシートでは、1,048,576行までデータが書けます。
でも、最終行までデータを書き込んで運用した人は、聞いたことも無い。

一方、列数は16,384列まで、セル数で総計１７１億個も入力できるの。
実に天文学的な数字だと思いますが、それだけ大きなビッグデータだな。

要するに、エクセルでもソフトデザインでは、それらを取り扱えるのです。
まあ、世の中では、企業がビッグデータを利用するのが流行っています。

特に、天気や天候に影響を受ける食品や衣料の需要予測では大事。
ビールなんか冷夏になれば、売り上げも伸び悩んで出荷量も減るしな。

こういった、数値化の難しかったデータを取り込んで分析しようと試みる。
だから、データが複雑で巨大化するので、ビッグデータとしたのでしょうな。

ただ、自分的にはエクセルに書けるデータ数でもビッグデータと思います。
まあ、パソコンのCPU処理能力では、VBAの計算に限界もあるでしょう。

そう思っていましたが、最近、VBAでSQLコマンドの操作を習得しました。
SQLと言うのは、良くシーケルと言い習わしますが、RDBの本家本元だ。

既に半世紀前のコンセプトですが、未だにデータ処理では王道のはず。
プログラミングやデータ処理で優れていますが、VBAでも使えるのです。

それで、CSVをエクセルに読込んで、SQLコマンドで検索させてみました。
二段階で絞込み検索をして、その結果を表示するコードを書いた分け。

でも、個人的にサンプルになるようなデータは、持ち合わせていません。
現在の職責では、そんな大きなデータに触れるチャンが全く無いのです。

    
となれば、ネットにどこか転がっていないものかと、ググッて探してみました。
ところが、キーワードが日本語では、探しても以外に見つからないんだ。

じゃあ、残るは英語だとばかりに入力してググリますが、なんとありました。
150万行・14項目のCSVファイルが用意されており、33MBの圧縮容量。

解凍すれば183MBとかなりの大きさで、もはやエクセルで表示できない。
それほど、個人が実験する事例としては、自分なりのビッグデータですな。

実際にダウンロードして回答してから、メモ帳で開こうとしますがフリーズ。
常識的に見ても、メモ帳でMBサイズのファイルを開くこともないでしょう。

なので、プログラマーが使うエディターと言うのを急きょ探してみました。
まあ、インターネットがモデムで通信する時代は、秀丸エディターが有名。

これって、BBS通信などにテキストラインを入力して送信した頃のお話。
四半世紀も前の時代で、昔語りと言うわけですが、ネットに変りは無い。

これを見ても、インターネットが初期の頃は、プログラマー達の独壇場。
素人の誰しもが、電話回線からネットを見に行く時代ではなかったもの。

それで、エディターは、マイクロソフトのVisualCodeEditorをインストール。
昔懐かしい、白黒反転画面もふさわしく、150万行はあっさり表示です。

   
それで、ギガバイトクラスのCSVファイルでも、エディターは開いてくれます。
でも、フリーのデータが見つからないので、取り合えず150万行で我慢だ。

他方、セルに目一杯書き込んでみると、どれぐらい時間を必要とするか。
以前、一億個のセルに乱数を書き込むマクロで、投稿を紹介しました。

４０分ぐらい掛かったと思いますが、大半はシートの表示に費やされます。
実際の乱数の計算と出力なんて瞬速ですから、シート表示は無意味。

要するに、ビッグデータは解析して結果が出てこそ意味のあるものです。
なので、エクセルにしても、”検索”機能こそがデータ処理の本命なんだ。

そう思って、四段階で絞込みを実施して、エクセルのシートに表示する。
結局、ワークシートって処理した最終結果のデータを表示させるだけね。

というわけで、割り切ってエクセルを使うと素人もビッグデータは扱えます。
今回、検索として、一致が２件、部分的に一致が１件、数値の範囲内が１件の計四条件で絞込みを掛けて、検索結果１件を抽出するのに所要した時間は、たった１５秒であり、恐るべしSQLの検索能力と感銘を受けたのでした。

※コードのサンプルだよ
Option Explicit
Sub Action()

 Dim StartTime As Variant
 Dim StopTime As Variant

ActiveSheet.Cells.Clear

  'ここから実行時間のカウントを開始します
  StartTime = Time

Call tmp

  StopTime = Time
  StopTime = StopTime - StartTime

  MsgBox "所要時間は" & Minute(StopTime) & "分" & _
                                  Second(StopTime) & "秒 でした"

End Sub

Sub tmp()

On Error GoTo Prcs_Error
    Dim AdoCnnct  As New ADODB.Connection
    Dim RcrdSt    As New ADODB.Recordset
    Dim ExtntnSet   As String
    Dim CnnctDir     As String   ' 接続先フォルダ
    Dim Sql        As String   ' SQL
    Dim WrkSht      As Worksheet
    Dim HdrItem        As Long     ' ヘッダー表示のループ変数
 
Application.ScreenUpdating = False
 
    CnnctDir = ActiveWorkbook.Path & "\"
 
    ' プロパイダの設定
    AdoCnnct.Provider = "Microsoft.ACE.OLEDB.12.0"    ' Office 2007 以降

    ' 読み込むファイルの格納フォルダのパス
    AdoCnnct.Properties("Data Source") = CnnctDir
 
    ' その他のプロパティの設定
    ExtntnSet = "text"
    ExtntnSet = ExtntnSet & ";FMT=Delimited"
    ExtntnSet = ExtntnSet & ";HDR=Yes"
    AdoCnnct.Properties("Extended Properties").Value = ExtntnSet
 
    ' 接続開始
    AdoCnnct.Open
 
    Sql = "SELECT * FROM [1500000SalesRecords.csv] where Country = 'Japan' And Item_Type = 'Fruits' And Sales_Channel = 'Offline' And Order_date Like '2017%' And Total_Profit BETWEEN 5000 AND 10000" 'Country = 'Japan'"
 
    ' SQL実行
    RcrdSt.Open Sql, AdoCnnct
 
    If RcrdSt.EOF Then
        ' 結果が1行もない場合終わり
        GoTo Prcs_Exit
    End If
 
    Set WrkSht = ThisWorkbook.Sheets("Sheet1")
 
    ' ヘッダーの表示
    For HdrItem = 1 To RcrdSt.Fields.Count
        WrkSht.Cells(1, HdrItem).Value = "'" & RcrdSt.Fields(HdrItem - 1).Name
    Next
 
    ' 結果をそのまま表示
    WrkSht.Cells(2, 1).CopyFromRecordset RcrdSt
 
    RcrdSt.Close
    AdoCnnct.Close
 
Application.ScreenUpdating = True
 
Prcs_Exit:
    On Error Resume Next
 
    ' 後処理
    Set RcrdSt = Nothing
    Set AdoCnnct = Nothing
 
    Exit Sub
Prcs_Error:
    MsgBox "ADO connecting (CSV/TEXT) ERROR：" & Err.Description & "(" & Err.Number & ")" & vbCrLf & CnnctDir, vbCritical
    GoTo Prcs_Exit


End Sub

いいねと思ったら、三つポチっとね！
  
にほんブログ村    にほんブログ村      にほんブログ村