CSVの読み込み方法（改の改）

公開日：2017-03-17　最終更新日：2021-05-10

CSVの読み込み方法（改の改）

CSVのマクロVBAでの読込方法については複数の記事を掲載しており、人気記事として多くのアクセスがあります。
掲載しているVBAコードは汎用的に書いてあり、ほぼそのまま使用できるものです。
しかし、
CSVは多くの形式（区切り文字、文字コード等）があり、今まで掲載したコードでは解決出来ないものがあります。
そこで、今回は今まで対応していなかった形式も含めて、
通常考えられる形式を全て処理可能なコードを提示します。

CSVの形式について

区切り文字

カンマ区切り
Comma Separated Values
ファイルの拡張子はcsv

タブ区切り
Tab Separated Values
ファイルの拡張子はtsvまたはcsv

狭義でのCSVは、もちろんカンマ区切りですが、
拡張子がcsvでタブ区切りのファイルも結構存在しており、Excelでは普通に開くことが出来ます。
拡張子のcsvがExcelに標準で紐ついていて、Excelがタブ区切りも読めるので、
本来ならtsvなのでしょうが、拡張子がcsvとなっているタブ区切りも多く存在します。

文字コード

Shift-JIS
Windowsでは標準ともいえる文字コードです。
Windowsで作成したcsvなら、ほとんどがShift-Jisでしよう。
メモ帳で保存時には、「ANSI」になります。
※Windows10のメモ帳はUTF-8のBOM無しが標準に変わりました。

UTF-8
BOM付きとBOM無しがあります。
BOM無しはUTF-8Nと言われたりしますが、
ここでは呼び方よりもBOMの有無があるという事を理解してください。。

Unicode
文字コードというより、文字集合と言った方が正しいのかもしれませんが、
ここでは、
UTF-16
と認識していただければ良いでしょう。

Unicode big endian
Unicodeには、複数バイトで構成されるデータの並べ方で、エンディアンというものがあり、
ビッグエンディアンとリトルエンディアンがある。
メモ帳でも保存時の、
「Unicode」は、リトルエンディアン
「Unicode big endian」は、ビッグエンディアン

結果として、上記の区切り文字と文字コードの組み合わせが存在することになります。
全ての組み合わせで処理可能なVBAコードを作ることが目的です。

CSVの読み込み方法（改）

実施したいこと・ファイル名を指定し、形式をカンマ区切り、文字列で開く、その際、改行コードLF、CRLF、CRいずれにも対応、セル内の","や改行についてはカラムで区切らない。掲示板で上記のリクエストを頂きました。ということで、対応ロジックを書いてみました。

UTF-8でCSVの読み書き（ADODB.Stream）

・アクティブシートの内容をUTF-8でCSV出力します・UTF-8のCSVを読込、シートに出力します・ADODB.Streamのメソッドとプロパティ・本サイトにあるCSV関連記事一覧

これらのページで掲載しているVBAコードを改造し、
テキストの文字コードを判定を加えたものです。

※UTF-8N（BOMなし）は文字コードの自動判別が完全には対応できません。
UTF-8Nのcsvを読み込むときには、
下記使用例のように、OptionのCharSetに"utf-8"または"utf-8n"を指定してください。

