【ACCESS VBA】Amazon QuickSightから出力したCSVのインポート

やりたいこと

Amazon QuickSightからエクスポートしたCSVファイルをVBAを使用してACCESSにインポートしたい！

いつもどおりにインポートしてみた

ACCESS VBAでのテキストインポートといえば DoCmd.TransferText メソッド ですね。

Access VBA

Sub インポート_Click()

    Dim Path As String: Path = "C:\Users\〇〇\Documents\test.csv"
    DoCmd.TransferText acImportDelim, , "import", Path, True, , 65001

End Sub

こんな感じですね。
Pathに置かれたCSVファイルを「import」テーブルにインポートするコードです。

ちなみにAmazon QuickSightのCSVはUTF-8 BOM付きファイルなので
DoCmd.TransferText メソッドのCodePage識別子で「65001（UTF-8）」を指定しています。

うまくいかない...

DoCmd.TransferText メソッドを使用したコードを実行したところ
フィールド名をうまくインポートすることができませんでした。

なんで？

今回インポートしたのは下記のようなテストデータです。
Amazon QuickSightから出力するCSVファイルはフィールド名とレコードが
ダブルクォーテーション(")で囲われています。

このダブルクォーテーションが邪魔してうまくフィールド名をインポートできなかったみたいです。

対処法

色々試しましたが、下記の方法でうまくいきました。

Access VBA

Sub インポート_Click()

    Dim Path As String: Path = "C:\Users\〇〇\Documents\test.csv"
    Dim db As Database: Set db = CurrentDb
    Dim Newtb As TableDef
    Dim fldCnt As Integer: fldCnt = 0
    Dim fld As Field
    Dim fldArr() As Variant
    Dim buf As String
    Dim tmpfld As Variant

    '➀ CSVをimportテーブルに取込'
    DoCmd.TransferText acImportDelim, , "import", Path, True, , 65001  'UTF-8'
    
    Set Newtb = db.TableDefs("import")  'importテーブルをNewtbに設定'
    '➁ 現在のフィールド名をリストに取込'
    For Each fld In Newtb.Fields
        ReDim Preserve fldArr(fldCnt)
        fldArr(fldCnt) = fld.Name       'フィールド名をfldArrリストに取込'
        fldCnt = fldCnt + 1
    Next

    'CSVファイルを開く'
    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8"
        .Open
        .LineSeparator = 10             '改行コード：LF'
        .LoadFromFile Path

        '➂CSVファイルの1行目（フィールド名）をリストに入れる'
        buf = .ReadText(-2)             '一行目をbufに取込'
        tmpfld = Split(buf, ",")        'カンマで分割してtmpfldリストに取込'

        '➃ importテーブルのフィールド名を変更する'
        For fldCnt = 0 To UBound(tmpfld, 1)
            Newtb.Fields(fldArr(fldCnt)).Name = Replace(tmpfld(fldCnt), """", "")
        Next
       .Close
    End With
    
    MsgBox ("インポート完了")
    
End Sub

ステップとしては
➀DoCmd.TransferText メソッドを使用してCSVファイルを取り込む
　（この時点ではフィールド名はうまくインポートできていないまま）
➁現在のフィールド名をリストに取り込み
➂CSVファイルの１行目だけをADODB.Streamを使用してリストに取り込み
➃➁で取得した現在のフィールド名を、➂で取得したCSVファイルのフィールド名に変換

とりあえずCSVファイルをインポートして、その後フィールド名を直すという流れですね。

上記のコードを使用してインポートをすると以下の通り。

想定通りにインポートできましたね。

まとめ

最初はどうしてフィールド名がうまくインポートできないのか分からず
BOM付きなのが原因なのかなと思ったのですが、ダブルクォーテーションがこんな風に邪魔しちゃうんですね。

ADODB.Streamを使用して１行目はフィールド名、それ以降はレコードに...
みたいなやり方を試したりしましたがうまくいかず、
DoCmd.TransferText メソッドでレコードは正しくインポートできていたので
今回のフィールド名だけを変更する方法に切り替えました。

ご案内

株式会社ジールでは、「ITリテラシーがない」「初期費用がかけられない」「親切・丁寧な支援がほしい」「ノーコード・ローコードがよい」「運用・保守の手間をかけられない」などのお客様の声を受けて、オールインワン型データ活用プラットフォーム「ZEUSCloud」を月額利用料にてご提供しております。
ご興味がある方は是非下記のリンクをご覧ください：
https://www.zdh.co.jp/products-services/cloud-data/zeuscloud/?utm_source=qiita&utm_medium=referral&utm_campaign=qiita_zeuscloud_content-area