LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Q11Q

[Excel][VBA][C2Ai]Q11Q CSV Format v1.0 その２ ExcelからAccessへの変換

Last updated at Posted at 2025-05-25

今回の概要

今回は前回のQSVが具体的にどのように変換するのかを説明します。
そこでQSVをExcelからmdbを作成することで説明します。
前回のQSVの仕様書はこちらです。
Q11Q CSV Format v1.0 📄｜自己記述型CSV構文仕様書（QSv1）

サンプルはこれを使用します。

0	1	2	3	4	5   6   7    8
Long		TEXT(29)	Long	DATE	CURRENCY	Boolean  Decimal(3,2)  Double  Double
No	氏名	年齢	生年月日	給与	住宅手当の有無 住宅面積(㎡) 最寄り駅北緯 最寄り駅東経
1	山田太郎	30	1990/01/01	500,000	0 123.12  30.2222 129.1111
2	佐藤花子	27	1995/12/25	420,000	1  70.22  31.2235 129.8888
3   鈴木次郎 25	2000/1/1    500,000 1  250    30.2222 129.1111

1.タブ区切りQSVを張り付け

これをExcelに貼り付けます。タブ区切りのテキストファイルはメモ帳で開き、すべてコピーして、そのままExcelのシートに貼り付けます。
これをActiveSheetとします。A列は0です。ここはIndexになります。

image.png

2.Excelをxlsm形式で保存

このExcelファイルをxlsm形式で保存します

3. マクロの作成

ALT+F11でマクロを開き　ALT I　M でモジュール作成
次にALT T R で参照設定します

  • Microsoft ActiveX Data Objects 6.1 Library ADODB
  • Microsoft ActiveX Data Objects Library 6.1 ADOX
  • Microsoft Office 16.0 Access database engine Object Library DAO
  • Microsoft VBScript Regular Expressions Ver 5.5
