Accessのバグ？とセミコロン区切りUTF-8形式CSVを読み込む方法 ファイルリストのテーブル化

Powershellでコンマ区切りUTF-8CSVを作る

C:\の適当なフォルダでもネットワーク上でもよい
C:\hoge\childvbs.txtにファイル、フォルダのプロパティをCSV形式で出力する。

CLS;Get-ChildItem "\\Network\private\IDnuber\Hoge" -Recurse | Select-Object CreationTime, LastAccessTime,LastWriteTime,DirectoryName,FullName,Name,BaseName,Extension,Attributes,IsReadOnly, Length | Export-Csv "E:\childvbs.txt" -delimiter ";" -Encoding UTF8 -NoTypeInformation -NoClobber -append;$file_contents=$(Get-Content "C:\hoge\childvbs.txt");$file_content -replace ";`"FullName`";`"Name`";`"BaseName`";" ,";`"FullName`";`"sName`";`"BaseName`";";$file_contents>'C:\hoge\childvbs.txt'

PowershellからExportするCSVの問題点

Powershellはファイル名をNameという名前の列に出力する。
このNameを変更すると、ほかのフォルダをchildvbsに追加しようとしてもできなくなる。
しかしAccessではNameという単語が予約語でフィールド名に使用できない。このためsNameとして使用した方がよい。下の方法だと問題はないが。ウィザードだと問題になる。

https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-2017
https://docs.microsoft.com/ja-jp/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-2017
Schemaの解説 SQLサーバーだけど
my-business-adversaria.blogspot.com/2012/08/adodbutf-8csv.html
備忘録 ADODBでUTF-8のCSVを取り込む
UTF-8の場合Schema.iniでCharacterSetを65001にするのは誤り
拡張子がtxt、csvでも65001では値が全部Nullになる。UNICODE
Encodingクラスで扱えるエンコーディング名は？［C#、VB］

筆者の日本語版Windows XP環境でこのサンプル・プログラムを実行した結果を表にまとめると次のようになった。
Code Page Identifiers

MicroSoft Access 2013のバグが疑われる奇妙な挙動

PowershellでUTF-8でExportし、Schema.iniがユニコードで成功する。一度読み込みに成功する。
次にPowershellでASCIIでChildvbs.txtにエクスポート、Schema.iniでANSIにするとなぜか機種依存文字が読み込めてしまう。
つぎに新たにAccessのaccdbファイルを作成し、下記のSubプロシージャ実行する。
するとNullちが読み込まれ、真っ白なテーブルが出来上がる。
この辺の読み込みの不安定さは知られておらず、これが不安定な挙動という印象を与えていることが判明した。

確実にファイルリストを作るには

1.PowershellでExport-CsVをセミコロン区切りUTF-8にする

機種依存文字を文字化けさせないためにはANSIでは無理。コンマが使えるので区切りをコンマ以外にする必要がある。またSchema.iniを作成し、Characterset = UNICODEにする。
またVBAではなくWizardを使用するときはWizardの文字コードはUNICODEにする。

Schema.iniでCharacterSet=Unicodeにする

65001(UTF-8)ではない。
Schema.iniは固定長ファイルだけではなく、可変長ファイルでもこうした場合明らかに挙動に影響があり、実は有効であることが分かった。

Wizardで読み込むときはUTF-8ではなくUnicodeになる

またWizardで手動で読み込むときはUnicodeでないと文字化けが起きる。UTF-8にすると文字化けが起きる。
Schemaが聞いたり聞かなかったりするように見えるのは先ほどの奇妙な点が原因である。
こうしたことから文字コードのセットは慎重に定義すること。

VBAでFMT=Delimited(;)にする。

気休めにはなる。この接続文字列はエラーもなく受け入れられる。(2013,2016確認)

(発展)フルパスは参照照合ができない

長いテキスト（MEMO形式）は実は参照照合ができない。
この時ShortFullPathとID列をchildvbsテーブルに追加する。
以下の更新クエリを作り実行する。ユーザー定義関数は下記のAccess用のコードに含まれている。

UPDATE Childvbs SET Childvbs.ShrotFullPath = ShowShortFullpath([Childvbs]![FullName]);

(発展)複数のフォルダ

C:\hoge1\ S:\data2\Tableの場合には

Get-ChildItem "C:\hoge\" -Recurse | Select-Object CreationTime, LastAccessTime,LastWriteTime,DirectoryName,FullName,Name,BaseName,Extension,Attributes,IsReadOnly, Length | Export-Csv "C:\hoge\childvbs.txt" -delimiter ";" -Encoding UTF8 -NoTypeInformation -NoClobber -append;
これを連続させる。
どういうことかというとC:\hoge\childvbs.txtにAppend追記するということをすればよい。

Get-ChildItem "C:\hoge1" -Recurse | Select-Object CreationTime, LastAccessTime,LastWriteTime,DirectoryName,FullName,Name,BaseName,Extension,Attributes,IsReadOnly, Length | Export-Csv "C:\hoge\childvbs.txt" -delimiter ";" -Encoding UTF8 -NoTypeInformation -NoClobber -append;Get-ChildItem "S:\data2\Table" -Recurse | Select-Object CreationTime, LastAccessTime,LastWriteTime,DirectoryName,FullName,Name,BaseName,Extension,Attributes,IsReadOnly, Length | Export-Csv "C:\hoge\childvbs.txt" -delimiter ";" -Encoding UTF8 -NoTypeInformation -NoClobber -append;

