CSVの読み込み方法（ジャグ配列）

公開日：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次元配列を入れます。

このようなイメージになります。
ただし、このままではワークシートのセルにまとめて出力することができないので、
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の読み込み方法（改）

CSVの読み込み方法（改の改）

CSVの読み込み方法（ジャグ配列）

・CSVの読み込み方法（改の改）での予告・CSV読み込みでのジャグ配列の使いどころ・CSV読み込みVBAコード：ジャグ配列バージョン・最後に・本サイトにある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）

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
マクロVBA応用編
マクロVBAサンプル集
CSVの読み込み方法（ジャグ配列）

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

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

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