2020/05/29
2023/03/31 追記「MSDASQLでの接続（64ビット版の場合）」

CSVファイルの読み込み

• １．背景
• ２．システムの概要
• ３．手法１（データベースとしてCSVを呼び出す）
• 　３－１．フォームの作成
• 　３－２．フォームのコード
• ４．手法２（StreamオブジェクトでCSVを呼び出す）
• 　４－１．手法２の本体部分
• 　４－２．CSVファイルの１行をカンマで分割する関数
• ５．手法３（FileSystemObjectオブジェクトでCSVを呼び出す）
• ６．手法のまとめ
• ７．最後に

１．背景

オフラインの設備等からの情報は、CSVファイルとして出力されるものが多いようです。これは、CSV（Comma Separated Value　＝カンマで区切った値）であれば幅広い処理機・処理アプリで対応できるという「互換性の高さ」があるからです。
Excelも例外では無く、普通に「ファイルを開く」でCSVを指定すれば開きますし、CSV形式でファイル保存も可能です。

「CSVファイルのVBAでのExcel読み込み」については他の多くのサイトで扱っていますので、今回はVBAで「CSVを読み込む」にはどの様な方法があるか、について軽く紹介したいと思います。

２．システムの概要

CSVファイルのサンプルとして、非常に簡単な図２－１を用意（添付ファイルの it-csv-01.csv）しました。値の区切り（delimiter）としてカンマ式やTAB式などがありますが、今回はカンマとしました。尚、最下行の下に２行ほど空行が入ってます。

図２－１

そのCSVファイルをダイアログのWebBrouserにドラッグ＆ドロップすると、シート上の選択セルを左上とする範囲にデータを書き込むことにしました。このWebBrouserを使う方法は「ファイルの存在有無の確認が不要」であるのと、「データをシート上の任意の位置にダウンロードできる」ことから選びました。
（実際にはデータを配列にし、内部処理する流れになると思います。）

図２－２

ちなみに、Range("B2:H7")は「csvをデータベースに見立ててアクセスする方法」、Range("B10:H15")は「csvを文字の並びとして取り出す方法」による結果です。下側は、セルの左上に緑三角印がついている通り、「文字列」扱いになっています。
この辺の違いについても整理していきます。

３．手法１（データベースとしてCSVを呼び出す）

３－１．フォームの作成

ここではCSVファイルを取得する手段として、フォーム上に「WebBrowserコントロール」を配置し、そこにSCVファイルをドラッグ＆ドロップすることでファイル名を取得させています。
但し、標準状態のツールボックスには「WebBrowser」がありませんので、ツールボックス上のマウス右ボタンで「その他のコントロール」を選択し、一覧の中から「Microsoft Web Browser」を選択します。（図３－１）

図３－１

するとツールボックスに「WebBrowser　印」が追加されますので、それをクリックしフォーム上に「WebBrowserコントロール」を配置します。（図３－２）

図３－２

尚、WebBrowser等のコントロールについては、当サイト内の下記項目も参考にして下さい。
　「画像を直接シートに貼り付ける」
　「あらゆるデータファイルをシートに貼り付ける」
　「回転させた画像をシートに貼り付ける」

３－２．フォームのコード

フォームのWebBrowserにCSVファイルをドロップした時に発生するイベントを使用し、CSVデータを取り出してシートに貼り付けるのが図３－３です。
一番下のサンプルファイル（it-028.xlsx）ではUserForm1の内容になります。

1. '==========　⇩①　データベースとしてCSVを呼び出す（UserForm1）　============
2. Private Sub WebBrowser1_BeforeNavigate2(ByVal pDisp As Object, URL As Variant, Flags As Variant, TargetFrameName As Variant, PostData As Variant, Headers As Variant, Cancel As Boolean)
3. 　Dim cn As Object
4. 　Dim rs As Object
5. 　Dim Path As String　　　 '←URLのファイル名の無いPathを代入する変数
6. 　Dim Fname As String　　　 '←ファイル名を代入する変数
7. 　Dim SQL As String　　　　 '←SQL文の変数
8. 　Dim Temp() As Variant　　 '←CSVの値を入れる配列
10. 　Cancel = True
12. 　Path = Left(URL, InStrRev(URL, "¥"))
13. 　Fname = Mid(URL, InStrRev(URL, "¥") + 1)
14. 　SQL = "Select * from [" & Fname & "] "
16. 　Set cn = CreateObject("ADODB.Connection")
17. 　Set rs = CreateObject("ADODB.Recordset")
19. 　cn.Provider = "Microsoft.ACE.OLEDB.12.0;"
20. 　cn.ConnectionString = "Data Source=" & Path & ";Extended Properties='Text; HDR=Yes';"
22. 　cn.Open
23. 　　rs.Open SQL, cn
24. 　　　ReDim Temp(0 To rs.Fields.Count - 1, 0 To 0)
25. 　　　For i = 0 To rs.Fields.Count - 1
26. 　　　　Temp(i, 0) = rs.Fields(i).Name
27. 　　　Next i
29. 　　　Do Until rs.EOF
30. 　　　　cnt = cnt + 1
31. 　　　　ReDim Preserve Temp(0 To rs.Fields.Count - 1, 0 To cnt)
32. 　　　　For i = 0 To rs.Fields.Count - 1
33. 　　　　　Temp(i, cnt) = rs(i).Value
34. 　　　　　If IsNull(Temp(i, cnt)) Then Temp(i, cnt) = ""
35. 　　　　Next i
36. 　　　　rs.movenext
37. 　　　Loop
39. 　　　Selection.Resize(UBound(Temp, 2) + 1, UBound(Temp, 1) + 1).Value = _
40. 　　　　Application.WorksheetFunction.Transpose(Temp)
42. 　　rs.Close
43. 　cn.Close
44. 　Set rs = Nothing
45. 　Set cn = Nothing
46. End Sub