CSV読み込みVBAコード


  
    
      Option Explicit


      '使用例

      Sub sample1()

      　　Dim ws As Worksheet

      　　Dim sFile As String

      　　'csvファイルを指定

      　　sFile = "csvのフルパス"

      　　'出力シート

      　　Set ws = ActiveSheet

      　　ws.Cells.Clear

      　　'以下では全列を文字に設定

      　　'数値も文字としてセルに入ります

      　　'文字設定にしなければ数値は数値として入ります。

      　　ws.Cells.NumberFormatLocal = "@"

      　　Application.ScreenUpdating = False

      　　'CSV取込

      　　Call CsvInText(ws, sFile)

      　　'utf-8決め打ちで読み込む場合は以下で

      　　'Call CsvInText(ws, sFile, "utf-8")

      　　Application.ScreenUpdating = True

      End Sub


      Sub CsvInText(ByVal ws As Worksheet, _

      　　　　　　　ByVal strFile As String, _

      　　　　　　　Optional ByVal CharSet As String = "Auto")

      　　Dim strRec As String

      　　Dim i As Long, j As Long, k As Long

      　　Dim lngQuate As Long

      　　Dim strCell As String

      　　Dim blnCrLf As Boolean

      　

      　　'文字コードを自動判別し、全行をCrLf区切りに統一してStringに入れる

      　　strRec = readCsv(strFile, CharSet)


      　　i = 1 'シートの1行目から出力

      　　j = 0 '列位置はPutCellでカウントアップ

      　　lngQuate = 0 'ダブルクォーテーションの数

      　　strCell = ""

      　　For k = 1 To Len(strRec)

      　　　　Select Case Mid(strRec, k, 1)

      　　　　　　Case vbLf, vbCr '「"」が偶数なら改行、奇数ならただの文字

      　　　　　　　　If lngQuate Mod 2 = 0 Then

      　　　　　　　　　　blnCrLf = False

      　　　　　　　　　　If k > 1 Then '改行のCrLfはCrで改行判定済なので無視する

      　　　　　　　　　　　　If Mid(strRec, k - 1, 2) = vbCrLf Then

      　　　　　　　　　　　　　　blnCrLf = True

      　　　　　　　　　　　　End If

      　　　　　　　　　　End If

      　　　　　　　　　　If blnCrLf = False Then

      　　　　　　　　　　　　Call PutCell(ws, i, j, strCell, lngQuate)

      　　　　　　　　　　　　i = i + 1

      　　　　　　　　　　　　j = 0

      　　　　　　　　　　　　lngQuate = 0

      　　　　　　　　　　　　strCell = ""

      　　　　　　　　　　End If

      　　　　　　　　Else

      　　　　　　　　　　strCell = strCell & Mid(strRec, k, 1)

      　　　　　　　　End If

      　　　　　　Case ",", vbTab '「"」が偶数なら区切り、奇数ならただの文字

      　　　　　　　　If lngQuate Mod 2 = 0 Then

      　　　　　　　　　　Call PutCell(ws, i, j, strCell, lngQuate)

      　　　　　　　　Else

      　　　　　　　　　　strCell = strCell & Mid(strRec, k, 1)

      　　　　　　　　End If

      　　　　　　Case """" '「"」のカウントをとる

      　　　　　　　　lngQuate = lngQuate + 1

      　　　　　　　　strCell = strCell & Mid(strRec, k, 1)

      　　　　　　Case Else

      　　　　　　　　strCell = strCell & Mid(strRec, k, 1)

      　　　　End Select

      　　Next

      　

      　　'最終列の処理

      　　If j > 0 And strCell <> "" Then

      　　　　Call PutCell(ws, i, j, strCell, lngQuate)

      　　End If

      End Sub


      '1フィールドごとにセルに出力

      Sub PutCell(ByVal ws As Worksheet, _

      　　　　　　ByRef i As Long, ByRef j As Long, _

      　　　　　　ByRef strCell As String, _

      　　　　　　ByRef lngQuate As Long)

      　　j = j + 1

      　　'「""」を「"」で置換

      　　strCell = Replace(strCell, """""", """")

      　　'前後の「"」を削除

      　　If Left(strCell, 1) = """" And Right(strCell, 1) = """" Then

      　　　　If Len(strCell) <= 2 Then

      　　　　　　strCell = ""

      　　　　Else

      　　　　　　strCell = Mid(strCell, 2, Len(strCell) - 2)

      　　　　End If

      　　End If

      　　ws.Cells(i, j) = strCell

      　　strCell = ""

      　　lngQuate = 0

      End Sub


      '文字コードを自動判別し、全行をCrLf区切りに統一してStringに入れる

      Function readCsv(ByVal strFile As String, _

      　　　　　　　　 ByVal CharSet As String) As String

      　　Dim objFSO As New FileSystemObject

      　　Dim inTS As TextStream

      　　Dim adoSt As New ADODB.Stream

      　　Dim strRec As String

      　　Dim i As Long

      　　Dim aryRec() As String

      　　

      　　If CharSet = "Auto" Then CharSet = getCharSet(CStr(strFile))

      　　Select Case LCase(CharSet)

      　　　　Case "unicode", "unicodefeff"

      　　　　　　'TristateTrueで読込

      　　　　　　Set inTS = objFSO.OpenTextFile(CStr(strFile), ForReading, , TristateTrue)

      　　　　　　strRec = inTS.ReadAll

      　　　　Case "utf-8", "utf-8n"

      　　　　　　'ADOを使って読込、その後の処理を統一するため全レコードをCrLfで結合

      　　　　　　With adoSt

      　　　　　　　　.Type = adTypeText

      　　　　　　　　.CharSet = "UTF-8"

      　　　　　　　　.Open

      　　　　　　　　.LoadFromFile strFile

      　　　　　　　　i = 0

      　　　　　　　　Do While Not (.EOS)

      　　　　　　　　　　ReDim Preserve aryRec(i)

      　　　　　　　　　　aryRec(i) = .ReadText(adReadLine)

      　　　　　　　　　　i = i + 1

      　　　　　　　　Loop

      　　　　　　　　.Close

      　　　　　　　　strRec = Join(aryRec, vbCrLf)

      　　　　　　End With

      　　　　Case Else

      　　　　　　Set inTS = objFSO.OpenTextFile(CStr(strFile), ForReading)

      　　　　　　strRec = inTS.ReadAll

      　　End Select

      　

      　　Set inTS = Nothing

      　　Set objFSO = Nothing

      　　readCsv = strRec

      End Function


      '文字コードの自動判別

      'UTF-8のBOMなしは文字コードの判別に対応できていません。

      Function getCharSet(ByVal sFile As String) As String

      　　Dim objHtml As MSHTML.HTMLDocument

      　　Dim strRec As String

      　　'GetObjectでHTMLDocumentを生成し、文字コードを判定する

      　　Set objHtml = GetObject(sFile, "htmlfile")

      　　Do While objHtml.readyState <> "complete"

      　　　　DoEvents

      　　Loop

      　　getCharSet = objHtml.CharSet

      　　Set objHtml = Nothing

      End Function

