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

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

CSVの読み込み方法(改の改)

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


CSVの形式について

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

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

狭義でのCSVは、もちろんカンマ区切りですが、
拡張子がcsvでタブ区切りのファイルも結構存在しており、Excelでは普通に開くことが出来ます。
拡張子のcsvがExcelに標準で紐ついていて、Excelがタブ区切りも読めるので、
本来ならtsvなのでしょうが、拡張子がcsvとなっているタブ区切りも多く存在します。
文字コード
Shit-JIS
Windowsでは標準ともいえる文字コードです。
Windowsで作成したcsvなら、ほとんどがShit-Jisでしよう。
メモ帳で保存時には、「ANSI」になります。

UTF-8
BOM付きとBOM無しがあります。
BOM無しはUTF-8Nと言われたりしますが、ここでは呼び方よりも

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

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

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

CSVの読み込み方法(改)
UTF-8でCSVの読み書き(ADODB.Stream)
VBAでUTF-8を扱う為にはADODB.Streamを使う必要があります。以下のコードを使用するには参照設定で「MicrosoftActiveXDataObjects2.8Library」にチェックを付けて下さい。またはDimadoStAsNewADODB.Stream ここを DimadoStAsObject SetadoSt=CreateObjec…

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

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は対応できません。


本記事は、私自身の備忘録も兼ねています。
VBA開発時には、CSV読込の雛形コードとして使えるようにする意味もあり、ここに掲載しました。




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

CSVの読み込み方法
CSVの読み込み方法(改)
CSVの読み込み方法(改の改)
CSVの読み込み方法(ジャグ配列)
CSVの出力(書き出し)方法
UTF-8でCSVの読み書き(ADODB.Stream)
ADOでマスタ付加と集計(SQL)
ADOでマスタ更新(SQL)
ADOでCSVの読み込み(SQL)
Excelファイルを開かずにシート名を取得
Excelファイルを開かずにシート名をチェック


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

CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)
オートフィルタを退避回復するVBAクラス|VBA技術解説(7月6日)
IfステートメントとIIF関数とMax関数の速度比較|VBA技術解説(6月23日)
Withステートメントの実行速度と注意点|VBA技術解説(6月6日)
VBA+SeleniumBasicで検索順位チェッカー(改)|VBA技術解説(6月2日)
マクロでShift_JIS文字コードか判定する|VBA技術解説(6月1日)
Shift_JISのテキストファイルをUTF-8に一括変換|VBAサンプル集(5月31日)
「VBAによる解析シリーズその2 カッコ」をやってみた|エクセル(5月21日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄
9.ひらがな⇔カタカナの変換|エクセル基本操作
10.セルに文字を入れるとは(Range,Value)|VBA入門



  • >
  • >
  • >
  • CSVの読み込み方法(改の改)

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


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




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