図３－３

WebBrowserコントロールにドロップされたファイルは、２行目の「BeforeNavigate2イベント」で受け取ります。その引数の内の「URL （As Variant）」でファイル名が取得できます。
３～８行目は、プロシージャ内で使用する変数の宣言です。CSVファイルの値は８行目のTemp配列に格納していきます。
１０行目の「Cancel = True」は、ドロップしたファイル内容をこのWebBrowserで表示させない（Cancel）ようにするものです。

１２～１３行目では、ドロップされたファイルのパス＋ファイル名として受け取った引数URLを、「パス名」と「ファイル名」に分解します。式内の「InStrRev(URL, "¥")」は、「文字列URLに対して¥の字の位置を後ろ側から探す」というものです。
探し当てた「¥」は、「パス名」と「ファイル名」を分けている部分に当たりますので、その位置を基準にして前側をパス名に（１２行目）、後ろ側をファイル名に（１３行目）にしています。

１４行目は、SQL文を作っています。「CSVファイル ＝ データベーステーブル」に見立てていますので、ファイル名（１３行目で作った変数Fname）がテーブルになり、そのテーブルから全てを取り出しますので「Select * From テーブル名」としています。 テーブル名はカギカッコ（ [ ] ）で囲みます。

１６～１７行目で、ADO（ActiveX Data Objects）の接続（ ＝ connection）と読み取り（ ＝ Recordset）のオブジェクトを生成し、１９行目でプロバイダーを、２０行目で接続文字列を設定します。
２０行目の接続文字列の内「Data Source」には、RDB（リレーショナルデータベース）で言うところのデータベースである「パス名（１２行目で作った変数 Path ）」を設定し、「Extended Properties」にはデータベースの種類である「Text」を設定します。

その後ろについている「HDR=Yes」ですが、HDR（Header Label 又は Header）は「１行目をカラム名として扱う」か否かの設定です。Yes（＝扱う）が既定値ですので、省略して「Extended Properties=Text;」でも同じ事になります。
「１行目もデータとして扱いたい」場合には、「HDR=No」として２０行目のようにTextと一緒にシングルクォーテーション（又はダブルクォーテーションを２連）で囲みます。これは両方ともExtended Propertiesの項目だからの様です。

２２行目ではConnectionを開き、２３行目では実行するSQL文と接続しているConnectionオブジェクトを引数にしてrecordsetを開きます。

２４～２７行目では、配列Tempにカラム名を代入しています。

ここまで来ると、２０行目のExtended Properties に「HDR=No」として「１行目もデータと見なして一緒に取得できるのではないか」と思われると思います。しかし、この方法はうまく行きませんでした。
原因は「CSV1行目とCSV２行目以降のデータ型が異なる」ために、CSV２行目以降のデータ型が数値（TypeがadInteger型）の場合にCSV１行目の文字列がNull値になってしまうのです。
例えば今回の場合CSVファイルの１列目と３列目についてはCSV２行目以降は全て数値ですので、そのカラム名である「ＡとＣの文字が表示されない（＝消えてしまう）」ことになります。

以上の理由で、CSVデータの全てが文字列（数値と見なされるものが無い）であったり、カラム名がデータ側（CSV２行目以降）と同じデータ型である場合以外は、「HDR=No　で１行目も一緒に取得」する方法はうまく行きません。
尚、列のデータ型を文字列相当（adVarWChar型）に強制的に変更しようとも試みましたが、こちらもうまく行きませんでした。

