Excel VBAダイアログボックスで選択したcsvをテキストファイルとして取込（「,」「”」ゼロ落ち・日付変換対応）

【本日のミッション】

Excel VBA ダイアログボックスで選択したcsvファイルをテキストファイルとして開いて、そのデータを取り込め。

■Open ステートメント使用
■「ゼロ落ち」（0から始まる文字列の0が消える）対応
■「日付変換」（文字列が日付に変換される）対応
■「桁落ち」（桁数の多い数値の16桁以降0になる）対応
■カンマ「,」ダブルクォーテーション「”」対応
■処理速度が速い

ミッションの概要

Excel VBA ダイアログボックスで選択したcsvファイルをテキストファイルとして開いて、そのデータを取り込め、というのが今回のミッションです。

今回挑戦するのは「Open ステートメント」を使ったcsvの取り込みです。前回の記事で対応できなかった、カンマ「,」ダブルクォーテーション「”」にも対応しています。

前回参考：Excel VBAダイアログボックスで選択したcsvファイルをテキストファイルとして取込（ゼロ落ち・日付変換対応）

■Open ステートメント使用
■「ゼロ落ち」（0から始まる文字列の0が消える）対応
■「日付変換」（文字列が日付に変換される）対応
■「桁落ち」（桁数の多い数値の16桁以降0になる）対応
■カンマ「,」ダブルクォーテーション「”」対応
■処理速度が速い

↓カンマ区切りのcsvを、Open ステートメントを使ってデータ取り込みを行います。

Excel VBAダイアログボックスで選択したcsvをテキストファイルとして取込（「,」「”」ゼロ落ち・日付変換対応）

【処理の流れ】

ダイアログから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

ファイルを開く時のモードを、キーワードで指定します。

キーワード	モード
Input	シーケンシャル入力モード（読み込み）
Output	シーケンシャル出力モード（書き込み）
Append	追加モード（追加）
Binary	バイナリモード（テキスト以外のデータ）
Random	ランダムアクセスモード（データベースにアクセス）（既定値）

Access

ファイルを開く時の処理を、キーワードで指定します。

キーワード	内容
Read	読み込み
Write	書き込み
Read Write	読み書き込み

〇

lock

開いたファイルに対する、他プロセスからの操作制御をキーワードで指定します。

キーワード	内容
Shared	共用
Lock Read	読み込みロック
Lock Write	書き込みロック
Lock Read Write	読み書き込みロック

〇

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) で開いたファイルから１行全体を読み込み、文字列型 (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

【参考】

■■■スポンサーリンク■■■