コード

Powershellのコード

ネットワークドライブでもよい
CSVファイルはC:\hoge\childvbs.txt これが3回出てくる

CLS;Get-ChildItem "\\Network\private\IDnuber\Hoge" -Recurse | Select-Object CreationTime, LastAccessTime,LastWriteTime,DirectoryName,FullName,Name,BaseName,Extension,Attributes,IsReadOnly, Length | Export-Csv "C:\hoge\childvbs.txt" -delimiter ";" -Encoding UTF8 -NoTypeInformation -NoClobber -append;$file_contents=$(Get-Content "C:\hoge\childvbs.txt");$file_content -replace ";`"FullName`";`"Name`";`"BaseName`";" ,";`"FullName`";`"sName`";`"BaseName`";";$file_contents>"C:\hoge\childvbs.txt"

UTF-8のSchema.iniをCSVと同じパスに置く。複数ある場合には下に伸ばして書き込む
Accessの場合、
CharacterSet = UNICODE

Schema.ini

[childvbs.txt]
CharacterSet = UNICODE
Format = Delimited(;)
ColNameHeader=True 
MaxScanRows=20
Col1 = CreationTime DateTime 
Col2 = LastAccessTime DateTime
Col3 = LastWriteTime DateTime
Col4 = DirectoryName Text Width 255
Col5 = FullName Memo
Col6 = Name Text Width 255
Col7 = BaseName Text Width 255
Col8 = ExtensionName Text Width 255
Col9 = Attributes Text Width 255
Col10 = isreadonly Bit
Col11 = Length Double

AccessのVBA(Microsoft Access 2010 Later)

Sub test()
'Access
'Schema.iniは可変長ファイルでもやはり効いている(UTF-8は明確に判定している)
'参照設定
Const Prov12_AC2010 = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" 'Access 2013
Const Prov15_AC2013 = "Provider = Microsoft.ACE.OLEDB.15.0;Data Source=" 'Access 2013
Const Prov16_AC2016 = "Provider = Microsoft.ACE.OLEDB.16.0;Data Source=" 'Access 2016
Dim cDB As dao.Database: Set cDB = CurrentDb
Dim tdf As TableDef
Dim i As Long
Dim fld As dao.Field
Dim CN As ADODB.Connection: Set CN = New ADODB.Connection
Dim aRS As ADODB.Recordset: Set aRS = New ADODB.Recordset

Dim sSQL As String
Dim dRs As dao.Recordset
On Error Resume Next
DoCmd.DeleteObject acTable, "childvbs"
On Error GoTo 0
sSQL = "Create Table [Childvbs]([CreationTime] DateTime, [LastAccessTime] DateTime, [LastWriteTime] DateTime, [DirectoryName] Text(255) , [FullName] LongText, [sName] Text(255) , [BaseName] Text(255) , [Extension] Text(255) , [Attributes] Text(255) , [IsReadOnly ] YESNO, [Length] NUMERIC);"
DoCmd.RunSQL sSQL
Set dRs = cDB.OpenRecordset("childvbs")
CN.ConnectionString = Prov15_AC2013 & "C:\hoge\;Extended Properties=""text;HDR=YES;FMT=Delimited(;)""" 'セミコロン区切りはここで区切り文字をセミコロンに合わせる。Delimitedでも動くが、これでもエラーは起きない Access2016は Prov15_AC2016を使用すること
CN.Open '接続開始
aRS.Open "Select * From childvbs.txt", CN 'レコードセットを開く
aRS.MoveFirst
Do Until aRS.EOF = True
dRs.AddNew
For i = 0 To aRS.Fields.Count - 1 'Accessのテーブルは0から始まるのでフィールド（列）数のマイナス1になる(Excelは1)
dRs.Fields(i).Value = aRS.Fields(i).Value '値を転記する
Next i
dRs.Update
aRS.MoveNext
Loop
End Sub

Function ShowShortPath(Folderspec) As String
'https://msdn.microsoft.com/ja-jp/library/cc428148.aspx
Dim fso, f, s
Dim ar, i, buf, br(), ib, sHead, ssHead
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists(Folderspec) Then
ar = Split(Folderspec, "\")
sHead = ar(LBound(ar)) & "\"
ssHead = sHead
For i = LBound(ar) + 1 To UBound(ar)
sHead = sHead & ar(i) & "\"
Set f = fso.getfolder(sHead)
ssHead = ssHead & f.shortname & "\"
Next
Else
GoTo Terminate
End If
ShowShortPath = ssHead
Exit Function
Terminate:
ShowShortPath = ""
Set fso = Nothing
End Function

Function ShowShortFullpath(fullPathfilespec As String) As String
'ユーザー定義関数ShowShortPathを活用しファイルのフルパスからショートパスとショートネームのフルパス文字列を作成する。
'Dim fso Scripting.FileSystemObject: Set fso = New Scripting.FileSystemObject
Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
Dim f, s, fol
If fso.FileExists(fullPathfilespec) Then
Set f = fso.GetFile(fullPathfilespec)
Set fol = fso.getfolder(fso.getparentfoldername(fullPathfilespec))
ShowShortFullpath = CStr(ShowShortPath(fol.Path) & f.shortname)
Else
ShowShortFullpath = ""
End If
Set fso = Nothing
End Function