【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

VBAの説明

前回作成したクエリと、指定範囲にある表を削除します。
同じ名前のクエリと表があると、VBAを再実行しRefreshするときにエラーとなるためです。

For Each Query In objWorkbook.Queries
Query.Delete
NextRange(“A1:H10”).ClearContents

Workbook.Queries.AddメソッドによりCSVファイルを開き、データを読み込みます。

objWorkbook.Queries.Add Name:=strTableName

タカヒロ
タカヒロ
WorksheetではなくWorkbookのメソッドとして使用する必要があります。

戻り値でobjWorkbook.Queriesオブジェクトを取得し、
Formulaでクエリを設定します。

Columnsで列の数を指定します。

Columns=

Encodingで言語を指定します。なお、932は日本語のシフトJISを意味します。

Encoding=932

他にも

・日本語EUC:51932
・日本語JIS:50220
・UTF-8:65001
・UTF-16:1200

などが指定できます。
詳細はデータ取り込み時にでるウイザードの左上「元のファイル」下のプルダウンリストをご参照ください。

「変更された型」の引数に「Table.TransformColumnTypes」を指定します。これにより指定フォーマットによる表が作成されます。

変更された型 = Table.TransformColumnTypes

「名前が変更された列」の引数に「Table.RenameColumns」を指定します。これによりColumn**のデフォルト表示名から指定する項目名へ変更することができます。

名前が変更された列 “” = Table.RenameColumns

objWorkbook.ActiveSheet.ListObjects.Addメソッドにより、作成したクエリを参照し、表へデータを挿入していきます。

SQLで対象クエリを指定し参照します。

.CommandType = xlCmdSql
.CommandText = Array(“SELECT * FROM [” & strTableName & “]”)

リフレッシュメソッドで表データを更新します。

.Refresh



VBAの実装手順

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」+「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。


さいごに

いかがでしょうか。

今回は、
Excel VBAでカンマ区切りのテキストデータ(CSV)を取り込む方法について
まとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です