※Tabが"で囲まれていないCSVの場合

Tabが"で囲まれていないCSVの場合は、
Case ",", vbTab '「"」が偶数なら区切り、奇数ならただの文字
このvbTabを消してください。

参照設定

Microsoft Scripting Runtime
Microsoft ActiveX Data Objects x.x Library
Microsoft Html Object Library

文字コード処理について

文字コードの判別については、GetObject関数でhtmlfileを作成しCharSetで判定しています。

・GetObject関数の構文・GetObjectの解説・GetObject関数の使用例・GetObject関数とCreateObject関数の使い分け・CreateObject関数

文字コードによって、
FileSystemObject.OpenTextFile
ADODB.Stream
どちらで読み込むかを振り分けています。

VBAコード全体については解説しきれませんので、VBAコード内コメントを頼りに読み進めてください。

区切り文字と文字コードの主要な組み合わせについては、
テストデータを作成して確認しましたが、漏れがあるかもしれません。
VBA開発で実際に使う事もあるので、気が付いた時点で修正します。
また、上手く動かない等に気づいたときにご一報をいただければ修正します。

配列を使ってシートにまとめて出力する場合

上記のマクロVBAでは、1フィールドごとにセルに出力しています。
これは明らかに処理速度が遅くなってしまいます。
（数万件くらいまでなら、そもそもそんなに時間もかかりませんが）
ここは、一旦配列に入れておいて、最後にまとめてシートに出力したいところです。
なのですが、
世の中には、お行儀のよいCSVばかりではありません。
行によって列数（つまりカンマの区切り数）が不定となっているようなものもあったりします。
先頭行は10列ではじまっているのに、途中から12列になっていたりという事です。
このようなCSVにおいては列数を事前に決められない為、配列を用意するのが難しくなります。
これらに対応するには、列数を多めにとった配列を用意するか、
一旦ジャグ配列（要素も配列である配列）として確保して、最後に2次元配列に入れ直してからシートに出力する等の工夫が必要です。
・列数が決まっていれば、その列数で配列を用意
・列数不明の時は、1行目の列数取得後に配列を用意
・列数不定の場合は、ジャグ配列で処理
以上のどれかで対応することになります。
下に行くにしたがって、VBAの難易度は上がっていきます。
上記VBAの主な変更点は、
・CsvInTextで配列を用意
・PutCellの
　ws.Cells(i, j) = strCell
　このws.Cells(i, j)を配列に変更
固定列数であれば、そんなに多くの修正は必要ないと思います。
配列の行数は最初は少し大きめに確保しておいて、行数が足らなくなったらRedim Preserveで確保すれば良いでしょう。
このような面倒な処理の必要性がないのは、シートのセルを直接利用する最大の利点ともいえます。

興味のある方は、ぜひ配列化にチャレンジしてみてください。
要望があり機会があれば、当サイトでも公開しようと思います。
・・・
という事で、作成しました。
CSVの読み込み方法（ジャグ配列）

・CSVの読み込み方法（改の改）での予告・CSV読み込みでのジャグ配列の使いどころ・CSV読み込みVBAコード：ジャグ配列バージョン・最後に・本サイトにあるCSV関連記事一覧

QueryTablesを使ったCSV読み込みVBAコード

