【Excel VBA】カンマ区切りのテキストデータ（CSV）を取り込む方法！設定だけで即実行！

Excel VBAでカンマ区切りのテキストデータ（CSV）を取り込みたいときはないでしょうか。

けど、そんな中で悩むことは、

・Excel VBAでカンマ区切りのテキストデータ（CSV）を取り込むやり方がわからない。
・Excel VBAでカンマ区切りのテキストデータ（CSV）を取り込む設定が複雑でわからない。

ですよね。

今回はそんなお悩みを解決する、

・Excel VBAでカンマ区切りのテキストデータ（CSV）を取り込む方法

についてまとめます！

カンマ区切りのテキストデータ（CSV形式）を取り込む基本操作

Excel2016からCSVファイルがExcelへ取り込むことがより簡単になりました。

その場でサクッと取り込みたい場合はこちらの標準機能を使うととても便利ですので、ぜひ覚えておきましょう。

基本操作の手順

カンマ区切りのデータを作成し、任意の箇所へCSVファイルとして保存します。

空のExcelブックを作成します。

「データ」タブを展開し、「テキストまたはCSVから」ボタンをクリックします。

先ほど作成したCSVファイルを選択します。

CSVデータが表変換され、プレビューされますので、問題なければ「データを読み込む」ボタンをクリックし取り込みします。

はい、できましたね。

カンマ区切りのテキストデータ（CSV形式）を取り込むVBA-PowerQueryを使用

これまでVBAを利用して、Excelへカンマ区切りのテキストデータを取り込む際は、OPENメソッドでファイルを開き、1行づつSplitで区切ってセルへ代入する方式が主流でしたが、動作が重いことが難点でした。

Excel2016からPowerQueryを使ったCSVファイルの読み込みが標準で用意され効率よく処理することができるようになりましたので、VBAでも同じくPowerQueryを使ってカンマ区切りのテキストデータをExcelへ取り込む処理をしていきたいと思います。

早速ですが、サンプルコードは以下の通りです。
実行結果は基本操作ものと同様になります。

Sub CSVファイルからデータを取り込む_PowerQuery版()

Dim strTableName As String
Dim strFilePass As String
Dim strSplitword As String
Dim strStartCell As String
Dim intColumnum As Integer
Dim strColumn As String


'表の名前を指定します。名前は任意で結構です。
strTableName = "hyou1"

'CSVファイルのパスを指定します。
strFilePass = "F:\test\data.csv"

'データの区切り文字を指定します。
strSplitword = ","

'表挿入位置を指定します。左上の支点となるセルを指定します。
strStartCell = "C2"

'Column（列）の数と列のデータ型を指定します。CSVファイルのデータ構成にあわせてください。※"Column*"は変更しないでください。
intColumnum = "4"
strColumn = "{{""Column1"", Int64.Type}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type date}}"

'Column名を変更する場合は各Columnごとに変更後の値を指定します。変更したくない場合は対象Columnの設定を削除、すべて変更しない場合は"{}"を設定してください。
strChangeColumn = "{}"

Set objWorkbook = ThisWorkbook

'前回作成したクエリを削除します。
For Each Query In objWorkbook.Queries
    Query.Delete
Next

'指定範囲にある表を削除します。範囲指定は適宜変更してください。
Range("A1:H10").ClearContents