一方、カラム名の行が無い（=CSVファイルの１行目からデータ）場合は、注意が必要です。
例えば１行目のデータが「123.567」の様な小数点のある数値の場合、上記のコードでは「123.567をカラム名と見なす」ことになります。すると「123#567」のように「小数点が＃印に置き換わる」現象が発生します。
これではデータになりませんので、「HDR = No」とした上で２５～２７行目のカラム名を配列に代入する代わりに「Temp = rs.GetRows」で全データを取り込む方法が良いと思います。

ですので２４～２７行目で、「わざわざカラム名を取り出して、配列Tempに代入する」作業をする事でCSVファイルの１行目を書き出しています。
コードの内容としては、２４行目のRedimで配列TempのサイズをCSV１行目が入るサイズに変更しています。CSV２行目以降もデータを同じ配列Tempに入れていきます（＝Redimでサイズを大きくしていく）ので、延ばしていく次元を２次元側にしています。行列から見ると縦横がひっくり返っていますので、最後貼り付ける段階（４０行目）で縦横を入れ替えます。

２９～３７行目は、CSV２行目以降のデータ本体部分を配列に入れていきます。
まず３１行目で配列Tempのサイズを１列増やしています。１次元目の「0 To rs.Fields.Count - 1」は２４行目と同じですが、２次元目の「0 To cnt」でDo～Loopが回るたびにcntが１つずつ増えていきます。
配列サイズを広げた後、３２～３５行目のFor～NextをCSVファイルの列数分だけ回し、３３行目で１つずつ配列に代入します。
但し代入した値がNullの場合があります。このNullが配列に存在すると、４０行目の「Transpose関数」で行列を入れ替える時にエラーが発生しますので、３４行目でゼロ文字に置き換えます。

CSVファイルの１行分の処理が終わると、３６行目で「movenext」を実行し、CSVファイルの次の行に移り処理を繰り返します。
CSVファイルの全行の処理が完了すると EOF（End of File）となりますので、２９行目の「Until rs.EOF」でDo～Loopから外れます。

４０行目で配列Tempの行列を入れ替え、それをシート上に貼り付けます。
貼り付ける範囲は貼りつける配列サイズと合っていないとエラーが発生しますので、配列TempのサイズをUbound関数で割り出してResizeで範囲サイズを変更します。
ここで「.Resize(UBound(Temp, 2) + 1, UBound(Temp, 1) + 1)」と、行・列ともに「＋１」しているのは、行列入れ替えをする前の配列Tempですのでインデックスがゼロから始まっているために「＋１」する必要があります。
尚、「Transpose関数」で行列入れ替えをした後は、インデックスは必ず１からになります。

最後に、４２～４３行目で「recordset」「connection」を閉じ、４４～４５行目でオブジェクトを開放します。

得られたシート上の値は、図３－４の様になります。

図３－４

どの列もデータ型が正しく貼り付けられています。但し、１列目は「011」などと「ゼロで埋めて3桁にする」意図でしたが、ゼロは取れてしまい通常の数値に置き換わってしまいました。あらかじめ書式が分かっていれば、貼り付ける前に書式を揃えることが出来るかと思います。


尚、１９～２０行目では、プロバイダとして「Microsoft.ACE.OLEDB.12.0」を使用しましたが、代わりのプロバイダとして「MSDASQL」を使用することも可能です（図３－５）。但しその際には接続ワード（２０～２１行目）も変更になりますので、必ずペアで変更させて下さい。

19. 　cn.Provider = "MSDASQL"
20. 　cn.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" _
21. 　　　　　　　　　　　　　& "DefaultDir=" & Path & ";"

図３－５

また、上で説明した「プロバイダとしてACEを使用した時に、１行目もデータと見なす HDR=No 」の役目は、図３－６の様に「FirstRowHasNames=0」を接続文字列に指定するのですが、バグのために無視されてしまいます。

19. 　cn.Provider = "MSDASQL"
20. 　cn.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" _
21. 　　　　　　　　　　　　　& "DefaultDir=" & Path & ";FirstRowHasNames=0; "

図３－６

よってプロバイダとしてMSDASQLを使用した時は、先頭行は必ずフィールド名として扱われる（＝ CSVの１行目は読み込めない）ことになります。ですので１行目を読み込むためには２５～２７行目が必要となります。


ここで、Excelが使用できるプロバイダの種類（図３－７）と、データベース対象別の接続文字列（図３－８）について下記にまとめました。今回の「CSVファイルをデータベースに見立てる」場合は、図３－８のTXTの部分になります。

OLEDBﾌﾟﾛﾊﾞｲﾀﾞ	ADODB.Connection.Provider=
Ace	"Microsoft.ACE.OLEDB.12.0;"
MSDASQL	"MSDASQL"

図３－７