Unicode big endian の対応が必要ないのであれば、
以下のQueryTablesを使った簡単なコードで対応できます。


  
    
      Sub sample2()

      　　Dim ws As Worksheet

      　　Dim sFile As String

      　　sFile = "パス\test.csv"

      　　

      　　Set ws = Worksheets("Sheet1")

      　　ws.Cells.Clear

      　　ws.Cells.NumberFormatLocal = "@"

      　　Call CsvInQuery(ws, sFile)

      End Sub


      Sub CsvInQuery(ByVal ws As Worksheet, ByVal sFile As String)

      　　Dim cArray() As Integer

      　　Dim i As Long

      　　

      　　ReDim cArray(255)

      　　For i = 0 To 255

      　　　　cArray(i) = XlColumnDataType.xlTextFormat

      　　Next

      　　With ws.QueryTables.Add(Connection:="TEXT;" & sFile, Destination:=ws.Range("$A$1"))

      　　　　.TextFileTabDelimiter = True

      　　　　.TextFileCommaDelimiter = True

      　　　　.TextFileColumnDataTypes = cArray

      　　　　Select Case LCase(getCharSet(CStr(sFile)))

      　　　　　　Case "utf-8", "unicode", "unicodefeff"

      　　　　　　　　.TextFilePlatform = 65001

      　　　　　　Case Else

      　　　　　　　　.TextFilePlatform = 932

      　　　　End Select

      　　　　.Refresh BackgroundQuery:=False

      　　　　.Delete

      　　End With

      End Sub


      Function getCharSet(ByVal sFile As String) As String

      　　Dim objHtml As MSHTML.HTMLDocument

      　　

      　　Set objHtml = GetObject(sFile, "htmlfile")

      　　Do While objHtml.readyState <> "complete"

      　　　　DoEvents

      　　Loop

      　　getCharSet = objHtml.charSet

      　　

      　　Set objHtml = Nothing

      End Function

要点としては、
TextFileColumnDataTypes
ここには、実際のカラム数以上を指定しても問題ありません。
上記では、256列全てを文字列指定にしています。
また、区切り文字として、カンマとタブを指定しておくことで、どちらにも対応できます。
これにより、VBAコードを変更することなく、上記のコードでほとんどのCSVを処理可能としています。
ただし、Unicode big endian については、QueryTablesは対応できません。

本サイトにあるCSV関連記事一覧

VBAでのCSVの扱い方まとめ

・本サイトにあるCSV関連記事一覧・CSVの読込方法・CSVの読み込み方法（改）・CSVの読み込み方法（改の改）・CSVの読み込み方法（ジャグ配列）・CSVの読み込み方法（ジャグ配列）（改）・CSVの出力（書き出し）方法・UTF-8でCSVの読み書き（ADODB.Stream）・ADOでCSVの読み込み（SQL）

CSVの読込方法

・もっとも簡単かつ良くあるCSV読み込みVBAコード・「,」「"」に対応したCSV読み込みVBAコード・CSVをExcelブックとして開くVBA ・クエリーテーブルを使ったCSV読み込みVBAコード・その他のCSV読み込み方法

CSVの読み込み方法（改）

CSVの読み込み方法（改の改）

・CSVの形式について・CSV読み込みVBAコード・配列を使ってシートにまとめて出力する場合・QueryTablesを使ったCSV読み込みVBAコード・本サイトにあるCSV関連記事一覧

CSVの読み込み方法（ジャグ配列）

CSVの読み込み方法（ジャグ配列）（改）

・CSV読み込みVBAコード：CSVの読み込み方法（ジャグ配列）（改）・CSVの読み込み方法（ジャグ配列）（改）の使用例・本サイトにあるCSV関連記事一覧

CSVの出力（書き出し）方法

・エクセルの機能をそのまま利用します・直接CSVを出力・本サイトにあるCSV関連記事一覧

UTF-8でCSVの読み書き（ADODB.Stream）

ADOでCSVの読み込み（SQL）

・CSVテストデータ・ADOでCSV読込のVBA ・ADO使用時の注意点・ADOレコードセットをCSV出力・ADOでTSVの読み込み・ADOでCSVの読み込みについて・本サイトにあるCSV関連記事一覧

※ほとんどの記事でUTF-8に対応しています。

同じテーマ「マクロVBAサンプル集」の記事

VBAでのCSVの扱い方まとめ
 CSVの読み込み方法
 CSVの読み込み方法（改）
CSVの読み込み方法（改の改）
CSVの読み込み方法（ジャグ配列）
CSVの読み込み方法（ジャグ配列）（改）
CSVの出力（書き出し）方法
 UTF-8でCSVの読み書き（ADODB.Stream）
ADOでマスタ付加と集計（SQL）
ADOでマスタ更新（SQL）
ADOでCSVの読み込み（SQL）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門

ホーム
マクロVBA応用編
マクロVBAサンプル集
CSVの読み込み方法（改の改）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBAサンプル集
CSVの読み込み方法（改の改）