やりたかったこと
こういうcsvに遭遇して、うまくカンマ区切りの形に整形したかったのでメモ。
1000,aaa,abcdef.pdf,aaa@hogehoge.com
2000,bbb,"abcdef.pdf,ghijkl.txt",bbb@hogehoge.com
3000,ccc,"mnopqr.jpg,stuvwx.pdf","ccc@hogehoge.com,ddd@hogehoge.com"
3列目が添付ファイル名、4列目が宛先のようなものだと思ってもらえばいい
処理の概要
正規表現でダブルクオーテーションに囲まれた部分を抽出し、その部分にあるカンマを別の文字に置換することにする。
ただし","
の部分はフィールド区切りなのでカンマだけは残しておく。
オリジナルがUTF-8 BOM付だったのでADODB.Streamを使うことにした。
' 機能
' 一部がダブルクオーテーションされているutf-8 bom csvについて、ダブルクオーテーション内のカンマを除去
' 読み込みファイルの指定 (相対パスなのでこのスクリプトと同じフォルダに置いておくこと)
Dim input
Set input = CreateObject("ADODB.Stream")
' 1:バイナリ・2:テキスト
input.Type = 2
' 文字コード指定
input.Charset = "UTF-8"
' Stream オブジェクトを開く
input.Open
' ファイルを読み込む
input.LoadFromFile "source.csv"
' 書き出しファイルの指定 (今回は新規作成する)
Dim output
Set output = CreateObject("ADODB.Stream")
output.Type = 2
output.Charset = "UTF-8"
output.Open
Dim objRep
' "~"の文字列を取得
Set objRep = New RegExp
' カンマで囲まれた部分。バックスラッシュでエスケープできないのでChr(34)で書く
objRep.Pattern = Chr(34) & ".*" & Chr(34)
objRep.IgnoreCase = True
objRep.Global = True
Dim lineStr
Dim tmpText_original
Dim tmpText_mod
Dim objMatch
' 読み込みファイルから1行ずつ読み込み、書き出しファイルに書き出すのを最終行まで繰り返す
Dim records
Do Until input.EOS
' -1:全行読み込み・-2:一行読み込み
lineStr = input.ReadText(-2)
' 正規表現でマッチ箇所を探す
Set objMatch = objRep.Execute(lineStr)
if objMatch.Count > 0 then
For Each m In objMatch
' バッファーを初期化
newText = ""
' オリジナルの部分を保存
originalText = m.Value
' ","の部分をいったん|にしておく
newText = Replace(m.Value, Chr(34) & "," & Chr(34), "|")
' ダブルクオーテーションを消去
newText = Replace(newText, Chr(34), "")
' カンマを/に置換
newText = Replace(newText, ",", "/")
' 退避した|をカンマに置換
newText = Replace(newText, "|", ",")
' オリジナルの部分と加工後の部分を置換
lineStr = Replace(lineStr, originalText, newText)
Next
end if
' 0:文字列のみ書き込み・1:文字列 + 改行を書き込み
output.WriteText lineStr, 1
Loop
' 書き出しファイルの保存
' 1:指定ファイルがなければ新規作成・2:ファイルがある場合は上書き
output.SaveToFile "result.csv", 2
' Stream を閉じる
input.Close
output.Close
変換結果
こうなる
1000,aaa,abcdef.pdf,aaa@hogehoge.com
2000,bbb,abcdef.pdf/ghijkl.txt,bbb@hogehoge.com
3000,ccc,mnopqr.jpg/stuvwx.pdf,ccc@hogehoge.com/ddd@hogehoge.com