OLEDBﾌﾟﾛﾊﾞｲﾀﾞ	対象ﾃﾞｰﾀｿｰｽ	ADODB.Connection.ConnectionString =
Ace	Access	"Data Source=[ﾌｧｲﾙﾊﾟｽ];"
	Excel	"Data Source=[ﾌｧｲﾙﾊﾟｽ]; Extended Properties=""Excel 12.0;""
	TXT	"Data Source=[ﾃﾞｨﾚｸﾄﾘﾊﾟｽ];　Extended Properties=Text"
MSDASQL	Access	"Driver=Microsoft Access Driver (*.mdb, *.accdb);DBQ=[ﾌｧｲﾙﾊﾟｽ];"
	Excel	"DSN=Excel Files;DBQ=[ﾌｧｲﾙﾊﾟｽ];"　　または "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=[ﾌｧｲﾙﾊﾟｽ];"
	TXT	"Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=[ﾃﾞｨﾚｸﾄﾘﾊﾟｽ];"

図３－８

[2023.03.31 追記] なお、Excelの64ビット版を使用している場合には、図３－５や図３－６ではエラーが発生します（私のPCではExcelがダウンしてしまいました）。これはDriverとして指定している「Microsoft Text Driver」が、64ビット版には存在しない為です。 他サイトではドライバーを入れる等の対策が紹介されていますが、コントロールパネルの「ODBCデータソースのセットアップ」を確認してみると、図３－９のようになっています。左側が32ビット版、右側が64ビット版です。 図３－９ 図３－５と図３－６で使っていた「Microsoft Text Driver」は、左側の32ビット版には確かにありますが、右側の64ビット版の方には見当たらない事が分かります。その代わりに似ているものとして「Microsoft Access Text Driver」というのがあります。 そこでこの「Microsoft Access Text Driver」をドライバーとして使用したのが図３－１０です。 　cn.Provider = "MSDASQL" 　cn.ConnectionString = "Driver={Microsoft Access Text Driver (*.txt, *.csv)};" _ 　　　　　　　　　　　　　& "DefaultDir=" & Path & ";" 図３－１０ 64ビット版の場合、この図３－１０を使用すると正しく動くことが確認できました。なおカッコ内の「*.txt」と「*.csv」を区切っているのは、32ビット版では「;（セミコロン）」でしたが、64ビット版では「,（カンマ）」になりますので注意が必要です。 なお、32ビット版と64ビット版が混在している環境で同じマクロを使用しなければならない時は、標準モジュール宣言部で「#If Win64 then ～ #Else ～ #End If」を使用してモジュール定数を変更し、流すコードを分岐する等が必要です。 なお、どのPCにも「Microsoft Access Text Driver」が存在するかまでは確認できていません。少なくともOfficeの32ビット版がインストールされている私の古いPCには存在しませんでした。もし当ドライバーが入っていない場合は、当然ながらこの手法は使えません。

なお、このデータベースとしてデータを扱う方法としては、下記項目も参考にして下さい。
「Excelシート上にいDBを作り、SQLを使ってデータを入出力する」
「ExcelシートDBを使った会議室予約システム」

４．手法２（StreamオブジェクトでCSVを呼び出す）

フォームの作成については、３－１項と同じで、WebBrowserコントロールを使用しています。
フォームのコードについて、図４－１で説明していきます。また自作関数mySplitを使って１行のデータを個々のデータに分割していますが、それについては図４－６で説明します。

４－１．手法２の本体部分

47. '==========　⇩②　StreamオブジェクトでCSVデータを呼び出す（UserForm2）　============
48. Private Sub WebBrowser1_BeforeNavigate2(ByVal pDisp As Object, URL As Variant, Flags As Variant, TargetFrameName As Variant, PostData As Variant, Headers As Variant, Cancel As Boolean)
49. 　Dim st As Object　　　　　　　　　　　　 '←Streamオブジェクト
50. 　Dim buf As String　　　　　　　　　　　 '←CSVデータを丸ごと入れる変数
51. 　Dim buf1() As String　　　　　　　　　　 '←CSVデータを改行印で区切る１次元配列
52. 　Dim buf2() As String　　　　　　　　　　 '←CSVデータを更にｶﾝﾏ印で区切る２次元配列
53. 　Dim buf_row As Variant　　　　　　　　　 '←CSVの1行分をｶﾝﾏで分割した１次元配列
54. 　Dim CharS As String　　　　　　　　　　　 '←CSVﾌｧｲﾙの文字コード
55. 　Dim CSV_row As Long, CSV_col As Long　　 '←csvファイルの行数・列数
56. 　Dim i As Long, j As Long 　　　　　　 '←ｶｳﾝﾀｰ変数
57. 　Cancel = True
59. 　Set st = CreateObject("ADODB.Stream")
61. 　CharS = "shift-jis"　　　　　　　 '←csvﾌｧｲﾙの文字コード（UFT-8の場合は "utf-8"に変える）
62. 　st.Charset = CharS
64. 　st.Open
65. 　　st.LoadFromFile URL
66. 　　buf = st.ReadText
67. 　st.Close
69. 　While Right(buf, Len(vbCrLf)) = vbCrLf
70. 　　buf = Left(buf, Len(buf) - Len(vbCrLf))
71. 　Wend
73. 　buf1 = Split(buf, vbCrLf)
74. 　CSV_col = UBound(Split(buf1(0), ","), 1)
75. 　CSV_row = UBound(buf1, 1)
77. 　ReDim buf2(0 To CSV_row, 0 To CSV_col)
79. 　For i = 0 To CSV_row
80. 　buf_row = mySplit(buf1(i), ",")
81. 　　For j = 0 To CSV_col
82. 　　　buf2(i, j) = buf_row(j)
83. 　　Next j
84. 　Next i
85. 　Selection.Areas(1).Item(1).Resize(UBound(buf2, 1) + 1, UBound(buf2, 2) + 1) = buf2
86. 　Set st = Nothing
87. End Sub

