VBAサンプル集
CSVの読み込み方法(改の改)

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日: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の読み込み方法(改)
実施したいこと ・ファイル名を指定し、形式をカンマ区切り、文字列で開く、その際、改行コードLF、CRLF、CRいずれにも対応、セル内の","や改行についてはカラムで区切らない。掲示板で上記のリクエストを頂きました。ということで、対応ロジックを書いてみました。
CSVの読み込み方法(改の改)
・CSVの形式について ・CSV読み込みVBAコード ・配列を使ってシートにまとめて出力する場合 ・QueryTablesを使ったCSV読み込みVBAコード ・本サイトにあるCSV関連記事一覧
CSVの読み込み方法(ジャグ配列)
・CSVの読み込み方法(改の改)での予告 ・CSV読み込みでのジャグ配列の使いどころ ・CSV読み込みVBAコード:ジャグ配列バージョン ・最後に ・本サイトにあるCSV関連記事一覧
CSVの読み込み方法(ジャグ配列)(改)
・CSV読み込みVBAコード:CSVの読み込み方法(ジャグ配列)(改) ・CSVの読み込み方法(ジャグ配列)(改)の使用例 ・本サイトにあるCSV関連記事一覧
CSVの出力(書き出し)方法
・エクセルの機能をそのまま利用します ・直接CSVを出力 ・本サイトにあるCSV関連記事一覧
UTF-8でCSVの読み書き(ADODB.Stream)
・アクティブシートの内容をUTF-8でCSV出力します ・UTF-8のCSVを読込、シートに出力します ・ADODB.Streamのメソッドとプロパティ ・本サイトにあるCSV関連記事一覧
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)


新着記事NEW ・・・新着記事一覧を見る

数字(1~50)を丸付き数字に変換するVBA|VBA技術解説(2022-11-15)
TEXTAFTER関数(テキストの指定文字列より後ろの部分を返す)|エクセル入門(2022-11-14)
TEXTBEFORE関数(テキストの指定文字列より前の部分を返す)|エクセル入門(2022-11-14)
TEXTSPLIT関数(列と行の区切り記号で文字列を分割)|エクセル入門(2022-11-12)
LAMBDA以降の新関数はVBAで使えるか|VBA技術解説(2022-11-11)
WRAPCOLS関数(1次元配列を指定数の列で折り返す)|エクセル入門(2022-11-08)
WRAPROWS関数(1次元配列を指定数の行で折り返す)|エクセル入門(2022-11-08)
EXPAND関数(配列を指定された行と列に拡張する)|エクセル入門(2022-11-07)
TAKE関数(配列の先頭/末尾から指定行/列数を取得)|エクセル入門(2022-11-06)
DROP関数(配列の先頭/末尾から指定行/列数を除外)|エクセル入門(2022-11-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.エクセルVBAでのシート指定方法|VBA技術解説
9.マクロって何?VBAって何?|VBA入門
10.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄




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


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



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