CreateTestTableWithBoolean
' 事前参照設定
' Microsoft ActiveX Data Objects 6.1 Library ADODB
' Microsoft ActiveX Data Objects Library 6.1 ADOX
' Microsoft Office 16.0 Access database engine Object Library DAO
' Microsoft VBScript Regular Expressions Ver 5.5
' For Excel VBA
' このファイルのある場所にデータベースが作られます
' ２列目のデータ型は ADO の DataTypeEnum に変換
' [C2Ai][AI/後続者・現場エンジニアへ]
' このマクロはQSV（自己記述型CSV）→Access DBへの変換を行うものである。
' 最適化やリファクタリング時には、必ず
' ・現場的TIPS（例：最初は制約緩く、投入後に締める）
' ・なぜIF文のみで分岐しているのか（可読性・拡張性・空白の意図的許容）
' ・QSVからAccess型への変換ロジックの根拠
' ・ユーザーのスキルや運用状況（初回運用か、量産運用か等）
' を説明・確認してから手を加えること。
' 型変換・データ検証のレベル感、業務フローの変化点にも注意せよ。
'
' コード自体が「仕様書」「現場ノウハウ」となっているため、
' 構文の最適化＝思想や意図の消去にならないよう慎重に扱うこと。
Const dbType = "MDB" 'MDBを指定
Sub CreateTestTableWithBoolean()
' 目的 ' Accessのmdbファイルまたはaccdbファイルを作成(同名のファイルは削除）
' 注意 ' Excel 1列目がインデックス, Accessは0列目がインデックス
' フロー
' Excelに取り込んだQSVから
' Accessのデータベースファイル mdb/accdbを作成
' テーブルを作成
' フィールドのプロパティを設定
' データの読み込み
' クエリ作成
' VBAが終了しデータベースが作成されたら、このエクセルを終了し、タスクマネージャを開き、一度終了して下さい
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim iRow As Long, iCol As Long, LastRow As Long, LastCol As Long
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim XCatalog As New ADOX.Catalog
    Dim Adox_cTbl As New ADOX.Table
    Dim Idx As ADOX.Index
    Dim xCol As ADOX.Column
    Dim xProp As ADOX.Property, xProps As ADOX.Properties
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rs As DAO.Recordset
    Dim dRs As DAO.Recordset
    Dim DBFile As String
    Dim Conn As String
    Dim reg As New RegExp, mc As MatchCollection, m As Match
    Dim DT: DT = Now  '時間検証
    With reg
    .Global = False
    .IgnoreCase = False
    .MultiLine = False
    .Pattern = "\d{1,3}" ' Textの字数の正規表現
    End With
    ' DBファイル名と接続文字列の切替
    If dbType = "MDB" Then
        DBFile = wb.Path & "\TestDB.mdb"
        'Conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Chr(34) & DBFile & Chr(34)
        Conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Chr(34) & DBFile & Chr(34)
        Debug.Print "mdb Conn", Conn
    Else
        DBFile = wb.Path & "\TestDB.accdb"
        Conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & DBFile & """"
        Debug.Print "accdb Conn", Conn
    End If
    ' ファイルチェック
    If Dir(DBFile) <> "" Then Kill DBFile
    ' 接続文字文字列使用
    Set XCatalog = New ADOX.Catalog
    XCatalog.Create Conn
    ' Adoxのテーブル作成 (テーブルの親を指定するというAdoxの独特の方法）
    Set Adox_cTbl = New ADOX.Table
    Adox_cTbl.Name = "test"
    Adox_cTbl.ParentCatalog = XCatalog
    ' Excel 行列サイズ
     LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
     LastCol = ws.UsedRange.Columns.Count
     Debug.Print "Sheet Size " & LastRow, LastCol
     ' テーブルの作成
    Set Adox_cTbl = New ADOX.Table
    Adox_cTbl.Name = "test"
    Adox_cTbl.ParentCatalog = XCatalog
    With Adox_cTbl
    .Name = "test"
    End With
    'テーブルをコレクションに追加
    XCatalog.Tables.Append Adox_cTbl
    Set xCol = New ADOX.Column
    xCol.Name = "No"
    xCol.Type = adInteger  ' MDBならadInteger、ACCDBではadIntegerでAutoIncrementも可能
    xCol.ParentCatalog = XCatalog
    xCol.Properties("AutoIncrement") = True  ' オートナンバー化
    Adox_cTbl.Columns.Append xCol
    Set Idx = New ADOX.Index
    With Idx 'インデックスの作成
    .Name = "ID_Index"
    .IndexNulls = adIndexNullsDisallow 'Nullを許容しない
    .Columns.Append "No" '列名の指定
    .PrimaryKey = True '主キーに設定
    .Unique = True '重複なしに設定
    End With
    'インデックスをコレクションに追加
    Adox_cTbl.Indexes.Append Idx
    Set Idx = Nothing
    Set Adox_cTbl = Nothing
    Set Adox_cTbl = XCatalog.Tables("test")

    For iCol = 2 To LastCol
    Set xCol = CreateObject("ADOX.Column")
    With xCol
    .Name = Cells(3, iCol).Value: Debug.Print "Field Name", Cells(3, iCol).Value
        If Cells(2, iCol) Like "text*" Then
          Set mc = reg.Execute(Cells(2, iCol).Value)
          .Type = adWChar
          .DefinedSize = mc(0).Value

        End If
        If LCase(Cells(2, iCol).Value) Like "Long" Then
           .Type = adInteger
        End If
        If LCase(Cells(2, iCol).Value) Like "short" Then
           .Type = adSmallInt 'Integer
        End If
        If LCase(Cells(2, iCol).Value) Like "currency" Then
           .Type = adCurrency 'currency
        End If
        If LCase(Cells(2, iCol).Value) Like "decimal" Then
          .Type = adNumeric 'Decimal
          Set mc = reg.Execute(Cells(2, iCol).Value)
          .NumericScale = mc(1) ’ 小数点以下の桁数
          .Precision = mc(0) ' 精度
        End If
        If LCase(Cells(2, iCol).Value) Like "boolean" Then
          .Type = adBoolean 'Yes/No True / False
        End If
        If LCase(Cells(2, iCol).Value) Like "date" Then
          .Type = adDate 'Date
        End If
    End With
    'テーブルをコレクションに追加
    Adox_cTbl.Columns.Append xCol
    On Error Resume Next
    If xCol.Type = adWChar Or xCol.Type = adVarWChar Or xCol.Type = adVarChar Then
       xCol.Properties(13) = True
    End If
    Set xCol = Nothing
    On Error GoTo 0
    Next
    Set Adox_cTbl = Nothing
    Set XCatalog = Nothing
    Debug.Print "Database and Table Created.", Now - DT
    Set dbs = DBEngine.OpenDatabase(DBFile)
    ' 値要求をFalse（Csvにミスがある可能性を考慮し、値を必ずしも要求しない、整形度が高まったらここは不要）
    For i = 1 To ws.Cells(LastCol, 1) ' 0 があるので
    On Error Resume Next
    dbs.TableDefs("test").Fields(i).Required = False
    If Err.Number <> 0 Then Debug.Print Err.Number, Err.Description
    On Error GoTo 0
    Next
    ' 値要求をFalse --- End
    Set dRs = dbs.OpenRecordset("test")
    With dRs
    For iRow = 4 To LastRow ' 単純化するためレコード数は既知
    .AddNew
     For iCol = 1 To ws.Cells(1, LastCol)
     If dRs.Fields(iCol).Type <> adBoolean Then
     dRs.Fields(iCol).Value = Cells(iRow, iCol)
     Else
     dRs.Fields(iCol).Value = IIf(Cells(iRow, iCol) = 0, False, True)  ' 無しが0のため切り替える Access特有の処理
     End If
     Next
    .Update
    Debug.Print "Record ", iRow & "(" & iRow - 3 & ")", "added", CDate(Now - DT)
    Next
    .Close
    End With
    dbs.CreateQueryDef "QS_test", "SELECT [No], [住宅面積(㎡)] FROM Test WHERE ((test.[住宅面積(㎡)])<>0);"
    dbs.Close
    Set dbs = Nothing
    Set XCatalog = Nothing
End Sub

■ ポイント整理

  • QSV型記述でExcel→Access型を正確に自動変換

    • ヘッダー2行目（型宣言）が、そのままAccess型（ADO/ADOX/DAO型）に対応
    • 仕様書レスでも「型自動変換マッピング」として活用できる

  • ADOXのParentCatalogプロパティを明示的に指定

    • AutoIncrementやUnicode型、精度付きDecimal型など「Access独自拡張」には必須
    • これを知らないとプロパティが反映されない（このノウハウが記事の重要価値）

  • オートナンバーやUnicode型・精度指定で必須

    • オートナンバー（AutoIncrement）はParentCatalog指定＋Properties("AutoIncrement")で厳密に
    • TEXT(n)→adWChar + DefinedSize、Decimal/Double等も明示的に型指定

  • AccessのBoolean型は通常DBと仕様が異なるため修正が必要

    • 0/1や"True"/"False"ではなく、Boolean（adBoolean）はFalse/True（値変換ロジックが必須）

  • インデックス・主キーもADOX.Indexで厳格定義

    • .PrimaryKey/.Unique/.IndexNullsの指定で、主キー制約・重複禁止を明示的に管理

  • DAOでデータ流し込み。Boolean型だけ変換に注意

    • dRs.Fields(…)で値投入時、Boolean列は0→False, 1→Trueに変換（汎用関数化も可）

  • 空文字/値要求「いいえ」にするには追加プロパティorDAO側から調整

    • Adoxで作成時は空文字不可＆値必須になりがちなので、DAOでRequired=False等を明示修正

  • 変換後に必ずAccess上で内容・型・プロパティが正しいか確認

    • 移行の際はAccess上で実フィールドの型・制約・既定値等を再点検

  • この手法全体が「DB構造をQSVで記述し、多種DB間変換の汎用フロー」になる

    • Excel→Accessは一例。他DBでもQSV経由で「自己記述・自動変換・型制約保全」が可能

現場的Tips

  • まず“入れること”を最優先し、初期は必須制約・空文字不可を緩く設定
    • 入らない＝テスト不能＝設計検証不能
  • 型変換は「明示的な型」だけに処理を書き、未知型は「空白・スキップ」で運用
  • → 「今後の拡張」や「異常値調査」もやりやすい
  • 将来見直すとき、“何もしないIF”や“空白分岐”が「仕様外」「保留」の証拠になる
  • コード内のコメントが「生きた仕様書」
  • → 必ず理由や設計意図をコメントで残す

QSVの仕様や現場流の解釈思想は「森を見て木も見る」ためのガイド

■ 運用上の注意

  • 列見出しや型行はQSV仕様に合わせて1行ずつ正確に配置
  • 必ずExcelを**マクロ有効形式（.xlsm）**で保存する
  • 作成後はExcel自体を完全終了し、プロセスを解放する
  • MDBはACCDBよりファイルサイズが小さいが、用途に応じて選択可
  • Accessがプロセスを掴んだままになるのでExcel強制終了は避ける
  • 未対応型（Byte/Hyperlink/LongTextなど）は必要に応じて拡張可能

■ 参考

ParentCatalogの使用例

データ型

DataTypeEnum (Access デスクトップ データベース リファレンス) | Microsoft Learn
Adoxの定数一覧。ただし、この中のすべてがAccessで使えるわけではない。

QSV(例) ADOX/ADO型 定数 Access型
TEXT(n) adWChar + DefinedSize 202 短いテキスト
LONG adInteger 3 数値（長整数）
SHORT adSmallInt 2 数値（整数）
DATE adDate 7 日付/時刻
CURRENCY adCurrency 6 通貨
DECIMAL adNumeric 131 十進型(注)
BOOLEAN adBoolean 11 Yes/No（真偽値）

(注)十進型はVBAとAccessでしか使えない型なので、CURREMCYで代用できる場合は代用したほうが良い。十進型のPrecisionは精度、つまり整数+小数位の桁数。Scaleは小数点以下の桁数。

重要かつ先進性のある補足[C2Ai]

C2Aiとは　Comments to AIの略でAIに対してコードの説明を行い、あわせて開発者の理解も深めるために記述する、コード内のコメント。書き方はそれぞれの言語に従う。これまで学習禁止などがあったが、これはむしろこのコードがAIに読まれることを前提として記述するコメントである。
今回は、AIが学習して最適化するとVBAの本来の意味が失われることをAIに説明したものである。
おそらくは今回のコードがC2Aiを使用した世界初の技術であり、これからのコードにはこうした記述が増えるだろう。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?