ExcelマクロVBAサンプル集
VBAでのCSVの扱い方まとめ

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

VBAでのCSVの扱い方まとめ


マクロVBAでCSVの読み書きする方法はいくつもあり、当サイトでも複数のページでそれぞれVBAコードを掲載しています。
順次記事を掲載しているので、それぞれどのような特徴があるかが良く分からなくなってしまっているようです。
そこで、CSVに関するページをまとめておきました。


本サイトにある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の読込は、「CSVの読込方法」「CSVの読込方法(改)」実施したいこと・ファイル名を指定し、形式をカンマ区切り、文字列で開く、その際、改行コードLF、CRLF、CRいずれにも対応、セル内の","や改行についてはカラムで区切らない。
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の読み込み方法」を参考にして下さい。

以下で簡単に内容を紹介します。

CSVの読込方法

CSVの読込方法
エクセルのVBAでのCSVの読込方法としては。・テキストファイルとして読み込む ・ワークブックとして読み込む ・クエリーテーブルを使う ・ADOを使う ・PowerQueryを使う 大別するとこのようになります。この記事を書いた当初は、エクセルのVBAでCSVの読み込みについてネットで検索したところ、

エクセルのマクロVBAでのCSVの読込方法としては。
・テキストファイルとして読み込む
・ワークブックとして読み込む
・クエリーテーブルを使う
・ADOを使う
・Power Queryを使う
大別するとこのようになります。
このページでは、以下について解説及びVBAコードを掲載しています。

もっとも簡単かつ良くあるCSV読み込みVBAコード


「,」「"」に対応したCSV読み込みVBAコード


CSVをExcelブックとして開くVBA


クエリーテーブルを使ったCSV読み込みVBAコード


CSVの読み込み方法(改)

CSVの読み込み方法(改)
実施したいこと ・ファイル名を指定し、形式をカンマ区切り、文字列で開く、その際、改行コードLF、CRLF、CRいずれにも対応、セル内の","や改行についてはカラムで区切らない。掲示板で上記のリクエストを頂きました。ということで、対応ロジックを書いてみました。

CSVの読込方法
これに対して、以下の要望を受けて対応をしたVBAになります。
・ファイル名を指定し、形式をカンマ区切り、文字列で開く
・その際、改行コードLF、CRLF、CRいずれにも対応
・セル内の","や改行についてはカラムで区切らない

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

CSVの読み込み方法(改の改)
CSVのVBAでの読込方法については複数の記事を掲載しており、人気記事として多くのアクセスがあります。掲載しているVBAコードは汎用的に書いてあり、ほぼそのまま使用できるものです。しかし、CSVは多くの形式(区切り文字、文字コード等)があり、今まで掲載したコードでは解決出来ないものがあります。

CSVの形式について

区切り文字
カンマ区切り
タブ区切り
文字コード
Shit-JIS
UTF-8
Unicode
Unicode big endian


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

CSVの読み込み方法(改)
UTF-8でCSVの読み書き(ADODB.Stream)

これらのページで掲載しているVBAコードを改造し、
テキストの文字コードを判定を加えたものです。

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

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

一旦ジャグ配列(配列の配列)に入れてからシートに出力するCSV読み込み方法になります。
以下の予告をもとに作成したVBAコードを公開したページになります。

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の読み込み方法(ジャグ配列)(改)

CSVの読み込み方法(ジャグ配列)(改)
CSVのマクロVBAでの読込方法についての記事は、人気記事として多くのアクセスがあります。順次改定していくつかのバージョンが存在します。最新のジャグ配列(配列の配列)で読み込むVBAについて、UTF-8Nの文字コード判別の課題が残っていました。

CSVの読み込み方法(ジャグ配列)では、UTF-8Nの文字コード判別の課題が残っていました。
そこで、

文字コードの判定を全て完璧に行うのは無理ですが、簡易的にでもUTF-8Nを判定したいところです。
そこで、きわめて簡易的ではありますが、これに対応するVBAを書いてみました。
また、使うにあたって参照設定が面倒な場合もあるので、参照設定せずにCreateObjectに変更しています。

文字コードの判定は、これで完璧ということではありません。
SHIFT_JISで使われていてUTF-8で使われていないと思われる文字コードの範囲を判定しています。
つまり、SHIFT_JISと判定できる場合以外は、UTF-8という事にしています。


CSVの出力(書き出し)方法

CSVの出力(書き出し)方法
シート内容をCSV出力(書き出し)する方法です。CSVの読込は、「CSVの読込方法」「CSVの読込方法(改)」実施したいこと・ファイル名を指定し、形式をカンマ区切り、文字列で開く、その際、改行コードLF、CRLF、CRいずれにも対応、セル内の","や改行についてはカラムで区切らない。

まずは、エクセルの機能をそのまま利用します。

