CSVファイルを読み込んでExcelに書き出すときの罠。CSVはExcelじゃないよ！の話。【VBA備忘録】

ズブの素人によるExcel VBA備忘録の(たぶん)第8回です。
例によって個人的な備忘録ですのでそのつもりでお読み下さいね。

どこか間違ってたら教えてくれると嬉しいです。

Contents

CSVファイルをExcelに書き出したい。Excelみたいなものと思ったら負け。
CSVを読み込む鍵は「Line input」。
出力結果に「”　”」が出て邪魔な場合の対処。
CSVファイルのテキストデータをExcelシートに書き出す方法のまとめ。

CSVファイルをExcelに書き出したい。Excelみたいなものと思ったら負け。

請求とか支払い絡みでもなんでもいいんですが、いくつかの取引先からCSVでデータが来るということがあるかと思います。
大体PDFとかとセットで来ることが多いので、今まではPDFを印刷して手作業で処理していました。

しかしせっかくCSVというデータでも送ってくれているので、うまいことExcelに取り込めば処理が楽になってミスも減るのではと思い立った次第でございますよ。

CSVというのは特に何もしなければ規定の読み取りソフトはExcelになっていますよね？ね？
エクセルで開いてみるとわかりますがちゃんと表になって出てきます。

ですので愚かなおじさんは取り掛かった当初はExcelと大体似たようなものだと思ってプログラムを組んでみるわけです。
間違っているのでコードは書きませんがどうやってもエラーで書き出すことはできません。

というのも、エクセルで開いたCSVファイルは一見表になっていますが、これはExcel自体の親切機能でそう見せているだけなんですね。
実際は表でもなんでもなくただのデータの羅列だからエラーが返ってくるわけです。

試しにメモ帳やTeraPadなどの別のソフトで開いてみると表でないことがわかります。

要はもともと↑な感じのデータをExcelと同じように扱って書き出そうとすること自体が間違っているということなんでしょうね。

毒猫

考えが甘い、思慮が浅いよ。
この先もそんな甘い考えで生きてくのか？

三本松

そんな責められるほどのこと！？

じゃあCSVファイルはどういうふうに扱えばシートに書き出せるかということをこれから書いていきますよ。

CSVを読み込む鍵は「Line input」。

上記の画像でわかる通り、CSVファイルは見た感じ行ごとの単位になっています。
その行の中で「,(カンマ)]を使って要素を分けているという構造です。
ですので基本的なやり方としてはCSVファイルの行を取り込み、カンマをトリガーとして列に分けていくことになります。

他にもう1つクエリテーブルなるものを使うやり方もあるらしいのですが、正直調べてもちんぷんかんぷんでしたので本記事では触れません。

まずは対象のCSVファイルを指定。

当然といえば当然ですが読み込みたいファイルがあるのであればそのファイルの場所とファイル名が必要になります。
場所は無難にフルパスを変数に入れておくとエラーが出にくくて書くのが楽です。

Dim PATH As String 　'フルパス用の変数
Dim TGT As String　'ターゲットになるCSVファイル用の変数

PATH = "C:\downloads\"  'CSVファイルの住所　適宜変えてください。
TGT = PATH & "data.csv"  'CSVファイルの住所と名前　適宜変えてください。

別に上の変数化はやらなくてもいいですが、いちいちフルパスとファイル名を書くのが面倒なので変数に格納して進めていきます。

三本松

めんどくさいのを減らすためにプログラミングやってるわけですから。
横着は正義！

CSVファイルを開くのは「Open ～ For Input As #1」

次にCSVファイルを読み込むために「Open」を使って開くのですが、今回は読み込むだけなので後ろに「For Input」をつけてやります。
For～はアクセスモードと言うらしく、Input以外にも用途によっていくつか種類があります。
ここを省略してしまうとランダムアクセスモードになるそうです。
ランダムにアクセスされるとか怖すぎるので読み込みに使うときはFor Inputをちゃんとつけましょう。

三本松

ほんとにランダムにやらかすわけではないですよ、念のため。
固定長のレコード用とかで用途が限定されるらしいです。

毒猫

省略して使用頻度低いモードにされるのもどうかと思う。

三本松

上級者はよく使うんじゃないの？知らんけど。

そして最後の「As #1」ですが、VBAでテキストファイルを扱うときには識別のために番号を振らなければいけないという決まりがあるようです。
今回はもちろん1個目のファイルなので「#1」を振ってやります。

Dim PATH As String 　'フルパス用の変数
Dim TGT As String　'ターゲットになるCSVファイル用の変数

PATH = "C:\downloads\"  'CSVファイルの住所　適宜変えてください。
TGT = PATH & "data.csv"  'CSVファイルの住所と名前　適宜変えてください。

Open TGT For Input As #1

ここまでで対象ファイルにアクセスできました。

