CSVからデータを選んで取り込む − GoGoマクロ!のWebサイト

HOME > レッスン > CSVデータ取込


ここでは、CSVファイルを効率よくExcelへ取り込むマクロの作り方について勉強していきます。

CSV形式ファイルのデータというのは、いわゆるカンマ区切り(データとデータの 間をカンマ記号の , で区切っているもの)であるということは皆さんよく ご承知かと思いますが、

Excelの場合、そのCSV形式のファイルからデータを取り込むというのは 至って簡単にできますから、大量データの受け渡しにはこのCSV形式のファイルが よく使われると思います。

大概の大企業であれば、本社のサーバー上から落としてきた CSVのデータを各部門にてエクセルに取り込んで、それを自部門に必要な データ加工を施して利用している・・・、といった業務の流れが定番となっている かと思います。

でも、そうやってエクセルに取り込んで実際に使うデータというのはどうせ その中のごく一部に過ぎない・・・、ということが多いのだろうと思いますので、

今回は、表題の「選んで取り込む」(ある文字列を含む行だけを抜き出したい) という所を、今回まず手始めにやっていきます。


それでは、 まずは今回のレッスンで用いるCSVデータの準備から行います。


0)
ネット上から下記にあるCSVデータ(全国の郵便番号のデータ) を、下記の手順で自分のPCにダウンロードしてください。
────────────────────────────+
1.まずは次のURLにアクセスして、郵便局のホームページを
 開いてください。  郵便局のホームページ→
 http://www.post.japanpost.jp/zipcode/dl/oogaki-zip.html

2.このページの中ほどに、「北海道」から始まる「都道府県一覧」
 の中の最後に「全国一括」というリンクがあると思いますので、
 そちらをクリックしてそのデータをダウンロードしてください。

 注)このデータは11MB程度のファイルなので光回線であれば
   一瞬で落とせるものですが、もし遅い回線をご利用の方は、
   どこか都道府県別の(自分の県名等の)1つのファイル
   でも構いません。ただし、以降の解説は「全国一括」を
   用いた場合にて行いますので、その点はご了承の上、以降
   進めてください。

3.なお、このファイルはZIP形式の圧縮ファイル「ken_all.zip」
 となっていますので、通常やっているやり方で(「ファイルを
 すべて展開する」ボタンを押す、又は解凍ソフトを使うなど
 して)適当な場所に解凍してください。

4.最後にいま解凍したCSVファイルを普通にエクセルで開いて
(ファイル名"KEN_ALL.CSV"をダブルクリックして)中身を
 確認してみてください。

(データ量が多いので開くのに10〜20秒程度掛かるかと思い
 ますが少々気長にお待ちください。)
 

5.開いたら、スクロールをして北海道から沖縄県の八重山まで
 12万3721件の全国郵便番号の一覧データが確認できれば
 データの準備はOKです。
────────────────────────────+

CSVを扱う場合には、なるべく大量のデータでやるのが実際に近い臨場感 あるレッスンができる思いますので、今回のレッスンでは、以降このCSV データを使って行っていくことにします。


1)
では、データの準備ができたところで今度はプログラムの方を準備します。
────────────────────────────+
1.まずは、いま開いた郵便番号データのファイルを一旦
 閉じてください。

2.エクセルを起動させます。(空のエクセルを立ち上げてください)

3.今回はまず、ここでプログラム用の画面を表示させます。
 [開発]タブの[コード]で [Visual Basic]をクリック。

4.そうしたら、この画面の上方のメニューで、[挿入(I)] →
 [標準モジュール(M)] とやって右側に白い画面を表示させ、
 そこへ以下のプログラムをコピーして、この全部を
 貼り付けてください。

Sub Macro1()
  Dim a(256) As String
  Open "csvファイルのフルパス名" For Input As #5
  j = 1
  Do While Not EOF(5)
    Line Input #5, buf
    If buf Like "*富士見*" Then
      Call wReadCsv(buf, a, n)
      For i = 1 To n
        Cells(j, i).Select
        ActiveCell.FormulaR1C1 = a(i)
      Next i
      j = j + 1
    End If
  Loop
  Close #5
  MsgBox j - 1 & "件のデータを取り込みました。"
  Range("A1").Select
End Sub

Sub wReadCsv(ByVal bufbuf As String, aa() As String, num)
  Dim char1 As String
  num = 1
  aa(num) = ""
  For n = 1 To Len(bufbuf)
    char1 = Mid(bufbuf, n, 1)
    Select Case char1
      Case ","
        num = num + 1
        If num > 20 Then Exit For
        aa(num) = ""
      Case Chr(34)
      Case Else
        aa(num) = aa(num) & char1
    End Select
  Next n

End Sub
────────────────────────────+

今回少々面倒でしたが、これで下準備は完了です。(今回はCSVデータを 扱うということなので少々の準備を要した点はご勘弁ください。)

いま貼り付けたこのプログラムの、まず下の方(Sub wReadCsv)は、ブラック ボックスです。ここでは、CSVのカンマ区切りでデータを一つ一つに分ける という処理をしていますが、これは中身のわからないもの(理解する必要のな いもの)という意味で、いわゆる一般用語でのそれと同じ意味です。

この中身を気にすると余計な時間が掛ってしまうだけなので、基本、これは ブラックボックスだと捕らえていただいて特に詳しい説明は割愛します。

ただ一箇所だけ、CSVデータでよくお尻に無駄なカンマがいっぱい付いて いる場合がある(,,,,,,,みたいな)かと思いますので、このコード中ほどの

  If num > 20 Then Exit For

