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

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

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

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


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

現在、本サイト内のCSV関連としては以下のページがあります。
CSVの読み込み方法
エクセルのVBAでのCSVの読込方法としては。・テキストファイルとして読み込む ・ワークブックとして読み込む ・クエリーテーブルを使う ・ADOを使う ・PowerQueryを使う 大別するとこのようになります。この記事を書いた当初はエクセルのVBAでCSVの読み込みについてネットで検索したところ
CSVの読み込み方法(改)
実施したいこと ・ファイル名を指定し、形式をカンマ区切り、文字列で開く、その際、改行コードLF、CRLF、CRいずれにも対応、セル内の","や改行についてはカラムで区切らない。掲示板で上記のリクエストを頂きました。ということで、対応ロジックを書いてみました。
CSVの読み込み方法(改の改)
CSVのVBAでの読込方法については複数の記事を掲載しており、人気記事として多くのアクセスがあります。掲載しているVBAコードは汎用的に書いてあり、ほぼそのまま使用できるものです。しかし、CSVは多くの形式(区切り文字、文字コード等)があり、今まで掲載したコードでは解決出来ないものがあります。
CSVの出力(書き出し)方法
シート内容をCSV出力(書き出し)する方法です。CSVの読込は「CSVの読込方法」「CSVの読込方法(改)」実施したいこと・ファイル名を指定し形式をカンマ区切り文字列で開くその際改行コードLFCRLFCRいずれにも対応セル内の","や改行についてはカラムで区切らない。
UTF-8でCSVの読み書き(ADODB.Stream)
VBAでUTF-8を扱う為にはADODB.Streamを使う必要があります。以下のコードを使用するには参照設定で「MicrosoftActiveXDataObjects2.8Library」にチェックを付けて下さい。またはDimadoStAsNewADODB.Stream ここを DimadoStAsObject SetadoSt=CreateObjec…


この中の、「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(readCsv(strFile, CharSet))
  '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



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

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

最後に

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

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

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



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

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


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

VBAコードの全プロシージャー・プロパィ一覧を取得|VBAサンプル集(10月12日)
VBAでエラー行番号を取得できるErl関数|VBA技術解説(10月12日)
手動計算時の注意点と再計算方法|ExcelマクロVBA技術解説(10月9日)
引数の数を可変にできるパラメーター配列(ParamArray)|VBA入門(10月7日)
VBEの使い方:デバッグ|ExcelマクロVBA入門(10月6日)
VBAにおける配列やコレクションの起点について|VBA技術解説(10月5日)
VBEの使い方:オブジェクト ブラウザー|VBA入門(10月5日)
VBEの使い方:ウォッチ ウィンドウ|VBA入門(10月4日)
VBEの使い方:ローカル ウィンドウ|VBA入門(10月3日)
VBEの使い方:イミディエイト ウィンドウ|VBA入門(10月2日)


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

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



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

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


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




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