図４－１

４９～５６行目は、プロシージャ内で使用する変数宣言です。
この手法２では「Streamオブジェクト」を使用しますので、それ用のオブジェクト変数を４９行目で準備します。

５７行目は、ドロップしたファイル内容をWebBrowse上で表示させないようにします。
５９行目では「Streamオブジェクト」を生成します。

６１行目では変数CharSにCSVファイルの文字コードを文字列として代入し、６２行目でStreamオブジェクトのCharsetプロパティに設定しています。
ここでは文字コードとして「shift-jis」をセットしていますが、対象のCSVファイルが「UTF-8」であるならばその文字列に変更して下さい。
しかし、文字コードが決まっていない（＝色々な文字コードのCSVファイルを扱う）場合には、固定する訳にいきません。文字コードには非常に多くの種類がありますが、残念ながらExcelには判別関数のような便利なものはありません。
そこで、他のサイトで多々紹介されている判別関数のコードをコピーし、その戻り値を６１行目のCharSに代入してもらえれば良いような形にしました。

６４行目のOpenメソッドでストリームオブジェクトを開き、６５行目でドロップしたCSVファイルの内容をストリームに読み込みます。
そのストリームに読み込んだCSVファイルから文字を読み取ります。ReadTextメソッドには読み取る文字数のパラメータを指定できますが、ここでは指定していません（＝既定のAdreadall）ので全文字を読み取り、変数bufに代入します。
読み取りが完了したら、６７行目でストリームを閉じます。

６９～７１行目は、「CSVファイルの末尾の空行を削除」しています。
空行が何行か有るということは「改行（＝vbCrLf）が何個か連なっている」ことになりますので、「ファイルの後ろ側から、改行を切り取る」ことを「一番後ろの改行が無くなるまで」続ければ良い事になります。
そこで今回は、While～Wendを使用しました。このWhileの後ろに続く条件式が成り立っている（True）間は７０行目を実行するものです。
良く使われるDo While～Loop と内容は同じですが、途中で抜け出す方法（Do～Loop ならExit Do ）が無いこともあり、現在ではあまり使われないようです。但し役割だけは覚えておくべきと思い今回使用しました。
なお、Do While～Loop に置き換えると図４－２のようになります。

69. 　Do While Right(buf, Len(vbCrLf)) = vbCrLf
70. 　　buf = Left(buf, Len(buf) - Len(vbCrLf))
71. 　Loop

図４－２

７３行目では、改行マーク（vbCrLf）毎にCSVファイルの文字列を分割し、配列buf1に代入しています。
７４～７５行目は、最終的なCSVファイルの縦横サイズを求めています。７４行目は、CSVファイルの１行目（＝buf1(0)）をカンマで分割し、その列数を算出しています。Split関数で分割したものはインデックスがゼロから始まりますので、この求められたCSV_colに１を足したものが列数になります。（７７行目のReDimはインデックスをゼロから始めていますので、あえて１を足していません。）
７５行目はbuf1（vbCrLfで分割したもの）の最大インデックスから、最終的なCSVファイルの（ゼロから数えた）行数を求めています。

７７行目は、７４～７５行目で求めた最終的なCSVファイルの縦横サイズから、buf2配列の大きさを指示しています。

７９～８４行目では、７７行目でサイズを確定させたbuf2配列にデータを入れていきます。
７９行目では行毎に、８１行目では列毎にループをまわします。
８０行目の右辺は自作関数mySplitです。イメージとしては図４－３の様なもので、行としてまとまったデータ（buf1(i)）と分割する文字列（ここではカンマ）を引数にすると、列数分に分割するものです。

図４－３

これだけだと通常のSplit関数と同じですが「ダブルクォーテーションで囲まれた文字列内のカンマでは分割しない」というのが特徴です。その分割した値は８０行目の左辺でbuf_rowに配列として代入されます。
分割のロジックについては図４－６で説明します。

