ExcelVBAを使ったCSVデータの読み込み まとめ①

Excel
スポンサーリンク

今回はVBAを使用したテキスト形式CSVデータの一括読み込み方法について備忘録的に紹介していきたいと思います。

CSVデータをExcelに読み込みたい場合、単純にCSVデータをダブルクリックすればExcelアプリケーションが自動的に起動して、Excelデータとして開くことができると思います。
しかし、その場合のよくあるトラブルとしては、以下になります。

  1. 文字化け
  2. 同一セルに複数のデータが入る(本来は1セル1データにしたいところ)
  3. 表示形式のトラブル(0001が1になったり)
  4. データ自体にダブルクォーテーション、カンマが入っていることによるデータ整形の必要性

などではないでしょうか。。。。

そういった場合は、VBAの使用、エクセルボタン機能(データの取得と変換、パワークエリなど)が考えられます。
上記のトラブル回避には、それなりのスキルが必要となってきます。

今回の記事では、ひとまず、VBAを活用した基本的なCSVデータの読み込み方法から文字化け対策CSV複数データ一括読込を紹介していきたいと思います。

関連記事はこちら

ExcelVBAを使ったCSVデータの読み込み まとめ②
ファイルシステムオブジェクトのText Streamオブジェクトを使った方法を紹介します。
ExcelVBAを使ったCSVデータの読み込み まとめ③
CSVテキストファイルを文字コードを指定して読み込む方法と各ファイルの文字コードを自動判定して読み込む方法を紹介しています。
スポンサーリンク

SHIFT-JIS(ANSI)カンマ区切りテキストファイル3つを一括してエクセルに読み込む

SHIFT-JIS(ANSI)カンマ区切りテキストファイルの3ファイルを一括してエクセルに読み込んでみたいと思います。(インプット先のエクセルと同一フォルダ内の「うまい棒一覧データ」を読み込みます。)

フォルダ内ファイル
CSVファイルのデータ内容

3つテキストファイルには上記のように日付ごとのデータが入っています。

Excelブックへの取り込み

大まかなコードの流れ(方法)

  1. Dir関数を使用して3つのファイルを順番に取得
  2. Openステートメント、Line Input #ステートメントで受け取ったファイルを1行ずつ読み込む

Sub CSV読み込み()

① Dim file, file2 As String
② Dim buf As String
③ Dim arrybuf As Variant
④ Dim flag As Boolean
⑤ Dim i As Integer

⑥ i = 2

⑦ file = Dir(ThisWorkbook.Path & "\*.txt") '読込先エクセルと同一フォルダのテキストファイルのデータを取得

⑧ Do While file <> ""

⑨ file2 = ThisWorkbook.Path & "\" & file 'フルパスを変数に格納

⑩ Open file2 For Input As #1 'ファイルをインプットモードで#1に入れる

⑪ flag = True 'タイトル行を除くためフラグを立てる

⑫ Do Until EOF(1) '最終行まで行ったらループを抜ける

⑬ Line Input #1, buf '1行をbuf変数にいれる

⑭ arrybuf = Split(buf, ",") '1行分のデータを配列に格納

⑮   If flag Then 'フラグがTRUEの場合
⑯   flag = False 'フラグをフォルスにする(タイトル行をスキップするため)
⑰   Else
⑱   Sheet1.Cells(i, 1).Resize(, 4).Value = arrybuf '2行目以降をエクセルに入れていく
⑲   i = i + 1
⑳   End If

㉑  Loop '次の行にいくためのループ

㉒  Close #1 'ファイルを閉じる

㉓ file = Dir() '次のファイルに移行

㉔ Loop '次のファイルに行くためのループ

㉕ End Sub


Dir関数(ファイルが存在するかどうかを判定する関数でファイル名を返します。)


⑦ file = Dir(ThisWorkbook.Path & "\*.txt") '読込先エクセルと同一フォルダ内のテキストファイルのデータを取得

【書式】
=Dir(ファイル名やフォルダ名の文字列式、取得するファイルの属性

【属性】内容
vbNormal0標準ファイル
vbHidden2隠しファイル
vbSystem4システムファイル
vbVolume8ボリュームラベル
vbDirectory16フォルダ

文字列式をワイルドカード(アスタリスク)にすることで、このフォルダの中のファイル名を返すということになります。今回の場合は文字列式部分が、

ThisWorkbook.Path & "\*.txt"

フォルダ内のテキストファイルすべてを返すという意味になります。

(フルパスを入力しますが、返り値はあくまでファイル名です。)

Openステートメント、Line Input #ステートメント

読み込みたいファイルを指定して、1行ずつ読み込みます。


⑩ Open file2 For Input As #1 'ファイルをインプットモードで#1に入れる

open ファイルフルパス for 開き方 as #〇

#については、ファイルをファイル番号#(ナンバー1~511)に入れておくというイメージです。

(1~511の番号を指定します。)そうすることで以降#ナンバーをファイルとみなしてコードが進んいきます。

今回は読み込みなので、開き方=inputとします。主な開き方は以下のなります。

Input入力モード読み込み
Output出力モード書き込み
Append追加モード書き込み

Outputモード・Appendモードはエクセルデータをテキストファイルなどにに書き出す場合に使います。Outputは書き出し先が上書きされ、Appendについては追記になります。

InputやOutputなどエクセル側から見た表現ですね。

Line Input #ステートメント


⑬ Line Input #1, buf '1行をbuf変数にいれる

Line Input #〇,buf

#〇(ファイルパス)の中のデータの1行を変数(buf)に入れます。

その他のコード解説


⑭ arrybuf = Split(buf, ",") '1行分のデータを配列に格納

先ほど、1行分のデータをbuf変数に入れましたが、このままではエクセルのセル1つに1行すべてのデータが入ってしまうので、配列変数に入れ直します。


⑱ Sheet1.Cells(i, 1).Resize(, 4).Value = arrybuf 

1行のデータの数のセル数(4列分)を指定して、配列変数のデータをセルに入れます。

タイトル行(1行目)除外の動作

タイトル行(1行目)はエクセルへ入力しないように、各ファイル2行目から入るようにします。

下記はタイトル行取得時のコードの動作です。

Line Input #1, buf 'タイトル行が変数に入ります。

arrybuf = Split(buf, ",") 'タイトル行のデータを配列に格納

    If flag Then 'flagはTrueなので、flagはフォルスになるのみで、次の行の読み込みループに移行します。
    flag = False 'フラグをフォルスにする(タイトル行をスキップするため)
    Else

1行分のデータ数の可変

データの可変

上記のようにデータによっては1行あたりのデータが4つと限らない場合があります。4月1日、4月6日には「よっちゃんいか」が入っていますね。


 Sheet1.Cells(i, 1).Resize(, UBound(arrybuf) + 1).Value = arrybuf

Resizeプロパティの列数にUBound関数を入れることで対応できます。
UBound関数は配列の最大インデックスを返します。つまり、1行ごとに配列データを作っているので、1行ごとの最大インデックスを返してくれます。(+1にしているのは、配列のインデックスは0から始まるので1をプラスしています。)

Excel読込可変対応

まとめ

CSV読込のためのOpenステートメント、Line Input #ステートメントを使ったコードを紹介しました。もっとも基本的なコードだと思います。

次回は、ファイルシステムオブジェクトText Streamオブジェクトを使った方法を紹介します。

ExcelVBAを使ったCSVデータの読み込み まとめ②
ファイルシステムオブジェクトのText Streamオブジェクトを使った方法を紹介します。

コメント

タイトルとURLをコピーしました