VBAサンプル集
CSVの読み込み方法(ジャグ配列)

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

CSVの読み込み方法(ジャグ配列)

CSVのマクロVBAでの読込方法についての記事は、人気記事として多くのアクセスがあります。
当初作成して以来、ご要望をいただいたり自身で使っている中で、
対応できないCSVが出てくるたびに改良を重ねています。


今回のVBAは、一旦ジャグ配列を使用したCSV読み込み方法になります。

現在、本サイト内のCSV読み込み関連としては以下のページがあります。
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の読み込み方法(改の改)での予告

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

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

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


CSV読み込みでのジャグ配列の使いどころ

直接セルに出力したからと言って、そんなに遅いという事はありません。
そもそも所要時間の半分近くは、ファイルからのデータ読み込み部分になるからです。
特にUTF-8で改行がLFになっている場合と、Windows標準のCRLFのCSVを共通で処理しているため、
データとしてのLFと改行としてのLFの区別をつけるために、行ごとの読み込みが必要となっています。
この部分の処理時間を短縮するには、
ある程度決め打ち(UTF-8専用とか、データ内にLFが無いとか)しないとかなり難しいVBAになると思います。

今回はファイルからデータ読み込んだ後のセルへの出力方法のみ変更しています。
1セル毎ではなく、一旦配列に入れてから一括でシートに出力するようにして時簡短縮を図っています。
ですが、
結果から言うと、計測してみた限りではさほど変わりませんでした。
確かに速くはなっていますが、数MBのCSVでは時間を測ってみないと分からない程度の違いです。
4MB程度のCSVで試したところ、
前が9秒くらいで、今回が7秒くらい、
まあ早くはなっていますが、、、
そもそもデータ読み込みに3秒以上かかっているので仕方ないところではあります。

CSVの列数が不定(1つのファイル内において行ごとに列数が違う)の場合の対応としてジャグ配列を使います。
CSVの行を1次元配列として、
その1行の要素の中に、1フィールドごとの1次元配列を入れます。

マクロ VBA CSV ジャグ配列

このようなイメージになります。
ただし、このままではワークシートのセルにまとめて出力することができないので、
2次元配列(行数×最大列数)に入れ直してから、ワークシートに出力しています。
※配列は1スタートで作成しています。

CSVを読み込む基本部分は、「CSVの読み込み方法(改の改)」と変更がありません。
あくまで、セル出力の部分を配列に変更したものになります。
CSV→ジャグ配列→2次元配列→シート
このような順で処理しているので、必要に応じてと途中の配列を使う事が出来ます。
シートに出力せずに処理するような場合には色々と活用できるのではないでしょうか。


CSV読み込みVBAコード:ジャグ配列バージョン

Option Explicit

'使用例
Sub sample1()
  Dim ws As Worksheet
  Dim sFile As String
  sFile = "csvのフルパス"
  '出力シート
  Set ws = ActiveSheet
  ws.Cells.Clear
  '以下では全列を文字に設定
  '数値も文字としてセルに入ります
  '文字設定にしなければ数値は数値として入ります。
  ws.Cells.NumberFormatLocal = "@"
  Application.ScreenUpdating = False
  'CSV取込
  Call CsvToSheet(ws, sFile)
  'utf-8決め打ちで読み込む場合は以下で
  'Call CsvToSheet(ws, sFile, "utf-8")
  Application.ScreenUpdating = True
End Sub

Public Sub CsvToSheet(ByVal ws As Worksheet, _
           ByVal strFile As String, _
           Optional ByVal CharSet As String = "Auto")
  Dim myArray() As Variant
  'readCsvでCSVを読み込み
  Dim strRec As String
  strRec = readCsv(strFile, CharSet)
  'CsvToJaggedで行・フィールドに分割してジャグ配列に
  Dim jagArray() As Variant
  jagArray = CsvToJagged(strRec)
  'JaggedTo2Dでジャグ配列を2次元配列に変換
  Call JaggedTo2D(jagArray, myArray)
  '上記を全てネストすれば以下で書けますが、お勧めはしません。
  'Call JaggedTo2D(CsvToJagged(readCsv(strFile, CharSet)), myArray)
  '2次元配列→シート
  ws.Range("A1").Resize(UBound(myArray, 1), UBound(myArray, 2)) = myArray
End Sub

Private Sub JaggedTo2D(ByRef jagArray() As Variant, _
            ByRef twoDArray As Variant)
  'ジャグ配列の最大列数取得
  Dim maxCol As Long, v As Variant
  maxCol = 0
  For Each v In jagArray
    If UBound(v) > maxCol Then
      maxCol = UBound(v)
    End If
  Next
  'ジャグ配列→2次元配列
  Dim i1 As Long, i2 As Long
  ReDim twoDArray(1 To UBound(jagArray), 1 To maxCol)
  For i1 = 1 To UBound(jagArray)
    For i2 = 1 To UBound(jagArray(i1))
      twoDArray(i1, i2) = jagArray(i1)(i2)
    Next
  Next
End Sub

