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

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日:2020-07-02

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)
VBAでUTF-8を扱う為には、ADODB.Streamを使う必要があります。以下のコードを使用するには、参照設定で、「MicrosoftActiveXDataObjects2.8Library」にチェックを付けて下さい。または、DimadoStAsNewADODB.Stream ここを DimadoStAsObject SetadoSt=CreateO…

これらのページで掲載している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

参照設定
Microsoft Scripting Runtime
Microsoft ActiveX Data Objects x.x Library
Microsoft Html Object Library

文字コード処理について
文字コードの判別については、GetObject関数でhtmlfileを作成しCharSetで判定しています。
ActiveXコンポーネントから提供されたオブジェクトの参照を返します。ファイルパス(フルパスと名前)からオブジェクトの参照を作成したり、既に起動中のオブジェクトを取得する際に使用します。GetObject関数の構文 GetObject([pathname],[class]) pathname 省略可能。
文字コードによって、
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のマクロVBAでの読込方法についての記事は、人気記事として多くのアクセスがあります。当初作成して以来、ご要望をいただいたり自身で使っている中で、対応できない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の扱い方まとめ
マクロVBAでCSVの読み書きする方法はいくつもあり、当サイトでも複数のページでそれぞれVBAコードを掲載しています。順次記事を掲載しているので、それぞれどのような特徴があるかが良く分からなくなってしまっているようです。そこで、CSVに関するページをまとめておきました。
CSVの読込方法
エクセルのVBAでのCSVの読込方法としては。・テキストファイルとして読み込む ・ワークブックとして読み込む ・クエリーテーブルを使う ・ADOを使う ・PowerQueryを使う 大別するとこのようになります。この記事を書いた当初は、エクセルのVBAでCSVの読み込みについてネットで検索したところ、
CSVの読み込み方法(改)
実施したいこと ・ファイル名を指定し、形式をカンマ区切り、文字列で開く、その際、改行コードLF、CRLF、CRいずれにも対応、セル内の","や改行についてはカラムで区切らない。掲示板で上記のリクエストを頂きました。ということで、対応ロジックを書いてみました。
CSVの読み込み方法(改の改)
CSVのVBAでの読込方法については複数の記事を掲載しており、人気記事として多くのアクセスがあります。掲載しているVBAコードは汎用的に書いてあり、ほぼそのまま使用できるものです。しかし、CSVは多くの形式(区切り文字、文字コード等)があり、今まで掲載したコードでは解決出来ないものがあります。
CSVの読み込み方法(ジャグ配列)
CSVのマクロVBAでの読込方法についての記事は、人気記事として多くのアクセスがあります。当初作成して以来、ご要望をいただいたり自身で使っている中で、対応できないCSVが出てくるたびに改良を重ねています。今回のVBAは、一旦ジャグ配列を使用したCSV読み込み方法になります。
CSVの読み込み方法(ジャグ配列)(改)
CSVのマクロVBAでの読込方法についての記事は、人気記事として多くのアクセスがあります。順次改定していくつかのバージョンが存在します。最新のジャグ配列(配列の配列)で読み込むVBAについて、UTF-8Nの文字コード判別の課題が残っていました。
CSVの出力(書き出し)方法
シート内容をCSV出力(書き出し)する方法です。CSVの読込は、VBAでのCSVの扱い方まとめ こちらを参照して下さい。以下では、2通りの方法を紹介します。エクセルの機能をそのまま利用します ※csv出力時日付がm/d/yyyyになってしまう場合の対処 日付がm/d/yyyyになってしまう場合に、yyyy/m/dにする場合には、
UTF-8でCSVの読み書き(ADODB.Stream)
VBAでUTF-8を扱う為には、ADODB.Streamを使う必要があります。以下のコードを使用するには、参照設定で、「MicrosoftActiveXDataObjects2.8Library」にチェックを付けて下さい。または、DimadoStAsNewADODB.Stream ここを DimadoStAsObject SetadoSt=CreateO…
ADOでCSVの読み込み(SQL)
VBAでADOを使用し、CSVデータを読み込みます。ADOではSQL文が必要になりますが、ここではSQL文の詳細については説明を省略します。ADO以外の方法については、「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 ・・・新着記事一覧を見る

import文(パッケージ・モジュールのインポート)|Python入門(9月24日)
例外処理(try文)とexception一覧|Python入門(9月23日)
リスト内包表記|Python入門(9月22日)
Pythonの引数は参照渡しだが・・・|Python入門(9月21日)
lambda(ラムダ式、無名関数)と三項演算子|Python入門(9月20日)
関数内関数(関数のネスト)とスコープ|Python入門(9月18日)
関数の定義(def文)と引数|Python入門(9月18日)
組み込み関数一覧|Python入門(9月17日)
辞書(dict型)|Python入門(9月16日)
入力規則への貼り付けを禁止する|VBA技術解説(9月16日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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