エクセルVBAのQueryTableオブジェクトであらゆる種類のCSVを取り込む方法


あらゆる種類のCSVを取り込むアイキャッチ
みなさん、こんにちは!
フジタニ(@libartweb)です。

エクセルVBAでCSVを高速に取り込むQueryTableオブジェクトの使い方についてシリーズでお伝えしております。

前回の記事はこちら

エクセルVBAのQueryTableオブジェクトのプロパティの基本とCSVをカンマ区切りする方法
エクセルVBAのQueryTableオブジェクトでCSVを取り込む際はプロパティ指定が必須です。今回はQueryTableオブジェクトのプロパティの基本と、カンマ区切りでCSVを取り込む方法をお伝えします。

プロパティの基本と、CSVを取り込む第一歩として、「カンマ区切りで取り込む方法」をお伝えしました。

今回は、UTF-8のCSVを取り込んだり、文字列型に指定して取り込んだり…あらゆる種類のCSVに対応できる、QueryTableオブジェクトのプロパティを、更に詳細に説明していきます。

CSVの取り込みを更に便利にするプロパティ

QueryTableオブジェクトは、複雑なロジックを組まなくてもあらかじめ用意されているプロパティを駆使することで、非常にシンプルなソースコードでCSVの取り込みが可能です。
ではどんなプロパティがあるのか?見ていきましょう!

CSVを項目の型を指定して取り込む

エクセルでCSVを開くと

・16桁以上の数字は『1.2346E+10』など、不明な表記に変換されてしまう
・先頭のゼロが消えてしまう
・勝手に日付と判定される

など、意図せぬ編集を自動的にされてしまう場合があります。

過去の記事で紹介しております。

CSVとは何か?そしてエクセルでCSVファイルを扱うときの注意点
初心者向けエクセルVBAでCSVファイルを取り込む方法丁寧に解説をしていきたいと思います。初回は準備編としてそもそもCSVファイルとは何か、エクセルで開く際の注意点についてお伝えします。

例えば、以下のCSVは、すべてが取り込み時に化けてしまうことになります。

CSV化けてしまう

エクセルに取り込んでみました。すべて化けてしまっています。
エクセルに取り込むと化ける

その場合は、「文字列」で型指定をすることで、化けずに取り込むことが可能です。

使うのは以下のプロパティです。

QueryTableオブジェクト.TextFileColumnDataTypes

既定値:xlGeneralFormat(1を指定してもOKです。)

Array(1列目の型, 2列目の型, 3列目の型・・・)というように配列で列の型を指定します。10列あれば、10列分指定してください。

定数指定、値指定の使い分け

定数か、値かのどちらかを指定することができます。列数が多くなると以下のようにソースコードが長くなってしまうので、値を指定することをおすすめします。

10列分を定数で指定する場合

10列分を値で指定する場合

値の方が明らかにスッキリしますね!

どのような定数や値があるか?は以下の表をご覧ください。よく使うのが太字にしているxlGeneralFormatとxlTextFormatです。

定数 説明
xlGeneralFormat(既定値) 1 一般
xlTextFormat 2 文字列
xlMDYFormat 3 MDY日付形式
xlDMYFormat 4 DMY日付形式
xlYMDFormat 5 YMD日付形式
xlMYDFormat 6 MYD日付形式
xlDYMFormat 7 DYM日付形式
xlYDMFormat 8 YDM日付形式
xlSkipColumn 9 スキップ(その列を取り込みたくない場合)
xlEMDFormat 10 EMD日付形式

実行結果

化けたCSVをTextFileColumnDataTypesプロパティで型を指定して取り込んでみます。

文字列型

文字列型で化けずに取り込むことに成功しました!

このプロパティを指定しなければ既定値の「一般」となりますので、文字列型で取り込みたい項目がなければ省略しても問題ありません。

前回の記事でも紹介しましたが、TextFileColumnDataTypesプロパティは、テキストファイルウィザード上だと以下の場所です。
QueryTableプロパティ3

その他のプロパティについて

通常は省略してOKですが、CSVにはたくさんの種類があります。知っておくと場合によっては便利なプロパティを紹介します。

今回紹介する4つのプロパティを表した、テキストファイルウィザードの画面を以下に紹介します。ご覧いただきながら説明を読んでいただくとわかりやすいでしょう。

QueryTableプロパティ1
画面2プロパティ説明2

CSVの取り込む開始行を指定する

QueryTableオブジェクト.TextFileStartRow

既定値:1

例えばCSVの先頭行に見出しがあり、見出しは取り込みたくない!というときに指定します。見出し行を取り込まず2行目から取り込みたい場合は2を指定します。CSVの1行目から取り込みたい場合は省略してOKです。

