VBA CSVファイルをAccessのテーブルにインポート

#今回のテーマ
[【C#】CSV、Excelファイルの内容をDataTableに格納する](http://qiita.com/OneK/items/c3077aea595635f40ee5)はC#版なので、VBAではどう書くか

#VBA
以下のコードはAdodb関係は参照設定済み
このCSVは`Const FN = "F:\一覧.csv"`で決まっています。この中身は、
"内容","内容"
のようにダブルクォーテーションで包まれ、コンマで区切られたものとします。
さらにこのCSVファイルはアクセスのテーブルをCSVでエクスポートしたもの（ただし書式は保持しない）という設定です。


```vb:CSVtoAccess
' For Access VBA
Option Compare Database
Option Explicit
Const Prov12_AC2010 = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source="
Const Prov15_AC2013 = "Provider = Microsoft.ACE.OLEDB.15.0;Data Source="
Const Prov16_AC2016 = "Provider = Microsoft.ACE.OLEDB.16.0;Data Source="
Const Excel_xlsb = "EXCEL 12.0"
Const Excel_xlsx = "EXCEL 12.0 Xml"
Const Excel_xlsm = "Excel 12.0 Macro"
Const Excel_97xls = "Excel 8.0"
Const Excel_95xls = "Excel 5.0"
'\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\ISAM Formats\


Const HeaderThru_True = "HDR = YES"
Const HeaderThru_False = "HDR = NO"

Sub CSVtoAccess()
Const FN = "F:\一覧.csv"
Dim db As Database: Set db = CurrentDb
Dim adRS As ADODB.Recordset
Dim CN As ADODB.Connection
Dim tdf As TableDef: Set tdf = db.TableDefs("一覧")
Dim RST As Recordset: Set RST = db.OpenRecordset(tdf.Name)
Dim varVer As Long
Dim i As Long
Set CN = New ADODB.Connection
'VersionよるOLEDｂの切り替え 接続文字列の決定
varVer = Application.Version
Select Case varVer
Case Is = 15
CN.ConnectionString = Prov15_AC2013 & CreateObject("Scripting.Filesystemobject").getFile(FN).parentfolder & ";Extended Properties=""" & "TEXT" & ";HDR= YES;IMEX=1;FMT=Delimited"";"
Case Is = 16
CN.ConnectionString = Prov16_AC2016 & CreateObject("Scripting.Filesystemobject").getFile(FN).parentfolder & ";Extended Properties=""" & "TEXT" & ";HDR= YES;IMEX=1;FMT=Delimited"";"
Case Else
CN.ConnectionString = Prov12_AC2010 & CreateObject("Scripting.Filesystemobject").getFile(FN).parentfolder & ";Extended Properties=""" & "TEXT" & ";HDR= YES;IMEX=1;FMT=Delimited"";"
End Select
CN.Open
Set adRS = New ADODB.Recordset
adRS.Open "Select * From " & CreateObject("Scripting.Filesystemobject").getFile(FN).Name & ";", CN, adOpenDynamic, adLockReadOnly
adRS.MoveFirst
Do While adRS.EOF = False
RST.AddNew
For i = 0 To adRS.Fields.Count - 1
RST.Fields(i).Value = adRS.Fields(i).Value
Next
RST.Update
adRS.MoveNext
Loop
adRS.Close
RST.Close
CN.Close
Set adRS = Nothing
Set RST = Nothing
Set CN = Nothing
End Sub
```

<UL>
<li>HDR 1行目がヘッダーかどうか。1行目が項目名（ヘッダー）だったらTrue、そうではない場合はFalse。通常はヘッダーがあるCSVにします。
<li> IMEX 0:Export 1:常にテキストとして読み取る。Importのとき有効。 2:LinkでImport IMEXはImport Export Modeの意味らしい。
</ul>

このIMEXに関する情報は微妙で1はテキストではなく何行か読み取ったうちの大体（Majority Type)で判断するとして判定するという説明もある。ようするにデータが多数あって、数万行のうち、1レコードだけは数字だけかもしれない。このため、とりあえずすべてテキスト型で読み込むというときに向いている。