８２行目では、その列数分に分割した値（buf_row）を１つずつ取り出してbuf2配列にデータを入れていきます。

なお、７９～８４行目を図４－４の様なコードでも結果は同じです。buf_row配列を使用せずに、mySplitの結果を直接buf2配列に代入する方法です。

79. 　For i = 0 To CSV_row
81. 　　For j = 0 To CSV_col
82. 　　　buf2(i, j) = mySplit(buf1(i), ",")(j)
83. 　　Next j
84. 　Next i

図４－４

しかし、８１～８３行目のFor～Nextが回るたびにmySplit関数が呼び出されており、しかも左辺への代入に使用する値は配列の１つだけ、という様に非常に効率が悪い（＝処理時間がかかる）事が分かります。
関数の戻り値を複数回使用する場合は、一度変数・配列に代入することで無駄が省けます。

最後に８５行目で、選択範囲にCSVデータを貼り付けています。
８６行目では５９行目で生成したstオブジェクトを開放しています。

得られたシート上の値は、図４－５の様になります。

図４－５

手法１と異なり、全列が左寄せ（＝文字列の表示）になっています。シート上での計算には支障ありませんが、見栄え的には手を入れる必要があるかもしれません。

４－２．CSVファイルの１行をカンマで分割する関数

図４－１の８０行目で使用した「文字列をカンマで区切る」mySplit関数が、以下（図４－６）です。

