【本日のミッション】
Excel VBA ダイアログボックスで選択したcsvファイルをテキストファイルとして開いて、そのデータを取り込め。
■Open ステートメント使用
■「ゼロ落ち」(0から始まる文字列の0が消える)対応
■「日付変換」(文字列が日付に変換される)対応
■「桁落ち」(桁数の多い数値の16桁以降0になる)対応
■処理速度が速い
■カンマ「,」ダブルクォーテーション「”」非対応
目次
ミッションの概要
Excel VBA ダイアログボックスで選択したcsvファイルをテキストファイルとして開いて、そのデータを取り込め、というのが今回のミッションです。
今回挑戦するのは「Open ステートメント」を使ったcsvの取り込みです。前回のWorkbooks.Open メソッドで処理するよりも難しいですが、処理は速くなります。
前回参考:Excel VBAダイアログボックスで選択したcsvファイルをエクセルブックとして開いて取込(「,」「”」対応)
■Open ステートメント使用
■「ゼロ落ち」(0から始まる文字列の0が消える)対応
■「日付変換」(文字列が日付に変換される)対応
■「桁落ち」(桁数の多い数値の16桁以降0になる)対応
■処理速度が速い
■カンマ「,」ダブルクォーテーション「”」非対応
非対応の文字がありますが、csv取込後のデータを確認し、おかしいところがないか確認し、問題なければOKです♪
↓カンマ区切りのcsvを、Open ステートメントを使ってデータ取り込みを行います。
【処理の流れ】
- ダイアログからcsvファイルを選択。
- Open ステートメントで、選択したcsvの入力モードを有効にし、ファイル番号を「1」と設定。
- ファイル番号「1」に設定したファイルの1行目から順に、データを変数「Rcd」に入れる。
- 変数「Rcd」をカンマ「,」で分割して、配列ary_dに入れる。
- 配列ary_dのデータをセルに入れる。
- ファイル番号「1」を閉じる。
プロシージャ
Sub CSV取込2()
'【変数】
Dim fn As Variant '取込ファイル名
Dim ws As Worksheet 'データ貼付用シート(このブックのsheet1)
Dim Rcd As String 'レコードデータ用
Dim ary_d '区切りレコードデータ用配列
Dim t_row As Long '対象行
Dim t_col As Long '対象列
'■変数セット
Set ws = ThisWorkbook.Worksheets("Sheet1") 'データ貼付用シート(このブックのsheet1)
'■ファイルの選択
fn = Application.GetOpenFilename(FileFilter:="CSVファイル(*.csv),*.csv")
'■ファイル選択の確認
If fn = False Then
Exit Sub 'ファイル選択されていなかったら終了
End If
'■データ貼付用シート 書式設定 文字列に変更
ws.Range("A:A,D:D").NumberFormatLocal = "@"
'■csvファイル「fn」の入力モードを有効にし、ファイル番号を「1」とする
Open fn For Input As #1
'■処理対象行の初期値=0
t_row = 0
'■ファイル番号「1」の1行目から最後行(EOF(1)=True)まで処理を繰り返す
Do Until EOF(1)
'■ファイル番号「1」の1行分のデータを変数Rcdに入れる
Line Input #1, Rcd
'■処理対象行
t_row = t_row + 1
'■変数Rcdをカンマ「,」で分割して、配列ary_dに分けて入れる
ary_d = Split(Rcd, ",")
'■配列ary_dのデータを順にデータ貼付用シート(このブックのsheet1)に入れる
For t_col = 0 To UBound(ary_d)
ws.Cells(t_row, t_col + 1) = ary_d(t_col)
Next
Loop
'■ファイル番号「1」のファイルを閉じる
Close #1
End Sub
CSVとは
Comma-Separated Values の略称です。
データがカンマ「,」で区切られている、拡張子が「csv」のファイルです。
カンマ「,」で区切られたデータ1行で1レコードとなり、レコードとレコードの間は改行で区切られます。
csvをOpen ステートメントで開く時のデメリット
今回のcsv取込方法は、どのcsvでも使えるわけではありません。
カンマ「,」ダブルクォーテーション「”」を含む文字列の区切りがおかしくなる
例えば、こんなcsvファイルを取り込んでみると・・・・
↓取り込んだ後のデータが、まったく違うものになっています。
上記csvを見ると、カンマ「,」を含む文字列「x,y,z」や「1,200」等の数字が「”」ダブルクォーテーションで囲まれていますね。このようなデータは、今回の方法で読み込むと、全てのカンマ「,」部分で区切られてしまいます。
GetOpenFilenameメソッド
GetOpenFilenameメソッドは、「ファイルを開く」ダイアログボックスを表示させ、選択したファイルのフルパスを返します。
Application.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)
引数名 | 説明 | 省略 |
---|---|---|
FileFilter | ファイルの種類・拡張子を指定する文字列(ファイルフィルター文字列)を指定します。 省略すると “すべてのファイル (*.*),*.*”になります。 | 〇 |
FilterIndex | 引数FileFilterで指定したファイルフィルター文字列の中で、1から何番目のFileFilterを既定値とするかを指定します。 省略または、引数FileFilter文字列の数より大きい数値を指定すると、最初のFileFilter文字列が既定値となります。 | 〇 |
Title | ダイアログボックスのタイトルを指定します。 省略すると “ファイルを開く” になります。 | 〇 |
ButtonText | Macintosh でのみ指定できます | 〇 |
MultiSelect | True:複数のファイルを選択できます。 False:1 つのファイルしか選択できません。(規定値) | 〇 |
今回は引数FileFilterのみを使用し、他の引数は省略しています。
Application.GetOpenFilename(FileFilter:="CSVファイル(*.csv),*.csv")
FileFilter
ファイルの種類・拡張子を指定する文字列です。
省略すると “すべてのファイル (*.*),*.*”になりますが、選択するファイルの種類が決まっているなら、ダイアログに表示されるファイルが限定されるので、ファイルを選択しやすくなります。
ファイルの種類1つにつき、下記2つの情報をセットにして指定します。
指定するときは、2つの情報の間にカンマ「,」を入れます。
参照:ワイルドカードとは。使い方いろいろ。(Excel)
- ファイルの種類を指定する文字列
- ファイルの拡張子
Application.GetOpenFilename("ファイルの種類を指定する文字列,ファイルの拡張子")
Application.GetOpenFilename("Microsoft Excelブック,*.xls*")
1つのファイルの種類に、複数の拡張子を表示する場合は、拡張子の間にセミコロン「;」を入れます。
Application.GetOpenFilename("テキストファイル,*.txt;*.csv")
複数のファイルの種類を表示する場合は、「ファイルの種類,拡張子」のセットをカンマ「,」で区切ります。カンマだらけになってしまいます。
Application.GetOpenFilename("ファイルの種類,拡張子,ファイルの種類,拡張子,ファイルの種類,拡張子")
Application.GetOpenFilename("Microsoft Excelブック,*.xls*,テキストファイル,*.txt,テキストファイル,*.csv")
もしも、こんなおふざけな指定をしてしまっても・・・
Application.GetOpenFilename("えくせる,*.xls*,てきすと,*.txt;*.csv")
指定した通りの内容で表示されます。
でも、かっこよく指定したい!という場合は、Excelの「ファイルを開く」ダイアログボックスのファイルの種類の文字をマネしておきましょう。誰からも突っ込まれないでしょう。
「ゼロ落ち」「日付変換」対応に書式設定を文字列に変更
Open ステートメントで、今回のテキストをそのまま開くと「ゼロ落ち」「日付変換」が起こります。
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
A列のID「01-01」が日付「1月1日」に変換されます。
D列の購入商品が、「0」始まりの文字なのに、「0」が抜け落ちています。
これを防ぐために、あらかじめセルの書式設定を文字列にしておきます。
ws.Range("A:A,D:D").NumberFormatLocal = "@"
こうしておくことで、csvデータを読み込んだ際、正しいデータ表示になります。
Open ステートメント
ファイルへの入出力を有効にします。つまりファイルを開いている状態にして、Excelとのデータやり取りを可能にしています。
Open pathname For mode [Access access] [lock] As [#]filenumber
今回はこのような感じで使用しています。
引数 | 内容 | 省略 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
pathname | ファイル名を指定します。フルパスではなく、ファイル名だけを指定した場合、カレントフォルダのファイルとして処理されます。 また、指定したファイルが存在しない場合、引数modeに「Input」以外のモードが指定されている場合、指定したファイルが新規で作成されます。 | × | ||||||||||||
mode | ファイルを開く時のモードを、キーワードで指定します。
| × | ||||||||||||
Access | ファイルを開く時の処理を、キーワードで指定します。
| 〇 | ||||||||||||
lock | 開いたファイルに対する、他プロセスからの操作制御をキーワードで指定します。
| 〇 | ||||||||||||
filenumber | 開いたファイルに割り当てる「ファイル番号」を1 ~ 511 の範囲で指定します。ファイル番号の前の「#」は省略可能です。 | × |
引数「filenumber」に「#1」を指定する理由
引数「filenumber」に「1」「2」「3」・・・と「ファイル番号」を指定することで、「1」のファイルを読み込んでください、「2」のファイルを閉じてください、とファイルを判別して処理を行うことができます。
ただし、今回のように使用するファイルが1つの場合は「1」を使用して問題ありません。
↓ちなみに、「#」は省略可能です。
Open fn For Input As 1
EOF関数
EOF関数は、Openステートメントのシーケンシャル入力モード(Input)またはランダムアクセスモード(Random)で開いたファイルの、現在位置がファイルの末尾に達している場合、「True」を返します。
つまり、ファイルを1行目から処理していって、最後の行の処理になったら「True」を返してデータ処理の終了を知らせてくれます。
EOF(filenumber)
filenumber にはOpen ステートメントで指定した「filenumber」を指定します。
EOF関数の引数「filenumber」の前には「#」を付けてはいけません。
今回は「1」を指定しているので、下記の通りになります。
EOF(1)
Do Until~Loop
Do Until 条件 ~★★★~ Loop
Do Until は、条件を満たすまで「~★★★~」の処理を繰り返します。
今回はEOF(1)が「True」になるまで「~★★★~」の処理を繰り返しています。
つまり、EOF(1)が「True」になったら、繰り返し処理を終了します。
Do Until EOF(1) ~★★★~ Loop
Line Input # ステートメント
OPENステートメントのシーケンシャル入力モード (Input) で開いたファイルから1行全体を読み込み、文字列型 (String) の変数に入れます。
1行分のデータを読み込むと、読み込み対象行を次の行に移します。
Line Input #filenumber, varname
指定項目 | 内容 | 省略 |
filenumber | 任意の有効なファイル番号です。ファイル番号の前には必ず「#」を付けます。 | × |
varname | 有効な変数または文字列の変数名です。 | × |
filenumberは、Open ステートメントで指定した「ファイル番号」を指定します。
今回はファイル番号に「1」、変数に「Rcd」を指定しているため、下記の通りになります。
Line Input #1, Rcd
Split関数
Split関数は文字列(Expression)を、指定した区切り文字(Delimiter)で分割した 1 次元配列を返します。
Split (Expression , Delimiter , Limit , Compare)
引数名 | 説明 | 省略 |
Expression | 区切り文字を含む文字列式を指定します。 | × |
Delimiter | 文字列を区切る、区切り文字を指定します。 省略すると、区切り文字にスペース (” “) が使用されます。 | ○ |
Limit | 返す配列の要素数を指定します。 -1(既定値)にすると、全ての文字列を含んだ配列を返します。 | ○ |
Compare | 文字列式を比較するモードを指定します。 省略すると、Option Compareステートメントの設定で比較します。 「vbBinaryCompare」バイナリ モードで比較を行います。 「vbTextCompare」テキスト モードで比較を行います。 テキストモードは全角半角・大文字小文字の区別をしません | ○ |
今回はこのような形で使用しています。
Split(Rcd, ",")
2行目データの場合、第1引数の区切り文字を含む文字列(Expression)は、変数「Rcd」にセットしている
01-01,山田 太郎,2023/5/3,01,x
という文字列になります。
第2引数の区切り文字列(Delimiter)は
, ←カンマ
です。
01-01,山田 太郎,2023/5/3,01,x
が区切り文字「,」で分割されて一次元配列を返すと、配列「ary_d」に下記の通り格納されます。
Close #ステートメント
Open ステートメントを使用して開いたファイルへの入出力を終了します。つまりファイルを閉じた状態にして、Excelとのデータやり取りを終了します。
Close [#]filenumberlist
指定項目 | 内容 | 省略 |
filenumberlist | 任意の有「ファイル番号」を指定します。複数のファイル番号を指定する場合は、ファイル番号をカンマで区切ります。 省略すると、Open ステートメントで開いた全てのファイルを閉じます。ファイル番号の前の「#」は省略可能です。 | 〇 |
今回は「ファイル番号」の「1」を閉じるため、下記の通りとなります。
Close #1
【参考】
指定したフォルダ内のファイル名全てを取得(Excel VBA)
変数でよく使われる「buf」「tmp」の意味
Dir関数が取得するファイルの順番
指定したフォルダ内のフォルダ名全てをGetAttrを使って「エラー53 ファイルが見つかりません。」を出さずに取得(Excel VBA)
GetAttrとは?「= vbDirectory」ではなく「And vbDirectory」となるビット演算の疑問
フォルダ名だけを取得したい時に出てくる 「.」 と 「..」 とは?
指定したフォルダ内とサブフォルダ内全てのファイル名を取得(Excel VBA)
CreateObject(“Scripting.FileSystemObject”) を使ってサブフォルダを取得
再帰処理とは?フォルダ内とサブフォルダ内全てのファイル名を取得(Excel VBA)
指定したフォルダ内のサブフォルダのフォルダ名を全部取得(Excel VBA)
再帰処理とは?フォルダ内のサブフォルダのフォルダ名を全部取得(Excel VBA)
参照渡し「ByRef」と値渡し「ByVal」の違い(Excel VBA)
ファイルのフルパスからファイル名のみを取得 InStrRev関数(Excel VBA)
指定したフォルダの全ての階層のフォルダ名・サブフォルダ名・ファイル名を取得(Excel VBA)
FileSystemObjectとは?CreateObject 関数 FolderExists・GetFolderの使い方
FileSystemObject CreateObject関数を使う方法・ 参照設定を使う方法 違いを理解してエラー防止
再帰処理とは?指定したフォルダの全ての階層のフォルダ名・サブフォルダ名・ファイル名を取得(Excel VBA)
ファイルのフルパスからファイル名のみを取得 Split関数(Excel VBA)
Callステートメントとは 引数 括弧()の使い方(Excel VBA)
指定したフォルダ内から「特定の文字を含まないファイル名」を取得(Excel VBA)
ワイルドカードとは。使い方いろいろ。(Excel)
Dir関数の使い方。ファイル名やフォルダ名の取得方法。(Excel VBA)
指定したフォルダのファイル名を取得し、そのファイル名を一括で変換(Excel VBA)
Excel起動時に「コンパイルエラー」。64ビット システムで Declareステートメントに、PtrSafe属性を設定(Excel VBA )
「ファイルを開く」ダイアログボックス から ファイル名を取得(Excel VBA)
「ファイルを開く」ダイアログボックス から 複数 ファイル名を取得(Excel VBA)
指定フォルダ内のサブフォルダ全てをフォルダ構成のみ(空フォルダ)を別フォルダにコピー(Excel VBA)
再帰処理とは?指定フォルダ内のサブフォルダ全てをフォルダ構成のみ(空フォルダ)を別フォルダにコピー(Excel VBA)
エクセル ファイルのフルパスから拡張子のみを取得 Split関数(Excel VBA)
エクセル ファイルのフルパスから拡張子のみを取得 InStr関数(Excel VBA)
Excel VBAダイアログボックスで選択したcsvファイルをエクセルブックとして開いて取込(「,」「”」非対応)
Excel VBAダイアログボックスで選択したcsvファイルをテキストファイルとして取込(ゼロ落ち・日付変換対応)