https://stackoverrun.com/ja/q/3020246
https://docs.microsoft.com/ja-jp/dotnet/framework/data/adonet/connection-string-syntax
>Excel への接続
Excel ワークブックへの接続には、Microsoft Jet プロバイダーが使用されます。 次の接続文字列では、Extended Properties キーワードは Excel に固有のプロパティを設定しています。 「HDR=Yes;」は最初の列にデータではなく行の名前が含まれていることを示し、「IMEX=1;」は "intermixed" データ行を常にテキストとして読み取ることをドライバーに指示しています。


#Schema.ini
CSVの場合、形が決まっていれば、不要。

#その他参考リンク
##配列で取得する方法など
[CSV形式のファイルをDataTableや配列等として取得する](http://dobon.net/vb/dotnet/file/readcsvfile.html)
上記のほか配列で取得（Line Input 、Adodb.Stream）も考えられる
Line InputはShift-jisのみなのでUNICODEが無理。ただし、英語と数字だけならLine Inputは早い。
ADODB.Streamは文字コードは色々扱えるがLine Inputより遅い。

##Privider=Microsoft.Jet4.0について
Excel2013 32bitなどではまだ使えるが64bitでは使えない。
ただし、32bit起動したVBSciptでは使える。

##CSVのフォーマットとデータ取り込みについて
###型がすでに決まっている場合
[CSVファイルフォーマットの解説](https://codezine.jp/article/detail/2364)
>　CSV形式でデータ交換を行う場合は、必ず以下の項目の取り決めを行ってください。
<OL><li>文字コード
<li>改行コード CrlfかLFか。WindowsはCrLf 定数はvbCrLf
<li>ヘッダ行の有無
<li>区切り文字 コンマかタブか
<li>ダブルクォートで囲むか否か
<li>ダブルクォートで囲まない場合の値内の制御文字(ダブルクォート、カンマ、タブ、改行)の処理
</OL>
###型が決まっていない場合、
上記のほか,Schemaのために型を意識する。
####String Text Char
テキストなら255文字まではChar 
#### Memo Hyperlink
それより長いならMemo もしくは LongChar 、HyperLinkなら　LongChar Attribute HyperLink
このときは改行が使用可能で
数字ExcelはなんでもDouble（Float）倍精度浮動小数点型のため、自分がLong（長整数型）でもExcelとCSVから交互にインポートするとずれる恐れがある。このためDoubleにする。CSV1択あればbyte,Integer,Long,Floatを使い分ける。IntegerとLongは曖昧なのでLongにしたほうが良い。Byte型は使わないこと。田中大先生の教え。
YesNo True/Falseは Bitになる。ただし、インポートはTrueが-1だが、Exportすると1になる。

エスケープ処理のうち`""`でデータ内に二重引用符を入れることは可能。
RFC仕様の和訳(2005)
http://www.kasai.fm/wiki/rfc4180jp

####テキストファイルのファイル名に空白ある場合
[How Can I Use ADO to Open a Text File That has Spaces in the File Name?](https://blogs.technet.microsoft.com/heyscriptingguy/2005/05/24/how-can-i-use-ado-to-open-a-text-file-that-has-spaces-in-the-file-name/)
[]角かっこでファイル名を囲んでからダブルクォーテーションで囲む
strFile = “[Fabrikam Employees.txt]”

```vb:texttoacTable.vbs
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Const Prov12_AC2010 = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source="
Const Prov15_AC2013 = "Provider = Microsoft.ACE.OLEDB.15.0;Data Source="
Const Prov16_AC2016 = "Provider = Microsoft.ACE.OLEDB.16.0;Data Source="
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
strPathtoTextFile = “C:\Scripts\Text files”
'objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=” & strPathtoTextFile & “;” & _
        “Extended Properties=””text;HDR=YES;FMT=Delimited”””
'Access 2007-2019
'OR Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント Install
objConnection.Open Prov12_AC2010 & strPathtoTextFile & “;” & _
        “Extended Properties=””text;HDR=YES;FMT=Delimited”””
'Access 2013
objConnection.Open Prov15_AC2013 & strPathtoTextFile & “;” & _
        “Extended Properties=””text;HDR=YES;FMT=Delimited”””
'Access 2016-2019
objConnection.Open Prov16_AC2016 & strPathtoTextFile & “;” & _
        “Extended Properties=””text;HDR=YES;FMT=Delimited”””

strFile = “[Fabrikam Employees.txt]” '<---Important!!!

objRecordset.Open “SELECT * FROM ” & strFile & ” WHERE Title = ‘Fiscal Specialist'”, _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item(“Name”)
    Wscript.Echo objRecordset.Fields.Item(“Department”)
    Wscript.Echo objRecordset.Fields.Item(“Title”)
    objRecordset.MoveNext
Loop
```

####Schema.iniについて
[Microsoft Jet のための Windows レジストリ設定のカスタマイズ](http://makoto-watanabe.main.jp/access/dahowcustomizingdataaccess.htm#start)
[Microsoft Excel ドライバの初期化](http://makoto-watanabe.main.jp/access/dahowchangingexcel.htm#start)
Schema.iniで検索すると、SQL Serverの解説ばかりヒットして非常に分かりづらい。
AccessのSchema.iniの説明はテキストドライバーの初期化というタイトルで以下のURLである。
https://docs.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/initializing-the-text-data-source-driver


#####文字コードについて
最近のバージョンのメモ帳はコードが表示される。
デフォルトの入出力は932 ANSIである。ExportさせるとBOMはついていない。
しかし現在の主流のUTF-8は65001である。
ただし、UTF-16LEの1200はできない。
UTF-16LEで出力するときは1200ではなく、UNICODEを使う。

#####True False Yes NO
これらはBit型になる。
True Falseという文字列でも変換される。
ただし、インポートしたときはTrueは`-1`だが、TextにExportすると1になる。符号が変わる。

#####Dateの型式指定
大枠の設定と違うそのフィールドだけの様式が指定できる。出力するとき、日付の並び順を変えられる。インポートするときに重宝する。
`Col2=日付 Date(M/dd/yyyy)`
日付のMonthのMは大文字にする。（分との区別）
`gggee/MM/dd h:mm:ss`のような元号出力はできない。

#####十進型のSchema.ini
十進型はテーブルを作るときに、フィールドを十進型にしていなければならない。
このような感じである。Scaleは小数点以下の桁数、Precisionは整数の桁数を意味している。
`Col3=整数 Decimal Precision 18 Scale 0`
###レジストリの位置
[Access 2000 でファイルをインポート、エクスポート、またはリンクすると、エラー メッセージ "インストール可能な ISAM ドライバが見つかりませんでした" が表示されるか、一部のファイルの種類が表示されない](https://support.microsoft.com/ja-jp/help/209805/you-receive-a-could-not-find-installable-isam-error-message-or-some-fi):::[WebArchie](https://archive.is/i2ouU)
このヘルプとレジストリの位置が異なるうえに64 Office 2010 再頒布可能コンポーネントを64bit /32bitをインストールしているため普通と違うかもしれない。なお、このAccess2010再頒布可能コンポーネントは削除されるおそれが高いので、

|ISAM|レジストリ|DLL名|[10]レジストリ|[10]DLL|
|:-:|:-:|:-:|:-:|:-:|
|TEXT|HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text|Mstext40.dll|HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines|C:\Windows\SysWOW64\mstext40.dll|
|Text|||\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Text|C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE14\ACETXT.DLL|
|Excel|||HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel|Msexcl40.dll|HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel|C:\Windows\SysWOW64\msexcl40.dll|
|Access|||\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel|C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE14\ACEEXCL.DLL|