という部分がそのストッパーの役目をしています。この場合だと、カンマの数 は最大20個までに制限をしています。つまり、Excelで開いた場合の20番目の T列までが有効になる。)ので、必要のある時(列の長いデータの場合)は、 ここの数字の部分だけ直して使ってください。

その上の Macro1 の方は、以降でいじりながら今後に解説していきます。


2)
それでは、ここでいつものようにマクロの記録を使ってちょっとしたプログラム作りから入っていきます。
────────────────────────────+
1.まず、エクセルの画面に戻ってください。

2.マクロの記録を開始します。

 [開発]タブの[コード]で [マクロの記録] をクリック →
「マクロの記録」画面が出るので、そのまま[OK]をクリック。

3.ここで、先ほどダウンロードして保存したファイル
(デフォルト名"KEN_ALL.CSV")を下記の操作にて開きます。

 「ファイル」→ [開く] → [ファイルを開く]のダイアログで
 左側のフォルダリストから先のCSVファイルのあるフォルダ
 を選び、下のファイル名入力窓の右側にある[ファイルの種類]
 を選ぶ為のプルダウン▼で、一番上の「全てのファイル(*.*)」
 を選んで、先ほどのCSVファイル"KEN_ALL.CSV"を開く。

 注)
 ここでは、必ず上記の操作にてファイルを開いてください。
 フォルダからファイル名をダブルクリックして開くとマクロ
 が記録されませんので、ご注意ください。

4.最後に、[開発]タブの[コード]で [■記録終了] をクリックして、
 マクロの記録を終了させます。
────────────────────────────+


3)
では、いま作ったプログラムの中身を見てみます。
────────────────────────────+
1.再びプログラムの画面を表示させます。
 [開発]タブの[コード]で [Visual Basic]をクリック。

2.この画面の左上半分の[−標準モジュール]という所の下の
 [Module2]の方をダブルクリックします。
────────────────────────────+


++++++++++++++++++++++++++++─
Sub Macro2()
'
  ChDir "C:\Users\santa\Desktop"
  Workbooks.Open Filename:="C:\Users\Santa\Desktop\KEN_ALL.CSV"
End Sub
++++++++++++++++++++++++++++─

※上記プログラム中のフォルダ名の部分(パス名を示す C:\Users\・・・ の部分)は各個人のPC環境やファイルを保存した場所によって異なります ので、上記は私のPCのデスクトップにファイルがある場合の例となってい ます。

で、これは紛れもなく最初にダウンロードした郵便番号一覧データのCSV ファイルをオーソドックスにエクセルで開くためのVBA構文となっている わけですが、これだと全部のデータがエクセルに表示されてしまうだけにな るので、以降にプログラムを改修していきます。


4)
それでは、先に準備しておいたプログラムにちょっと手を加えます。
────────────────────────────+
1.まず、いま記録した Macro2 の中からパス名とファイル名の
 部分( Workbooks.Open の行の後半の " から " までを)
 コピーしてください。

2.そしたら、この画面の左上半分の[−標準モジュール]という所
 の下の[Module1]の方をダブルクリックして Macro1 を表示し、

その Open 行にある "csvファイルのフルパス名" という所に
 そのまま置き換える形で貼り付けして(上書きして)ください。
────────────────────────────+

修正した後のプログラム(Macro1)は下記の様になります。

++++++++++++++++++++++++++++─
Sub Macro1()
  Dim a(256) As String
  Open "C:\Users\Santa\Desktop\KEN_ALL.CSV" For Input As #5
  j = 1
  Do While Not EOF(5)
     ・
     ・
     ・
  (以下省略)
++++++++++++++++++++++++++++─

この解説は後でします。


5)
それでは早速、実行してみましょう。
────────────────────────────+
1.エクセルの画面に戻ってください。

2.まず、いま開いているCSVのファイルの方を
 (右上の×を押して)閉じておいてください。

3.では、実行します。
 [開発]タブの[コード]で [マクロ] をクリック → 「マクロ」
 画面が出るので、そのまま(Macro1が選ばれた状態で)[実行]
 をクリック。

4.しばらく待つと、「○○件のデータを取り込みました。」
 とのポップアップ画面が出ますので、その画面の[OK]
 ボタンを押して終了させます。
────────────────────────────+

この結果、シート上に日本中で"富士見"という名称の付いた地名の郵便番号 の一覧が表示されたらOKということになります。これはトータルで159件 ほどあるかと思います。

この結果を見ると、北海道から九州の大分県まで実に多くの富士見(富士山 の見える場所?)の地名が日本中にあることが分かりますね。

で、当然ですが抽出条件がこのままでは(固定文字の"富士見"限定では・・) 意味がありませんので、次回はこれをどんな地名でも抽出ができるような プログラムへ 拡張してみたいと思います。


今回はこれで終了です。


今日作ったマクロプログラムは次回も この続き でまた使いますので、 ファイルの種類を必ず「マクロ有効ブック」にて、大切に保管して おいてください。(ファイル名:gogo197.xlsm)


NEXT >>
CSVファイルからの連続取り込み
VBA高速化ランキング【CSVデータ取込】

マクロ教材のご案内 >>
VBA恐怖症な人に最適な『三太郎式マクロ』(フルセットの本格CD教材です。)



 

▼一から自分で作れるマクロ講座の登録はこちら((無料))  まぐまぐ
Go!Go! エクセルマクロをはじめよう! (マガジンID:0000135169)   
メルマガ登録
  メールアドレス:
 

読者数1万人達成しました!

エクセルで 最大級の
メールマガジン。
まぐまぐ殿堂入り

最新号は 今すぐ ↓↓↓ 登録

メールアドレス:

Powered by
まぐまぐ

完全まぐまぐ配信なので登録安心です!


エクセルマクロの検定のページ