「Line input」を使って1行ずつ書き出す。

そして次に「Line input」を使って1行ずつ読み込んでいくのですが、そのやり方がちょっと複雑です。

Dim buf As String 'CSVファイルの1行分を文字列として格納する箱
Dim tmp As Variant　'1行分のデータを仮置する配列
Dim WS As Worksheet　'書き出し先のワークシート
Dim R As Long　'書き出し先のワークシート選択範囲の開始位置を決める数字

Set WS = Workbooks("xxx").Sheets(1) '書き出し先のワークシートをセット　適宜変えてください。
WS.Range("A:Z").clearcontents '前回データがあればそれをクリア　範囲は適宜（ｒｙ

R = 0
Do Until EOF(1) 'CSVファイル内データが終わるまでループ。()内はCSVファイルにさっき振った番号#1の数字
    Line Input #1, buf  'CSVファイルの対象行データを文字列としてbufに格納
    tmp = Split(buf, ",")　'格納した文字列をsplitでカンマ区切りで分割し配列tmpに格納
    WS.Range("A1").Resize(1, UBound(tmp) + 1).Offset(R) = tmp
    '出力先A1セルから起点をR行分下にずらし、そこから縦1行・横データ数分の範囲のセルに配列の中身を代入
    R = R + 1   '選択範囲を1行下に
Loop

わけがわかりませんね。上で使ったデータを参照して1個ずつ見ていきましょう。

まずLine Inputは、1行分のデータを読み込む作業をしてくれます。
Do Until EOF(1)～Loop　があるのでその作業を最終行まで繰り返してくれるわけですね。
この場合だとまず変数bufに「”商品コード”,”商品名”,”ケース入数”,”内容量”,”単価”」という文字列がまるごと格納されます。

次にSplit関数によってその文字列を区切ります。
Split(buf, “,”)だと、長い文字列bufの中身を「,」をトリガーとしてバラバラにしてくれます。
そしてバラバラになった元文字列は「tmp=」によって配列の変数tmpに格納されます。
ここで初めてさっきの長い文字列が「”商品コード”」「”商品名”」「”ケース入数”」「”内容量”」「”単価”」と別れて格納されるんですね。

「WS」はワークシート名を書くのがだるいので変数化しただけなのでいいと思います。
ワークシートの中身を決めるときに頭に「Set」をつけるのを忘れないようにしましょう。

そしてこの１行が最も面倒なのですが、ここでは先ほど格納した行を貼り付けるシート上の範囲を選択するコードになっています。
Resizeは選択範囲を指定する魔法、Uboundは配列内の最大インデックス番号を取得する魔法です。
つまりRange(“A1”).Resize(1, UBound(tmp) + 1)の意味としては、「A1セルを基準に配列のデータ数だけ横に広げて選択しなさいよ」ということになります。

三本松

配列は0からスタートするのでデータの数自体はインデックス番号+1になりますよ。

次の「Offset(R)」は、「セル範囲の起点をR行分下にずらしなさい」という意味です。
1行書き出したら書き出し行を下にずらしてやらないと1行目に延々と上書きしてしまいますからね。
Rの値は最初はゼロ、以後1行書き出すごとに+1されていきますので行ごとに次の行に書き出し位置を移してくれるわけです。
つまり2行目のデータを書き出すときにはA1から1つずらしてA2、3行目であればA3が書き出し位置の基準となります。

これをCSVデータが終わる(EOF)まで繰り返してくれることで、晴れてCSVデータをExcelのシートに書き出すことができました。

…。

とここまで書いていて、「あれ？別にOffsetで起点ずらさなくても変数で行数を直接指定すれば行けるんじゃない？」と気づいてしまったので下のコードを書いてみました。

R = 1
Do Until EOF(1) 'CSVファイル内データが終わるまでループ。()内はCSVファイルにさっき振った番号#1の数字
    Line Input #1, buf  'CSVファイルの対象行データを文字列としてbufに格納
    tmp = Split(buf, ",")  '格納した文字列をsplitでカンマ区切りで分割し配列tmpに格納
    WS.Cells(R, 1).Resize(1, UBound(tmp) + 1) = tmp
    'A,Rセルを起点に、そこから縦1行・横データ数分の範囲のセルに配列の中身を代入
    R = R + 1   '起点セルを1行下に
Loop

これを走らせてみたところ同じように書き出すことができたので、「Offsetとかめんどいな」という場合は起点の行数自体を変数にしてしまうほうがわかりやすいかもしれません。

三本松

私の上司は「Offsetでずらしたほうがわかりやすいよ」と言っていましたがどこがわかりやすいのか今の私にはさっぱりわかりません。