88. '====　⇩③　１行データをDelimiterで個々のデータに分割（UserForm2、UserForm3）　=======
89. Function mySplit(buf As String, Delimiter As String) As Variant()　　 '引数：buf=対象文字列　Delimiter＝区切り文字
90. 　Const MOJI As String = """"　　　 '←文字列を囲っている文字(ここでは｢"｣)を指定
91. 　Dim MOJI_count As Boolean　　　　 '←MOJIを数える変数。奇数ならFalse、偶数ならTrue
92. 　Dim SP() As Variant　　　　　　　 '←１行分のデータをｶﾝﾏ印で分割した値を入れる配列
93. 　Dim buf1 As String　　　　　　　　 '←bufから切り出した1文字
94. 　Dim buf2 As String　　　　　　　　 '←切り出した文字をまとめた文字列
95. 　Dim i As Long, cnt As Long　　　　 '←カウンタ変数
97. 　buf = buf & Delimiter
99. 　For i = 1 To Len(buf)
100. 　　buf1 = Mid(buf, i, 1)
101. 　　If buf1 = MOJI Then
102. 　　　MOJI_count = Not MOJI_count
103. 　　ElseIf (buf1 = Delimiter And MOJI_count = False) Then
104. 　　　ReDim Preserve SP(0 To cnt)
105. 　　　cnt = cnt + 1
106. 　　　SP(UBound(SP, 1)) = buf2
107. 　　　buf2 = ""
108. 　　Else
109. 　　　buf2 = buf2 & buf1
110. 　　End If
111. 　Next i
113. 　mySplit = SP
114. End Function

図４－６

このmySplit関数は、２つの引数を受け取ります。第一引数は対象文字列、第二引数は区切り文字です。
文字列を囲う文字は９０行目で「"」に固定していますが、引数にすることも可能です。

９０行目は定数MOJIの設定をしていますが、ダブルクォーテーションが４つ並んでいます。両端のダブルクォーテーションは「文字列を囲む印」です。右から２つ目のダブルクォーテーションが「本当のダブルクォーテーション」で、右から３つ目のダブルクォーテーションは「２つ目のダブルクォーテーションを特殊記号では無く、ただの文字としてのダブルクォーテーションとｓて認識させるためのエスケープ記号」です。

見た目は４連のダブルクォーテーションなので分かりにくいのは確かです。ですのでASCII文字コードを使って表現す方法もあります。（図４－７）

• Const MOJI As String = Chr(34)　　 '←エラーになります。
• 　
• Dim MOJI As String
• MOJI = Chr(34)

図４－７

ダブルクォーテーションのASCII文字コードは34です。と言って、Chr(34)を４連のダブルクォーテーションと入れ替えてもダメです（エラーが出ます）。定数として設定するものは、数値や文字そのものでなくてはならないからです。
ですので変数として宣言し、Chr(34)を変数に代入する事が必要です。

９１～９５行目は、この関数の中で使用する変数宣言です。

９７行目は、引数の対象文字列の最後にDelimiter（区切り文字＝今回はカンマ）を足します。足す理由と共に、この関数の考え方を図４－８で説明します。

図４－８

対象文字列を先頭から１文字１文字調べて行くのですが、と「ダブルクォーテーション」が現れた時に特別な処理をさせます。
まず「ダブルクォーテーション」が現れた時には、Boolean型変数（ここではMOJI_count ）をFalse⇔Trueに切り替えます。変数を宣言した状態ではFalseですので、最初にダブルクォーテーションを見つけたらTrueに変わり、２つ目を見つけたらFalseに変わる、という具合です。

通常の文字の場合は一時保管をしていきますが、「区切り文字」が現れた時にBoolean型変数（MOJI_count ）がFalseであれば一時保管していた文字列を配列に代入し一時保管場所をクリアします。
「区切り文字」が現れてもBoolean型変数（MOJI_count ）がTrueであれば、それは文字列の中（ダブルクォーテーションで囲まれた中）であるので通常の文字扱いで一時保管していきます。

対象文字列が無くなったら配列を呼出し元に返すのですが、「「区切り文字」出現　＋　Boolean型変数＝False」が配列にデータを代入する条件ですので、９７行目のように対象文字列の最後に区切り文字を結合しているのです。
（＝最後の区切り文字が無いと、最終列の値が配列に落ちることなく捨てられる事になります。）

なお、ダブルクォーテーションの出現回数をここではFalse・True切り替えで判別しましたが、他サイトでは「ダブルクォーテーションの数を調べ、２で割った余り」で判断させる方法も紹介されています。
自分の腑に落ちるロジックを使えば良いと思いますし、方法は決して１つでは無いので独自のものを編み出してもらいたいと思います。

９７行目の対象文字列の最後に区切り文字を足す理由は最後列の値を取り出すため、でした。
そのカンマを足した対象文字列を、９９～１１１行目のFor～Nextで１文字ずつ処理します。繰り返し数は文字数分（Len(buf)）です。

まず１００行目で１文字を切り出し、その１文字を１０１行目・１０３行目・１０８行目のIf文で仕分けます。
１０１行目は切り出し文字が「"（ダブルクォーテーション）」であった場合は、「MOJI_count = Not MOJI_count」でMOJI_countのFalse・Trueを逆転させます。宣言段階ではMOJI_countはFalseですので、１回来たらTrue、２回来たらFalseに戻ります。
「"（ダブルクォーテーション）」は文字列を囲んでいるだけですので一時保管もしません。

１０３行目は、切り出し文字がDelimiter（カンマ）で且つ MOJI_count＝False（つまり、ダブルクォーテーションが０回か偶数回）の場合は、値の区切りであると判断され１０４～１０７行目が実行されます。図４－８では「データ区切り」という場所です。
同じカンマが来ていても、MOJI_count＝True（つまり、ダブルクォーテーションが奇数回）の場合は、ダブルクォーテーションで囲われた文字列の中のカンマと判断されるということです。
実行内容としては、１０４行目で配列SPを１つ増やし、１０６行目で配列SPの最大インデックスに溜めておいた文字列buf2を代入します。代入後は、１０７行目で文字列buf2をクリアします。

１０８行目はそれ以外の時の処理です。切り出し文字が値そのもの（値中のカンマも含めて）の場合に１０９行目を実行し、「溜めた値の後ろに切り出し文字を結合していく」という処理をします。
ここで溜めたものが、１０３行目の条件に合致する（値と値を区切るカンマの時）時に配列に格納される事になります。

尚、９７行目で最後にカンマを結合していますが、その最後のカンマの処理（１つの行の中のダブルクォーテーションはゼロ又は偶数のはずなので、MOJI_count＝Falseのはず）で最終列の値を配列に書き出してFor～Next文が終了します。

１１３行目で、値を格納した配列SPを関数の戻り値にして終了します。

５．手法３（FileSystemObjectオブジェクトでCSVを呼び出す）

フォームの作成については、３－１項と同じで、WebBrowserコントロールを使用します。
フォームのコードは図５－１となります。また、自作関数mySplitは図４－６と同じものを使用します。

115. '==========　⇩④　FileSystemObjectオブジェクトでCSVデータを呼び出す（UserForm3）　==========
116. Private Sub WebBrowser1_BeforeNavigate2(ByVal pDisp As Object, URL As Variant, Flags As Variant, TargetFrameName As Variant, PostData As Variant, Headers As Variant, Cancel As Boolean)
117. 　Const ForReading = 1, ForWriting = 2, ForAppending = 8
118. 　　　 '（1=読み込み）　（2=書込み）　（8=ﾌｧｲﾙの最後に追記する）
119. 　Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
120. 　　　 '（-2=ｼｽﾃﾑの規定値で開く）　（-1=Unicodeﾌｧｲﾙとして開く）　（0=ASCIIﾌｧｲﾙとして開く）
121. 　Dim Fso As Object
122. 　Dim OTF As Object
123. 　Dim buf As String
124. 　Dim buf1() As String
125. 　Dim buf2() As String
126. 　Dim buf_row As Variant
127. 　Dim CSV_row As Long, CSV_col As Long　　　　 'csvファイルの行数・列数
128. 　Dim i As Long, j As Long　　　　　　　　　　　 '←ｶｳﾝﾀｰ変数
130. 　Cancel = True
132. 　Set Fso = CreateObject("Scripting.FileSystemObject")
133. 　Set OTF = Fso.OpenTextFile(URL, ForReading, TristateTrue)
135. 　　buf = OTF.ReadAll
136. 　OTF.Close
138. 　While Right$(buf, Len(vbCrLf)) = vbCrLf
139. 　　buf = Left$(buf, Len(buf) - Len(vbCrLf))
140. 　Wend
142. 　buf1 = Split(buf, vbCrLf)
143. 　CSV_col = UBound(Split(buf1(0), ","), 1)
144. 　CSV_row = UBound(buf1, 1)
145. 　ReDim buf2(0 To CSV_row, 0 To CSV_col)
147. 　For i = 0 To CSV_row
148. 　　buf_row = mySplit(buf1(i), ",")
149. 　　For j = 0 To CSV_col
150. 　　　buf2(i, j) = buf_row(j)
151. 　　Next j
152. 　Next i
154. 　Selection.Areas(1).Item(1).Resize(UBound(buf2, 1) + 1, UBound(buf2, 2) + 1) = buf2
155. 　Set OTF = Nothing
156. 　Set Fso = Nothing
157. End Sub

図５－１

１１７～１２０行目は、１３４行目で使用するOpenTextFileメソッドの第二引数・第三引数の値を定数として設定しています。
１２１～１２８行目は変数の宣言です。

１３０行目はCSVファイルをWebBrowserコントロールに表示させないために、Cancel引数にTrue値を代入しています。

１３２行目では、FileSystemObjectオブジェクトを生成し、変数Fsoに代入しています。
１３３行目では、FileSystemObjectオブジェクトのOpenTextFileメソッドでドロップしたファイルを開いています。

OpenTextFileメソッドの第一引数はCSVファイルのパス名＋ファイル名（引数URL）ですが、第二引数は入出力モード（iomode）、第三引数は開くファイルの形式（format）を指定しています。

実はMicrosoftのサイトでは、構文として「object.OpenTextFile (filename, [ iomode, [ create, [ format ]]]) 」と説明があります。
今回使用していないcreate引数は「ファイルが存在しない場合に新しいファイルを作成できるかどうか」でTrue又はFalseを設定するようなのですが、１３４行目でcreate引数を（TrueでもFalseでも）設定すると、なぜか正常なデータになりません。
今のところ、データが異常になる原因は調べ切れていません。なにか掴めましたら報告したいと思います。

１３５行目は、１３３行目で開いたファイルの全てを読み込み、変数bufに代入します。
１３６行目では開いたファイルを閉じます。

１３８行目以降は、手法２と同じため説明を省きます。
また、１５５～１５６行目は生成したオブジェクトを解放しています。
出力される値は、手法２の図４－５と全く同じになります。（CSVデータを取り出すところが違うだけなので当然ですが）

なお、１３３行目の「OpenTextFile」の代わりに「GetFile と OpenAsTextStream」を使っても同様の結果が得られます。
これは、URLで指定した「TEXTファイルを開き､開いたTextSystemオブジェクトを返す」というものです。（図５－２）

133. 　Set OTF = Fso.GetFile(URL).OpenAsTextStream

図５－２

６．手法のまとめ

以上、３つの手法を紹介しましたが、これをまとめてみると図６－１のようになります。

	手法１	手法２	手法３
データの扱い	データベース上のフィールド値	文字列
使用オブジェクト	ADODB.Connection ADODB.Recordset	ADODB. Stream	Scripting. FileSystemObject
各ﾌｨｰﾙﾄﾞの分割	不要	必要（区切り文字、文字列囲み文字による処理要）
対象文字コード	Shift-JISのみ	なんでもOK Charsetで設定	Shift-JISのみ
出力されるデータ型	データを自動認識し 適切な型で出力	文字列
注意点	CSVﾌｧｲﾙの1行目からﾃﾞｰﾀが始まる 場合は、HDR=NOで1行目から ﾃﾞｰﾀとして取得が必要	処理無しの場合は全て文字列で出力される為、表示時に処理要

図６－１

７．最後に

既にCSVファイルが決まっている場合には、それに対応できる方法で解析ソフトを考えるしかありません。
しかしこれから設備を導入する場合、CSVを吐き出す機能を検討しているのであれば、解析側のことも考えてより加工し易いCSVを考えて頂きたいと思います。


CSVファイルの読み込み（it-028.xlsm）
CSVサンプルファイル（it-csv-01.csv）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。