Private Function CsvToJagged(ByVal strRec As String) As Variant()
  Dim childArray() As Variant 'ジャグ配列の子配列
  Dim lngQuate As Long 'ダブルクォーテーション数
  Dim strCell As String '1フィールド文字列
  Dim blnCrLf As Boolean '改行判定
  Dim i As Long '行位置
  Dim j As Long '列位置
  Dim k As Long
  
  ReDim CsvToJagged(1 To 1) 'ジャグ配列の初期化
  ReDim childArray(1 To 1) 'ジャグ配列の子配列の初期化
  i = 1 'シートの1行目から出力
  j = 0 '列位置はputChildArrayでカウントアップ
  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 putChildArray(childArray, j, strCell, lngQuate)
            'これが改行となる
            Call putjagArray(CsvToJagged, childArray, _
                     i, j, lngQuate, strCell)
          End If
        Else
          strCell = strCell & Mid(strRec, k, 1)
        End If
      Case ",", vbTab '「"」が偶数なら区切り、奇数ならただの文字
        If lngQuate Mod 2 = 0 Then
          Call putChildArray(childArray, 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 putChildArray(childArray, j, strCell, lngQuate)
    Call putjagArray(CsvToJagged, childArray, _
             i, j, lngQuate, strCell)
  End If
End Function

Private Sub putjagArray(ByRef jagArray() As Variant, _
            ByRef childArray() As Variant, _
            ByRef i As Long, _
            ByRef j As Long, _
            ByRef lngQuate As Long, _
            ByRef strCell As String)
  If i > UBound(jagArray) Then '常に成立するが一応記述
    ReDim Preserve jagArray(1 To i)
  End If
  jagArray(i) = childArray '子配列をジャグ配列に入れる
  ReDim childArray(1 To 1) '子配列の初期化
  i = i + 1 '列位置
  j = 0 '列位置
  lngQuate = 0 'ダブルクォーテーション数
  strCell = "" '1フィールド文字列
End Sub

'1フィールドごとにセルに出力
Private Sub putChildArray(ByRef childArray() As Variant, _
             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
  If j > UBound(childArray) Then
    ReDim Preserve childArray(1 To j)
  End If
  childArray(j) = strCell
  strCell = ""
  lngQuate = 0
End Sub

'文字コードを自動判別し、全行をCrLf区切りに統一してStringに入れる
Private 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(strFile)
  Select Case LCase(CharSet)
    Case "unicode", "unicodefeff"
      'TristateTrueで読込
      Set inTS = objFSO.OpenTextFile(strFile, ForReading, , TristateTrue)
      strRec = inTS.ReadAll
      inTS.Close
    Case "utf-8"
      'ADOを使って読込、その後の処理を統一するため全レコードをCrLfで結合
      Set inTS = objFSO.OpenTextFile(strFile, ForAppending)
      i = inTS.Line - 1
      inTS.Close
      ReDim aryRec(i)
      With adoSt
        .Type = adTypeText
        .CharSet = "UTF-8"
        .Open
        .LoadFromFile strFile
        i = 0
        Do While Not (.EOS)
          aryRec(i) = .ReadText(adReadLine)
          i = i + 1
        Loop
        .Close
        strRec = Join(aryRec, vbCrLf)
      End With
    Case Else
      Set inTS = objFSO.OpenTextFile(strFile, ForReading)
      strRec = inTS.ReadAll
      inTS.Close
  End Select
  Set inTS = Nothing
  Set objFSO = Nothing
  readCsv = strRec
End Function

'文字コードの自動判別
'UTF-8のBOMなしは文字コードの判別に対応できていません。
Private 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

VBAコードについては解説しきれないので、
コード内のコメントを参考にしてください。

最後に

CSVを読み込む方法は多種多様にありますが、
この記事を書いた当初は、
エクセルのマクロVBAでCSVの読み込みについてネットで検索したところ、なかなか良いものが見つかりませんでした。
それならということで、CSVの読み込みのマクロVBAを作成しました。
それ以来改良を何度か重ねた結果が今回のものとなっています。

VBAコードは、常に引き継いでいるので、冗長な部分もあると思いますが、
旧バージョンを使い、独自に変更を加えている人にとっては、
新バージョンでは、なるべく旧バージョンのVBAコードが継承されている方が良いだろうという理由もあります。

掲載しているVBAは当然そのままでも動作しますが、
独自にアレンジを加えて使用したり、そもそもVBA学習の素材として使ってもらう事を目的としています。

後日追記
文字コード判定で、UTF-8Nが判定できていないという課題が残っています。
文字コードの判定を全て完璧に行うのは無理ですが、簡易的にでもUTF-8Nを判定したいところです。
そこで、きわめて簡易的ではありますが、これに対応するVBAを書いてみました。
また、使うにあたって参照設定が面倒な場合もあるので、参照設定せずにCreateObjectに変更しています。
CSVの読み込み方法(ジャグ配列)(改)
・CSV読み込みVBAコード:CSVの読み込み方法(ジャグ配列)(改) ・CSVの読み込み方法(ジャグ配列)(改)の使用例 ・本サイトにあるCSV関連記事一覧


本サイトにある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 ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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