【本日のミッション】
Excel VBA ダイアログボックスで選択したcsvファイルをテキストファイルとして開いて、そのデータを取り込め。
■Open ステートメント使用
■「ゼロ落ち」(0から始まる文字列の0が消える)対応
■「日付変換」(文字列が日付に変換される)対応
■「桁落ち」(桁数の多い数値の16桁以降0になる)対応
■カンマ「,」ダブルクォーテーション「”」対応
■処理速度が速い
目次
ミッションの概要
Excel VBA ダイアログボックスで選択したcsvファイルをテキストファイルとして開いて、そのデータを取り込め、というのが今回のミッションです。
今回挑戦するのは「Open ステートメント」を使ったcsvの取り込みです。前回の記事で対応できなかった、カンマ「,」ダブルクォーテーション「”」にも対応しています。
前回参考:Excel VBAダイアログボックスで選択したcsvファイルをテキストファイルとして取込(ゼロ落ち・日付変換対応)
■Open ステートメント使用
■「ゼロ落ち」(0から始まる文字列の0が消える)対応
■「日付変換」(文字列が日付に変換される)対応
■「桁落ち」(桁数の多い数値の16桁以降0になる)対応
■カンマ「,」ダブルクォーテーション「”」対応
■処理速度が速い
↓カンマ区切りのcsvを、Open ステートメントを使ってデータ取り込みを行います。
【処理の流れ】
- ダイアログからcsvファイルを選択。
- Open ステートメントで、選択したcsvの入力モードを有効にし、ファイル番号を「1」と設定。
- ファイル番号「1」に設定したファイルの1行目から順に、データを変数「Rcd」に入れる。
- 変数「Rcd」の区切り文字のカンマ「,」を「@」に変換する。
- 変数「Rcd」を「@」で分割して、配列ary_dに入れる。
- 配列ary_dのデータをセルに入れる。
- ファイル番号「1」を閉じる。
プロシージャ
Sub CSV取込3() '【変数】 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の区切り文字のカンマを「@」に変換 Call 変換(Rcd) '■変数Rcdを「@」で分割して、配列ary_dに分けて入れる ary_d = Split(Replace(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 Sub 変換(Rcd As String) '【変数】 Dim R As String '変数Rcdから取り出した1文字 Dim dq As Long 'ダブルクォーテーションカウント Dim L As Long ’文字取り出し位置 '■変数Rcdの左から1文字ずつ文字を取り出す For L = 1 To Len(Rcd) '■変数Rcdの1文字を変数Rに入れる R = Mid(Rcd, L, 1) '■変数Rがダブルクォーテーション「"」の場合 If R = """" Then '■ダブルクォーテーションカウント「dq」+1 dq = dq + 1 '■変数Rがカンマ「,」の場合 ElseIf R = "," Then '■ダブルクォーテーションカウント「dq」が偶数の場合 If dq Mod 2 = 0 Then '■RcdのL番目の文字を「@」に変換 Rcd = Left(Rcd, L- 1) & "@" & Right(Rcd, Len(Rcd) - L) End If End If Next L End Sub
CSVとは
Comma-Separated Values の略称です。
データがカンマ「,」で区切られている、拡張子が「csv」のファイルです。
カンマ「,」で区切られたデータ1行で1レコードとなり、レコードとレコードの間は改行で区切られます。
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
callステートメント
プロシージャ内での処理中に、他のSubプロシージャやFunctionプロシージャを呼び出して処理を行うのが、Callステートメントです。
Call 呼び出し先プロシージャ名(引数)
「Call」は省略して、プロシージャ名だけでも処理してくれるのですが、後から見たとき、これナニ?ってならないように私は「Call」を付けるようにしています。
呼び出し先プロシージャで引数が必要ない場合は「(引数)」は不要です。
このプロシージャですが、「Call」を付ける場合には、引数を「( )」で囲む必要があります。逆に「Call」を省略する場合には、引数の前後にカッコ「( )」を付けてはいけません。
今回は、「変換」プロシージャを呼び出し、引数に変数「Rcd」を指定しています。
Call 変換(Rcd)
参考:Callステートメントとは 引数 括弧()の使い方(Excel VBA)
参考:参照渡し「ByRef」と値渡し「ByVal」の違い(Excel VBA)
区切り文字のカンマ「,」を「@」に変換
変数「Rcd」に含まれるカンマ「,」を「@」に変換したいだけなのですが、ダブルクォーテーション「”」も含まれていて、処理が煩雑なので、「変換」プロシージャとして別に分けました。
変数Rcdの左から1文字ずつ文字を取り出して、変数Rに入れる
ファイル番号「1」のcsvの2行目を処理している場合、変数「Rcd」には「01-01,山田 太郎,2023/5/3,01,”x,y,z”,”1,200″」が入っています。この文字列の左の一文字目から順に、文字の種類を確認し、種類によって処理内容を変えます。
For L = 1 To Len(Rcd) R = Mid(Rcd, L, 1) Next L
【例:変数L= 6 の時の処理】
変数「Rcd」の左から6文字目のカンマ「,」が、変数Rに入ります。
Len関数
引数に指定した文字列の文字数を数えます。半角・全角どちらも1文字として数えます。
Len(文字列)
引数名 | 説明 | 省略 |
文字列 | 文字数を調べる文字列を指定します。 | × |
今回の場合、変数Rcdに「01-01,山田 太郎,2023/5/3,01,”x,y,z”,”1,200″」が入っていると「39」を返します。
Len(Rcd)
Mid関数
Mid関数は、ある文字列(String)の任意の位置(Start)から指定した文字数分(Length)の文字列を返します。
Mid (String , Start , Length)
引数名 | 説明 | 省略 |
String | 文字列を取り出す、元の文字列式を指定します。 | × |
Start | 引数 String の先頭の位置を 1 として、どの位置から文字列を取り出すかを先頭からの文字数で指定します。 Start が String の文字数を超える場合、Mid 関数は長さ 0 の文字列 (“”) を返します。 | × |
Length | 取り出す文字数を指定します。 省略した場合、または文字列内に Length より短い文字数しかない場合には、Start から後の全ての文字を返します。 | ○ |
今回、変数「Rcd」が「01-01,山田 太郎,2023/5/3,01,”x,y,z”,”1,200″」、変数「L」が6、文字数が「1」の場合は、変数Rにカンマ「,」が入ります。
R = Mid(Rcd, L, 1)
ダブルクォーテーション「”」の出現回数を数えて、処理中のカンマが「区切り文字」か「テキストの一部」か判断
変数Rがダブルクォーテーション「”」の場合、変数「dq」に1を加え、ダブルクォーテーション「”」の出現回数を数えます。
If R = """" Then dq = dq + 1
変数Rがカンマ「,」で、ダブルクォーテーション「”」の出現回数が偶数(ゼロ)の場合、変数Rcdの「L」番目の文字(カンマ「,」)を「@」に変換します。奇数の場合は変換しません。
ElseIf R = "," Then If dq Mod 2 = 0 Then Rcd = Left(Rcd, L - 1) & "@" & Right(Rcd, Len(Rcd) - L)
Split関数
Split関数は文字列(Expression)を、指定した区切り文字(Delimiter)で分割した 1 次元配列を返します。
Split (Expression , Delimiter , Limit , Compare)
引数名 | 説明 | 省略 |
Expression | 区切り文字を含む文字列式を指定します。 | × |
Delimiter | 文字列を区切る、区切り文字を指定します。 省略すると、区切り文字にスペース (” “) が使用されます。 | ○ |
Limit | 返す配列の要素数を指定します。 -1(既定値)にすると、全ての文字列を含んだ配列を返します。 | ○ |
Compare | 文字列式を比較するモードを指定します。 省略すると、Option Compareステートメントの設定で比較します。 「vbBinaryCompare」バイナリ モードで比較を行います。 「vbTextCompare」テキスト モードで比較を行います。 テキストモードは全角半角・大文字小文字の区別をしません | ○ |
今回はこのような形で使用しています。変数Rcdは置換(Replace)処理を省いて記載しています。
Split(Rcd, "@")
2行目データの場合、第1引数の区切り文字を含む文字列(Expression)は、変数「Rcd」にセットしている
01-01@山田 太郎@2023/5/3@01@”x,y,z”@”1,200″
という文字列になります。
第2引数の区切り文字列(Delimiter)は
@ ←アットマーク
です。
01-01@山田 太郎@2023/5/3@01@“x,y,z”@“1,200”
が区切り文字「@」で分割されて一次元配列を返すと、配列「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ファイルをテキストファイルとして取込(ゼロ落ち・日付変換対応)
Excel VBAダイアログボックスで選択したcsvをテキストファイルとして取込(「,」「”」ゼロ落ち・日付変換対応)