'CSVファイルのデータを読み込みクエリを設定します。
objWorkbook.Queries.Add Name:=strTableName, Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    ソース = Csv.Document(File.Contents(""" & strFilePass & """),[Delimiter=""" & strSplitword & """, Columns=" & intColumnum & ", Encoding=932, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    変更された型 = Table.TransformColumnTypes(ソース," & strColumn & ")," & Chr(13) & "" & Chr(10) & "    #""名前が変更された列 "" = Table.RenameColumns(変更された型," & strChangeColumn & ")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""名前が変更された列 """


With objWorkbook.ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & strTableName & """;Extended Properties=""""" _
    , Destination:=Range("" & strStartCell & "")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [" & strTableName & "]")
    .PreserveColumnInfo = True
    .ListObject.DisplayName = strTableName
    .Refresh BackgroundQuery:=False
End With


End Sub

なお、設定箇所が多数ありますので、順に説明をします。

表の名前を指定する

表の名前を指定します。名前は任意で結構です。

strTableName = “hyou1”

CSVファイルのパスを指定

CSVファイルのパスを指定します。

strFilePass = “F:\test\data.csv”

データの区切り文字を指定

データの区切り文字を指定します。今回はカンマ区切りですので、”,”を設定します。

strSplitword = “,”

表挿入位置を指定

表挿入位置を指定します。左上の支点となるセルを指定します。サンプルでは”A1″を指定しています。

strStartCell = “A1”

タカヒロ

例えばC2と指定すると図のような位置になります。

Column（列）の数と列のデータ型を指定

Column（列）の数と列のデータ型を指定します。
CSVファイルのデータ構成にあわせてください。なお”Column1～4″となっているカラム名は固定で、別のカラム名を指定すると読み込み時に「Columnが見つからない」とエラーが発生しますので、カラム名は変更しないでください。

タカヒロ

カラム（列）を追加する場合の手順は以下「カラム（列）を追加する」の章をご参照ください。

列のデータ型はデータの形式に合わせて指定してください。
代表的なデータ型は以下の通りです。

・数値：Int64.Type
・テキスト： type text
・日付：type date

VBAを実行する

設定が完了したらさっそくVBAを実行してみましょう。

はい、カンマ区切りのテキストデータが読み込まれていることが確認できますね。

次は列を増やしたり、カラム名を変更したりカスタムをしてみましょう。

カラム（列）を追加する

カラム（列）を追加する場合の手順について説明します。

まずは元データとなるCSVファイルへカンマと値を追加します。
サンプルでは右端に都道府県の値を追加しています。

次にVBA側の設定を変更していきます。

カラム（列）が追加されたので4から5へ値を変更します。

intColumnum = “5”

追加カラムのカラム名を”Column5″にし、データ型は文字列を示す「type text」を指定します。

strColumn = “{{“”Column1″”, Int64.Type}, {“”Column2″”, type text}, {“”Column3″”, type text}, {“”Column4″”, type date}}”

設定を終えたらVBAを実行してみましょう。

はい、カラム（列）が追加され、元データの値も挿入されましたね。

カラム名を変更する。

デフォルトのカラム名は”Column*”となりますが、なんの列かわかりづらいですね。
こちらを指定するカラム名に変更していきます。

サンプルでは「項番」、「商品名」、「単価」、「購入日」、「産地（追加）」という項目名にするため
strChangeColumn = “{}”を以下へ入れ替えます。

strChangeColumn = “{{“”Column1″”, “”項番””}, {“”Column2″”, “”商品名””}, {“”Column3″”, “”単価””}, {“”Column4″”, “”購入日””}, {“”Column5″”, “”産地（追加）””}}”

コピペで入れ替え実行をしてみましょう。

はい、カラム名が標準から指定の値へ変更されました。

データの区切り文字を変更する

カンマ区切りのデータのほかに別の文字の指定も可能です。

strSplitword = “,”

コロン”:”やタブ、スペースなどの指定が可能です。
なおタブの場合は直接値に設定はできませんので、Chr(9)を指定するようにしてください。

‘タブ区切り
strSplitword = Chr(9)
‘コロン区切り
strSplitword = “:”
‘セミコロン区切り
strSplitword = “;”
‘スペース区切り
strSplitword = ” “

タカヒロ

ここまで追加設定をしたVBAは以下のようになります。
ご参考ください。

Sub CSVファイルからデータを取り込む_PowerQuery版_追加()

Dim strTableName As String
Dim strFilePass As String
Dim strSplitword As String
Dim strStartCell As String
Dim intColumnum As Integer
Dim strColumn As String



'表の名前を指定します。名前は任意で結構です。
strTableName = "hyou1"

'CSVファイルのパスを指定します。
strFilePass = "F:\test\data.csv"

'データの区切り文字を指定します。
strSplitword = ","

'タブ区切り
'strSplitword = Chr(9)
'コロン区切り
'strSplitword = ":"
'セミコロン区切り
'strSplitword = ";"
'スペース区切り
'strSplitword = " "



'表挿入位置を指定します。左上の支点となるセルを指定します。
strStartCell = "A1"

'Column（列）の数とColumnタイプを設定します。CSVファイルのデータ構成にあわせてください。※"Column*"は変更しないでください。
intColumnum = "5"
'strColumn = "{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}}"
strColumn = "{{""Column1"", Int64.Type}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type date}, {""Column5"",  type text}}"

'Column名を変更する場合は各Columnごとに変更後の値を指定します。変更したくない場合は対象Columnの設定を削除、すべて変更しない場合は"{}"を設定してください。
strChangeColumn = "{{""Column1"", ""項番""}, {""Column2"", ""商品名""}, {""Column3"", ""単価""}, {""Column4"", ""購入日""}, {""Column5"", ""産地（追加）""}}"
'strChangeColumn = "{}"

Set objWorkbook = ThisWorkbook


'前回作成したクエリを削除します。
For Each Query In objWorkbook.Queries
    Query.Delete
Next

'指定範囲にある表を削除します。範囲指定は適宜変更してください。
Range("A1:H10").ClearContents


'CSVファイルのデータを読み込みクエリを設定します。
objWorkbook.Queries.Add Name:=strTableName, Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    ソース = Csv.Document(File.Contents(""" & strFilePass & """),[Delimiter=""" & strSplitword & """, Columns=" & intColumnum & ", Encoding=932, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    変更された型 = Table.TransformColumnTypes(ソース," & strColumn & ")," & Chr(13) & "" & Chr(10) & "    #""名前が変更された列 "" = Table.RenameColumns(変更された型," & strChangeColumn & ")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""名前が変更された列 """


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & strTableName & """;Extended Properties=""""" _
    , Destination:=Range("" & strStartCell & "")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [" & strTableName & "]")
    .PreserveColumnInfo = True
    .ListObject.DisplayName = strTableName
    .Refresh BackgroundQuery:=False
End With


'"let" & Chr(13) & "" & Chr(10) & " ソース = Csv.Document(File.Contents(""" & strFilePass & """),[Delimiter="","", Columns=" & intColumnum & ", Encoding=932, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " 変更された型 = Table.TransformColumnTypes(ソース," & strColumn & ")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    変更された型"

End Sub