しかし、これでは、いろいろ不都合な場合があります。
他システム、特にDB等へアップロードする場合には、このままでは出来ない事があるのです。
例えば、日付は、表示形式のままの文字列で出力されてしまいます。
また数値もカンマ付の場合は、"12,345"のように、文字列として出力されます。
CSV出力前に、当該シートの書式を全て直してから行えばよいのですが、
書式の変更も面倒なら、また元に戻す必要があり、何かと不都合です。

このような場合は、直接CSVを出力するようにします。

「最終行取得」、「最終列取得」は以前に作成済のモジュールです。
行列がでこぼこに入力されている場合でも、正しく最終判定をする為に使用しています。
通常の一覧なら、End(xlUp)等でも良いでしょう。
FileSystemObjectを使用していますが、昔からある、
Open ファイル For Output As #1
でも良いです。

UTF-8でCSVの読み書き(ADODB.Stream)

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

VBAでUTF-8を扱う為には、ADODB.Streamを使う必要があります。

アクティブシートの内容をUTF-8でCSV出力します。

シートのUsedRangeをCSV出力しています。
※本記事を掲載後に、BOM無しのUTF8(UTF8N)でCSVを作成することがありましたので追記します。
このようにすることで、BOM無し(UTF8N)でCSVを作成できます。

UTF-8のCSVを行単位で読込、アクティブシーヘ書き出しています。

ADODB.Streamのメソッドとプロパティ
Open メソッドを使って、Record または URL から Stream を開きます。
Close メソッドを使って、Stream を閉じます。
Write メソッドまたは WriteText メソッドを使って、バイトまたはテキストを Stream に入力します。
Read メソッドまたは ReadText メソッドを使って、Stream からバイトを読み取ります。
Flush メソッドを使って、ADO バッファにある Stream データを基になるオブジェクトに書き込みます。
CopyTo メソッドを使って、Stream の内容を別の Stream にコピーします。
SkipLine メソッドおよび LineSeparator プロパティを使って、ソース ファイルから行を読み取る方法を制御します。
EOS プロパティおよび SetEOS メソッドを使って、ストリーム位置の末尾を設定します。
SaveToFile メソッドおよび LoadFromFile メソッドを使って、ファイル内のデータを保存および復元します。
Charset プロパティを使って、Stream の保存に使う文字セットを指定します。
Cancel メソッドを使って、非同期 Stream 操作を停止します。
Size プロパティを使って、Stream 内のバイト数を設定します。
Position プロパティを使って、Stream 内の現在の位置を制御します。
Type プロパティを使って、Stream 内のデータ型を設定します。
State プロパティを使って、Stream の現在の状態 (開いている、閉じている、または実行中) を設定します。
Mode プロパティを使って、Stream のアクセス モードを指定します。

ADOでCSVの読み込み(SQL)

ADOでCSVの読み込み(SQL)
VBAでADOを使用し、CSVデータを読み込みます。ADOではSQL文が必要になりますが、ここではSQL文の詳細については説明を省略します。ADO以外の方法については、「CSVの読み込み方法」を参考にして下さい。

VBAでADOを使用し、CSVデータを読み込みます。
ADOではSQL文が必要になりますが、
ここではSQL文の詳細については説明を省略します。
エクセルのブックと同一フォルダにある「TESTCSV.csv」を読み込み、
シート「CSV」に貼り付けています。



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

VBAでのCSVの扱い方まとめ
CSVの読み込み方法
CSVの読み込み方法(改)
CSVの読み込み方法(改の改)
CSVの読み込み方法(ジャグ配列)
CSVの読み込み方法(ジャグ配列)(改)
CSVの出力(書き出し)方法
UTF-8でCSVの読み書き(ADODB.Stream)
ADOでマスタ付加と集計(SQL)
ADOでマスタ更新(SQL)
ADOでCSVの読み込み(SQL)


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

CSVの読み込み方法(ジャグ配列)(改)|VBAサンプル集(11月11日)
VBAでのCSVの扱い方まとめ|VBAサンプル集(11月9日)
VBAにおける変数のメモリアドレスについて|VBA技術解説(11月8日)
空文字列の扱い方と処理速度について(""とvbNullString)|VBA技術解説(1月7日)
Errオブジェクトとユーザー定義エラー|VBA入門(11月5日)
シングルクォートの削除とコピー(PrefixCharacter)|VBA技術解説(11月4日)
ユーザー定義型の制限とクラスとの使い分け|VBA技術解説(11月3日)
クリップボードに2次元配列を作成してシートに貼り付ける|VBA技術解説(11月1日)
VBAクラスを使ったイベント作成(Event,RaiseEvent,WithEvents)|VBA技術解説(10月31日)
VBAクラスのAttributeについて(既定メンバーとFor Each)|VBA技術解説(10月19日)


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

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入門



  • >
  • >
  • >
  • VBAでのCSVの扱い方まとめ

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


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




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