もし、不要な見出し行が存在していて、このプロパティを使わなかった場合はCSVを取り込んだあとに、先頭行を削除する処理を追加しなければなりません。

それを省くことができるので場合によってはとても便利になるでしょう。

ダブルクォーテーションなど引用符を指定する

QueryTableオブジェクト.TextFileTextQualifier

既定値:xlTextQualifierDoubleQuote

通常のループを使用したCSVの取り込みだと以下の記事のように引用符のダブルクォーテーションをReplaceで削除しますが、QueryTableオブジェクトの場合は、デフォルトでダブルクォーテーションを削除してくれます。

エクセルVBAでダブルクォーテーションで囲まれているCSVファイルを取り込む
エクセルVBAでCSVファイルを取り込む方法シリーズです。今回はデータがダブルクォーテーションで囲まれているパターンのCSVをエクセルVBAで取り込む方法についてお伝えしたいと思います。

デフォルトなので、通常はプロパティ指定自体を省略してOKです。とても親切!

シングルクォーテーションの場合や、エクセルに取り込んだときもダブルクォーテーションを残したい、という場合に指定してください。

定数 説明
xlTextQualifierNone 引用符なし
xlTextQualifierDoubleQuote ダブルクォーテーション
xlTextQualifierSingleQuote シングルクォーテーション

UTF-8形式などの文字コードを指定する

QueryTableオブジェクト.TextFilePlatform

既定値:932(Shift-JIS)

文字コードを指定します。

Shift-JIS以外を取り込む場合は下記の値を指定してください。

文字コード
Shift-JIS 932
UTF-8 65001
UTF-16 1200

デフォルトだと化けてしまうUTF-8のCSV。TextFilePlatformプロパティを使えば、文字化けせずに取り込むことが可能です。

Shift-JISのCSVの場合は、プロパティ自体省略可能ですが、例えばUTF-8のファイルを取り込む場合は以下の通り記述してください。

ちなみに、通常のループ使用のCSV取り込みだと以下の通り対応します。

文字化けよさようなら!エクセルVBAでUTF-8のCSVを読み込む方法
エクセルVBAのLine Input命令でUTF-8のCSVファイルを取り込むと文字化けを起こします。今回は、ADODB.Streamを使ってUTF-8のCSVファイルを取り込む方法についてお伝えします。

区切り文字の形式を指定する

QueryTableオブジェクト.TextFileParseType

既定値:xlDelimited

データの形式を指定します。CSVの場合は、区切りデータなので既定値(省略可)でOKです。

もし、固定長のファイルを取り込みたい場合はxlFixedWidthを使用してください。

定数 説明
xlDelimited カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ
xlFixedWidth スペースによって右または左に揃えられた固定長フィールドのデータ

まとめ

以上、様々な種類のCSVを取り込む際に知っておくと便利なQueryTableオブジェクトのプロパティを紹介しました!使えそうなのはありましたか?

他にもたくさんのプロパティが存在しますので、興味のある方は調べてみてください!

QueryTable プロパティ (Excel)

次回は、3つめのメソッド(メソッドはこれで最後です)である、QueryTable.Deleteメソッドを紹介します。

このメソッドは記述を忘れても正常にCSVを取り込めたように見えますが、忘れたら大変なことになります。

QueryTableオブジェクトによるCSV取り込みの最終処理として非常に重要となるメソッドです。どうぞお楽しみに!

連載目次:エクセルVBAのQueryTableで高速にCSVを取り込む

一般的なループを使用したCSV取り込みよりも、更に高速にCSVを取り込むことのできるQueryTableオブジェクトに関する連載です。サイズの大きなCSVを取り込む際に非常に便利です。
  1. エクセルVBAでQueryTableオブジェクトを使って高速にCSVを取り込む方法
  2. エクセルVBAのQueryTables.Addメソッドでクエリテーブルを作成する方法
  3. エクセルVBAのQueryTable.RefreshメソッドでCSVをシートに出力する方法
  4. エクセルVBAのQueryTableオブジェクトのプロパティの基本とCSVをカンマ区切りする方法
  5. エクセルVBAのQueryTableオブジェクトであらゆる種類のCSVを取り込む方法
  6. エクセルVBAで高速にCSVを取り込むQueryTableオブジェクトの最終処理
  7. エクセルVBAでCSVを高速に取り込むQueryTableオブジェクト使用時の注意点

  投稿者プロフィール

フジタニ
フジタニLibArt代表
複業エンジニアとして「フリーランス」、「会社員」の2つの視点で業務効率化を提案。VBAによる自動化はもちろん、ビジネスチャットの推進(電話が大嫌い。)などを日々行っています。自転車が大好きな、走れるエンジニアです!

コメント