ちなみにCSVデータをシートに書き出すやり方は行ごとだけでなく、配列から各データを個別に書き出す方法や、データを一旦すべて配列に納めて一発で書き出す方法もあるようです。
ただまあ前者は書き出し回数が増えることで重くなるらしいですし、後者は難解すぎてやりながらわけがわからなくなってしまいましたのでこの記事での説明は割愛します。

三本松

作業効率と理解しやすさでちょうどいいところを取るのができる男のやり方です！

毒猫

強気で押せば何とかなると思うなよ？

出力結果に「”　”」が出て邪魔な場合の対処。

上のマクロを走らせた結果が下の図です。

一応表としてデータが書き出されてはいますが、全部のデータがダブルクオーテーションで囲われていて不細工ですし、まずこれでは数値の集計などができません。
というわけで書き出す際に一工夫してこの邪魔なダブルクオーテーションを消していきましょう。

三本松

CSVの段階で””の無いデータだった場合は関係ないので飛ばしてください。

エクセルに書き出してから消すことも可能ですが、書き換えに時間を要してしまうのでカンマで分ける直前の段階で消します。
つまり、

tmp = Split(buf, ",")

「bufの中身をカンマ基準で切り分ける」という上のコードを、「bufの中身から”を排除したものをカンマで切り分ける」というコードに書き換えてやるわけですね。
そう考えると話は簡単で、Split関数の中に「”」を排除する関数を入れ子にしてやればOKということになります。

そして排除するために使う関数は「Replace」です。
Replaceは置き換えの関数ですが、置換後の値を「」(無)にしてやることで単純に排除してくれます。

tmp = Split(Replace(buf,"""",""), ",")

コードはこんな感じになります。

毒猫

「”」が4個並んでたりするのは何？バグ？お前のバグ？

三本松

バグじゃねえわ！

VBA中でのダブルクオーテーション絡みにはざっくりと2つのルールがあります。

文字列として扱う場合はそれをダブルクオーテーションで囲う(「”〇〇”」みたいに)
ダブルクオーテーション自体を文字列として扱う際にはニコイチ。(「””」と記すと「”」という文字列として認識する)

というものです。

つまり上の4連ダブルクオーテーションのうち、1個目と4個目の「”」が文字列を囲う記号としてのダブルクオーテーションで、2個目と3個目の「”」がセットで1文字分の文字列「”」を表すことになります。バグではありません。

4連ダブルクオーテーションのあとに出てくる2連ダブルクオーテーションについては、これは両方とも文字列を囲う記号としての「”」で、「“”の中身が何もない(0文字の)文字列」ということを表しています。

このコンボでCSVデータ内の「”」を消し去るという結果が出てくるわけです。

三本松

ややこしいですが落ち着いて考えるればいずれわかります。
てか慣れます。

CSVファイルのテキストデータをExcelシートに書き出す方法のまとめ。

ここまでのコードをまとめると、

Dim PATH As String 　'フルパス用の変数
Dim TGT As String　'ターゲットになるCSVファイル用の変数

PATH = "C:\downloads\"  'CSVファイルの住所　適宜変えてください。
TGT = PATH & "data.csv"  'CSVファイルの住所と名前　適宜変えてください。

Open TGT For Input As #1

Dim buf As String 'CSVファイルの1行分を文字列として格納する箱
Dim tmp As Variant　'1行分のデータを仮置する配列
Dim WS As Worksheet　'書き出し先のワークシート
Dim R As Long　'書き出し先のワークシート選択範囲の開始位置を決める数字

Set WS = Workbooks("xxx").Sheets(1) '書き出し先のワークシートをセット　適宜変えてください。
WS.Range("A:Z").clearcontents '前回データがあればそれをクリア　範囲は適宜（ｒｙ

R = 1
Do Until EOF(1) 'CSVファイル内データが終わるまでループ。()内はCSVファイルにさっき振った番号#1の数字
    Line Input #1, buf  'CSVファイルの対象行データを文字列としてbufに格納
    tmp = Split(Replace(buf,"""",""), ",")  
　　'格納した文字列の「"」を消した上で、splitでカンマ区切りで分割し配列tmpに格納
    WS.Cells(R, 1).Resize(1, UBound(tmp) + 1) = tmp
    'A,Rセルを起点に、そこから縦1行・横データ数分の範囲のセルに配列の中身を代入
    R = R + 1   '起点セルを1行下に
Loop

Set WS = nothing 'ワークシート名の破棄

最後の1行は、オブジェクト変数の内容を破棄する命令です。
何かSetで決めたオブジェクト変数は最後にNothingで破棄しておかないと悪さをすることがあるんだそうです。

三本松

「このマクロ走らせた後はExcelとか全部閉じるよ！」って場合は勝手に破棄してくれるので大丈夫ですが、まあたった1行なんで書いといたほうが安心です。

というわけでCSVのデータをExcelのシートに書き出すときのポイントは以下の通りです。