LoginSignup
0
1

More than 3 years have passed since last update.

@Q11Q

[Powershell](三訂)Microsoft AccessのCSV読み込みにバグの回避

Last updated at Posted at 2019-11-02

AccessはCSVの読み込みにバグがある

以下の記述は文字コードがANSI Shift-Jis 932が前提

Microsft Access バグ　和暦ANSI Csvファイルからインポートするとき日付をH31/2/1 S50.3.4としていると読み込みを失敗する
で判明したのだが、CSVの読み込みのとき、Excelと比較して、Accessはレジストリにある和暦のデータを読んでいないということがわかった。
このため、基本的に和暦の日付は読み込むことができない。
たとえ昭和29年3月10日といったように、きれいに書いても読まない。Excelは分析して日付に変える。
また、Accessは西暦はドット形式で区切ると日付のみなら読み込むが "2019.12.01 11:23:50 AM"、のようになると、時間を読み込まないため失敗する。
この点についてさらに、分析した結果、Excelでは"2019/12/01 11:23:50AM"のようにAM/PM形式を使用する場合、"2019/12/01 11:23:50 AM" とAM/PMの前に半角スペースを入れないと日付として認識せず、Accessでは当初から読み込まない仕様であることがわかった。
http://makoto-watanabe.main.jp/access/dahowchangingtext.htm
さらに、コンマ付き数字はコンマが現れないと不具合をおこし、FSO.TextStreme,ADODB.Streamを用いるときSPLITで失敗するなど読み込む方法で挙動が変わるため、結局削除するがあることが判明した。
つまり日付とコンマが絡むと不具合が起きてしまう。

この他にも仕様として、

機種依存文字をファイル名に使わないほうが良い。（これがデータベースの名前になるため）
フィールド名になるので列見出しは15文字以内、また同名のフィールドはだめ。
ピリオドを拡張子の前以外に使ってはならない。（ピリオドは[].[]のような意味を持つため、CSVを読み込むときクエリがfilename#csvで解説されているのはそのため。
桁数が長いコードが存在する場合、倍精度で整数として表示されず、その部分は必ず文字列指定が必要な点。これはExcel、Access共通。これを書くのを忘れていた。

Powershell以外の対策方法

ExcelでWorkbooks.OpenTextで読み込む

今度はExcelのバグ：自動記録で失敗する可能性

Excelは自動的にデータの型を認識するので、DBに取り込むときも重宝する。（だからといって、AccessのWizardはやはり指定しなければならない）
しかし、このとき、列数が多く、列ごとに細かく指定する場合、行継続文字を使い切って自動記録が勝手に終了する。
このような場合は、QueryTableが良い。
しかし、QueryTableは255列しか読み込まない。
これはAccessと合わせている。
SQLServerなどをプッシュしている割に、なぜかこういう制約があり喜んで使うとすぐ壊れてしまう。
QueryTableを扱うサイトは多いが、本当にCSVを使っているのか疑問だったりする。
と書いていましたが、Excel2013以上では255列を超えて読み込めることが確認できたので訂正します。しかし、VBAで自動記録したのにエラーをはくなど、意味不明な挙動をするのはたしかです。

というのもExcelには5MBの壁があって、それを超えるといきなり挙動が鈍くなる。
だいたい1万行もあれば遅延が生じる。
これを破るには、Accessに移行しなければならず、そうしないとおそすぎて使えない。
Accessは制約がある分、早い。問題なのは、その制約をMSがはっきりさせていない点である。

Powershellの正規表現を使う方法

別にVBSでもいいと思うのだが、Powershellの方が複雑な条件をかけてPowershell_ISEでテストができる。このため、確認しながら進められる。

Powershell_ISEでの確認方法

コマンド入力画面で、文字列をReplaceしたいときは

>$("確認したい文字列") -replace "確認",'削除'

のように入力する。VBAやVBSでは難しい。

ハマった点

[regex]::replace($x,"[０-９]", { $args.value[0] - 65248 -as "char" })は使えない

https://qiita.com/acuo/items/a4f83d886c4b8a7fcf52
この記事のサンプルは今回参考になったが、この[Regex]を使うと改行が消えてしまい、1行になることがわかった。

漢字まで想定すると3行必要

Excelが漢字でも認識されるため、おそらく漢字で日付を書いてあるCSVは当然あるだろう。
そうした想定のもとに、変換をするととても長くなった。

元年はマッチした時点で西暦に変えてしまう

これは自分で開発したちょっとしたテクニックだ。マッチして整形したもので元年を西暦に変えると、あとは元年以外を考慮すれば良い。あとから楽になる。しかもそれはReplaceを2つ書くことで実現できる。

年月日とスラッシュ区切りを分ける

コードは長くなるが1945年10月1日と1945/10/01は末尾に「日」があるか、まったくないかが違う。
このためこれを一緒にするとコードがわけがわからなくなる。
そのため、これを分けて置換している。

\dは全角数字も表している

全角数字も入っているという解説を見かけたが、動かないようだ。
そこで、[0-9０-９]とした
しかし、検証した結果、これは[\d]で代行できる。
途中ではどうも表示が違うのだが、最初の全角、半角変換で変換されているらしい。

上記の場合\$1は半角数字、全角数字ともに含んでいる

[\d]は([0-9０-９])なので、\$1は含んでいる

コンマ付き数字はダブルクォーテーションで包まれていることを前提

ダブルクォーテーションから通貨記号（数字以外）が1字あるかないか、で一度変換する。するとそのあとは必ず3つ以上の数字の並びの後ろにコンマが来る。この限界は必ず非数字になる。
というアルゴリズムで変換している。

ダブルクォーテーションは削除しなくても良い

https://www.atmarkit.co.jp/ait/articles/1706/16/news032.html
古い解説ではコンマのほかダブルクォーテーションを削除しないと文字列型にしかならないというのを見かけたが、どうもそんなことはないと思う。逆に全部包まれている方がコンマ付き数字を変換できるし、ExcelもAccessも文字列にするというわけではない。

AM/PM表示を24時間制にする

まずAMのケースはそのまま削除
PMのケースはマッチコレクションを定義し、さらにその中でGroupsを取る
Groups[0]が全体を表す
そして時間だけを12を足す。このときintで型変換
それをString宣言して再度合成して
置換する。
数が多いと遅いだろう。
https://sevenb.jp/wordpress/ura/2015/08/06/powershell%E6%96%87%E5%AD%97%E5%88%97%E3%82%92%E6%97%A5%E4%BB%98%E3%81%AB%E5%A4%89%E6%8F%9B%E3%81%99%E3%82%8B/
日付だけなら[Datetime]::ParseExact("yyyy/MM/dd")が使えるが時間ではエラーを起こす。
https://www.reddit.com/r/PowerShell/comments/3px1kr/change_12h_time_format_to_24_in_csv/
この24時間変換は海外でも話題。

出力文字のエンコードはPowershell6.0以降異なっている

今回はCP932 ShiftJIS ANSIしかし
$x>C:\hoge\hoge.txt
はUTF-16 LEになってしまう。
これは古くからある現象だ。
問題なのはPowershell5.0以前はDefaultを使うとSjisになったが、
どうやってもならないことがわかった。
http://mojibake.seesaa.net/article/55710388.html
このように2007年から知られているが、対応方法が変わっている。検索するとき注意しなくてはならない。
http://chintongame.hatenablog.com/entry/2015/01/29/143158
このStringオプションも効かなかった。
Powershellでファイルの文字コードを変換
Powershell5.0の時と挙動が違うようだ。
https://docs.microsoft.com/ja-jp/previous-versions/windows/powershell-scripting/hh847788(v=wps.640)?redirectedfrom=MSDN
PowerShell 6.0からファイル出力に関わるエンコーディングが変わります
とにかくPowershellで置換するにはCSVファイルの文字コードがSjisかUTF-8が有利であることは判明した。

おそらくUTF-8を標準化？

なにもしない、Defaultオプションを使うとUTF-8になる。UTF-8を指定するとUTF-8BOM付きになる。
以前の記事とDefaultが変わっている。どうもShift-Jisから変えようとしているのではないか。

ただしSchemaの指定は932でもよい。

もともとSjisで文字化けしないデータであれば、機種依存文字もないのであれば、Shema.iniの文字コードは932でもトラブルは起きないようだ。
もし起きるなら、Schema.iniのコードを変えれば良い。

ImportするファイルがSjisでないならPoweshellのコードも変えること

UTF-8なら65001にすること
またGet-Contentsなどのところも変更すること

#MicrosoftはCSVファイルが和暦 H20.1.1 H20/1/1,昭和20年1月1日であるとAccessが日付型として読み込むことができないバグを20年以上放置している
#
#set-executionpolicy remotesigned -scope process -force
#http://www.vwnet.jp/windows/PowerShell/2018122702/ConvJC.htm
#[Powershell > for文と変数のカウントアップ](https://qiita.com/7of9/items/a6e8f66e19eed4b20a01)
#Get-Contet  UTF16、SJIS、UTF8(BOMあり）のテキストファイルを読み取れる。 #http://yanor.net/wiki/?PowerShell/%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E6%93%8D%E4%BD%9C/%E3%83%86%E3%82%AD%E3%82%B9%E3%83%88%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%81%AE%E5%85%A5%E5%87%BA%E5%8A%9B%E3%81%A8%E6%96%87%E5%AD%97%E3%82%B3%E3%83%BC%E3%83%89
#[Get-Content](https://docs.microsoft.com/ja-jp/previous-versions/windows/powershell-scripting/hh847788(v=wps.640)?redirectedfrom=MSDN)
#[Regex.Matches メソッド](https://docs.microsoft.com/ja-jp/dotnet/api/system.text.regularexpressions.regex.matches?view=netframework-4.8)
#全角を半角　例えばUTF8(BOMなし）なら Get-Content -Encoding UTF8
#[System.IO.File]::WriteAllLines("utf8-bom.txt", "utf8-nobom.txt")
#[Powershellでファイルの文字コードを変換](https://qiita.com/tworks55/items/b7884c4bb6ec84e73e57)
using namespace Microsoft.VisualBasic
using namespace System.Text.RegularExpressions;
Add-Type -AssemblyName Microsoft.VisualBasic

#$narrow = [Strings]::StrConv($wide, [VbStrConv]::Narrow)
#$wide = [Strings]::StrConv($narrow, [VbStrConv]::Wide)

#####FUNCTION BLOCK#################################### Function Block ####################
function ConvertTo-Narrow
{
    param
    (
        [Parameter(ValueFromPipeline=$true)]
        [string]
        $String
    )

    process
    {
        [Microsoft.VisualBasic.Strings]::StrConv($String, [Microsoft.VisualBasic.VbStrConv]::Narrow)
    }
}

function ConvertTo-Wide
{
    param
    (
        [Parameter(ValueFromPipeline=$true)]
        [string]
        $String
    )

    process
    {
        [Microsoft.VisualBasic.Strings]::StrConv($String, [Microsoft.VisualBasic.VbStrConv]::Wide)
    }
}
function ConvertTo-HourPMFormat
{
    param
    (
        [Parameter(ValueFromPipeline=$true)]
        $String
    )

    process
    {
        [Microsoft.VisualBasic.Strings]::StrConv($String, [Microsoft.VisualBasic.VbStrConv]::Narrow)
    }
}
#####FUNCTION BLOCK#################################### Function Block ####################

##### Character Setting Status ################## Character Setting Status ################
chcp 932
$OutPutEncodeStatus =  [Console]::OutputEncoding
Write-host $OutPutEncodeStatus
$enc = [Text.Encoding]::GetEncoding("Shift_JIS")
$vbCrLf = "`n`r" ; $vbTb = "`t" ; $vbCr = "`r" ; $vbLf = "`f"
$filePath = "C:\dbfolder\Inporttestdate.csv"
$importfile = "C:\dbfolder\rst.txt"
$outFilePath = "C:\dbfolder\Inporttestdate.txt"
#####FUNCTION BLOCK#################################### Function Block ####################
$x= $(Get-Content -Path $filePath  -Encoding string )
 write-host $x
 #$x=[regex]::replace($x,"[０-９]", { $args.value[0] - 65248 -as "char" }) #932だと改行が消える
 $x = $x -replace "０" , "0" -replace "１", "1" -replace "２", "2" -replace "３", "3" -replace "４", "4" -replace "５", "5" -replace "６" , "6" -replace "７", "7" -replace "８", "8" -replace "９", "9" 
 $x = $x -replace "ＡＭ", $(ConvertTo-Narrow("ＡＭ"))

 $x = $x -replace "ＰＭ",'PM'
 $x = $x -replace "[ＲｒRr]([0-9０-９元]{1,2})[年]([0-9０-９]{1,2})[月]([0-9０-９]{1,2})[日]",'R$1/$2/$3' -replace "R元",'2019'
 $x = $x -replace "(令和)([0-9０-９元]{1,2}[年]([0-9０-９]{1,2})[月]([0-9０-９]{1,2})[日])",'R$2/$3/$4' -replace "R元",'2019'
 $x = $x -replace "(令)([0-9０-９元]{1,2})[年]([0-9０-９]{1,2})[月]([0-9０-９]{1,2})[日]",'R$2/$3/$4' -replace "R元",'2019'

 $x = $x -replace "[ＲｒRr]([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'R$1/$2/$3' -replace "R元",'2019'
 $x = $x -replace "(令和)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'R$2/$3/$4' -replace "R元",'2019'
 $x = $x -replace "(令)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'R$2/$3/$4' -replace "R元",'2019'

 $x = $x -replace "[ＨｈHh]([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'H$1/$2/$3' -replace "H元",'1989'
 $x = $x -replace "(平成)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'H$2/$3/$4' -replace "H元",'1989'
 $x = $x -replace "(平)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'H$2/$3/$4' -replace "H元",'1989'
 $x = $x -replace "[ＨｈHh]([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'H$1/$2/$3' -replace "H元",'1989'
 $x = $x -replace "(平成)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'H$2/$3/$4' -replace "H元",'1989'
 $x = $x -replace "(平)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'H$2/$3/$4' -replace "H元",'1989'

 $x = $x -replace "[ＳｓSs]([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'S$1/$2/$3' -replace "元",'1'
 $x = $x -replace "(昭和)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'S$2/$3/$4' -replace "元",'1'
 $x = $x -replace "(昭)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'S$2/$3/$4' -replace "元",'1'

 $x = $x -replace "[ＳｓSs]([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'S$1/$2/$3' -replace "元",'1'
 $x = $x -replace "(昭和)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'S$2/$3/$4' -replace "元",'1'
 $x = $x -replace "(昭)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'S$2/$3/$4' -replace "元",'1'

 $x = $x -replace "[TtＴｔ]([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'T$1/$2/$3' -replace "元",'1'
 $x = $x -replace "(大正)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'T$2/$3/$4' -replace "元",'1'
 $x = $x -replace "(大)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'T$2/$3/$4' -replace "元",'1'

 $x = $x -replace "[TtＴｔ]([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'T$1/$2/$3' -replace "元",'1'
 $x = $x -replace "(大正)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'T$2/$3/$4' -replace "元",'1'
 $x = $x -replace "(大)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'T$2/$3/$4' -replace "元",'1'

 $x = $x -replace "([0-9０-９]{4})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'$1/$2/$3'
 $x = $x -replace "([0-9０-９]{4})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'$1/$2/$3'
 $x=  $x -replace "([0-9０-９]{1,2}\:[0-9０-９]{1,2}\:[0-9０-９]{1,2})(AM|PM)" , '$1 $2'
 $x = $x -replace "(`"\D{0,1})([0-9]{1,3})\,([0-9]{3}.*)([\.\D])",'$1$2$3$4'
for($idx=1;$idx -lt 4;$idx++){
$x = $x -replace "(`"\D{0,1})([0-9]{3,})\,([0-9]{3})([\.\D])",'$1$2$3$4'
}
for($idx=1;$idx -lt 65;$idx++){
$G1 = "S" + $idx + "\/([0-9]{1,2})\/([0-9]{1,2})"
$G2= [string](1925d + $idx) + "/" + '$1' + "/" + '$2' 
$x = $x -replace  $G1,$G2
}
for($idx=1;$idx -lt 32;$idx++){
$G1 = "H" + $idx + "\/([0-9]{1,2})\/([0-9]{1,2})"
$G2= [string](1988d + $idx) + "/" + '$1' + "/" + '$2' 
$x = $x -replace  $G1,$G2
}
for($idx=1;$idx -lt 30;$idx++){
$G1 = "R" + $idx + "\/([0-9]{1,2})\/([0-9]{1,2})"
$G2= [string](2018d + $idx) + "/" + '$1' + "/" + '$2' 
$x = $x -replace  $G1,$G2
}
$x = $x -replace "([0-9]{4}\/[0-9]{1,2}\/[0-9]{1,2}) ([0-9]{1,2}\:[0-9]{1,2}\:[0-9]{1,2}) AM",'$1 $2'
$x = $x -replace "([0-9]{4}\/[0-9]{1,2}\/[0-9]{1,2}) ([0-9]{1,2}\:[0-9]{1,2}) AM",'$1 $2'
#PMのパターンを変換
$pattern ="([0-9]{4}\/[0-9]{1,2}\/[0-9]{1,2}) ([0-9]{1,2})(\:[0-9]{1,2}\:[0-9]{1,2}) PM"
$mc = [regex]::matches($x, $pattern,"ignorecase")
if(($mc.count -ge $mc.count) -eq $true){
for($idx=0;$idx -lt ($mc.count );$idx++){
$m= $mc.item($idx)
$ihour = [int]$m.groups[2].value +12
[string]$G3 = $m.groups[1].value + ' ' + $ihour + $m.groups[3]
$x = $x -replace $m.groups[0].value, $G3
}
}
#PMのパターンを変換
$pattern ="([0-9]{4}\/[0-9]{1,2}\/[0-9]{1,2}) ([0-9]{1,2})(\:[0-9]{1,2}) [PMＰＭ]" #[PMＰＭ],[PM|ＰＭ],[(PM)|(ＰＭ)]は失敗する
$mc = [regex]::matches($x, $pattern, "ignorecase")
if(($mc.count -ge $mc.count) -eq $true){
for($idx=0;$idx -lt ($mc.count );$idx++){
$m= $mc.item($idx)
$ihour = [int]$m.groups[2].value +12
[string]$G3 = $m.groups[1].value + ' ' + $ihour + $m.groups[3]
$x = $x -replace $m.groups[0].value, $G3
}
}
if((Test-Path $importfile) -eq $true ){Remove-Item $importfile}
#$x | Out-File -FilePath $importfile -Encoding utf8 -Force -NoClobber -NoNewline 
#$x>$importfile #UTF-16 LEになる
$x | Set-Content  $importfile -Encoding Default -Force
Write-Host $x

エターナルスタンダードデータベースファイル形式、CSVの利点

あまり誰も書かないけど、だいたいこんな点が長所
1. ファイル容量が2GBを超えて良い(Accessの上限は2GBで実際に使えるのはこの半分くらい）
2. 列（フィールド）が255を超えて良い。
3. 列のデータ型が厳格ではないので、適当に扱ってもバグにはならない
4. 途中から新たに異なるデータ型、列数のデータベースを結合できる。
5. 置換、検索が容易（暗号化されていな文字数）
6. Microsoft Officeのようにクラッシュしたりしない。そういう意味で堅牢。
7. バイナリではないので、メモ帳でも編集可能。
8. 自由に注釈が可能
9. どんなデータベースでも読み込可能（DBごとの制約はあるが。）
10. このような無敵の特徴しかないCSV形式こそ最強のデータベースファイル形式であり、あとは全部CSV形式に勝てない劣った負け犬のファイル形式しか存在しない。
11. このような汎用性を持っているため、100年後でもおそらく読み込み可能。Accessはあと20年もすれば存在すら忘れ去られるかもしれない。次世代に引き継げる唯一のデータベースである。
12. 最強の堅牢性を持っており、弱点が存在しない。平文であることは機密性にかけるが、ネットからはなし、さらに物理的に隔離すれば問題がない。セキュリティに費用がかかりすぎ。
13. 特別なソフトを必要としないため、維持費用は保存するメディアを買い換える程度である。つまり実質無料で維持できる。
14. 業界標準のようなデファクトスタンダードというような無意味なものではなく、エターナルスタンダードというレベルの汎用性を持っている。

上部にコメントが入った場合のcsv

ただし項目の行のパターンはユニークであるとします。しかし上部にRegExで項目の行と同じパターンが含まれています。
今度はc:\hoge\Inporttestdate.csvに次のように注釈が入っているとします

hahaha
bougai
[Select-Stringは具体的に何を検索しているのか？](https://blog.shibata.tech/entry/2015/08/20/234923)
Out-Steamを使うとフォルダないのファイル名を検索。リテラルパスはワイルドカードが使用できる。
[Out-Stringを使ってSelect-Stringをより便利に使う](https://blog.shibata.tech/entry/2015/08/23/235304)
コンソールに出力される文字列はSelect-Stringの検索対象になるとは限らない
[【Powershell】対象文字列を含んだ行数（何行目か）を取得する](https://pig-log.com/powershell-row-line/)
[powershellを使って文字列"(ダブルクォーテーション)を削除したいです。](https://ja.stackoverflow.com/questions/33790/powershell%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%A6%E6%96%87%E5%AD%97%E5%88%97%E3%83%80%E3%83%96%E3%83%AB%E3%82%AF%E3%82%A9%E3%83%BC%E3%83%86%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%B3%E3%82%92%E5%89%8A%E9%99%A4%E3%81%97%E3%81%9F%E3%81%84%E3%81%A7%E3%81%99)
今回は使わないがせっかく検索したので。特殊文字なのでエスケープ
[特殊文字をエスケープする](https://riptutorial.com/ja/powershell/example/22721/%E7%89%B9%E6%AE%8A%E6%96%87%E5%AD%97%E3%82%92%E3%82%A8%E3%82%B9%E3%82%B1%E3%83%BC%E3%83%97%E3%81%99%E3%82%8B)
https://code-examples.net/ja/q/1fa69f
上記サイトが最も使える。ただしオカルト的はまり方をしている。
重要[RegEx]::Escape("input") ただしReplaceに直接かけない
[改行コードの定義](https://bayashita.com/p/entry/show/91)
[RegEx]::Escape("""No"",""F01Name"",""F02Cur_Income"",""F03PurchaseDate"",""F04DatePaid""") 
"No","F01Name","F02Cur_Income","F03PurchaseDate","F04DatePaid"
1,"John Smith","\2000","S20.1.1","令和元．１２．３１"
2,"Samantha Smith","\1,000,000.22","1945/1/1 11:23:24ｐｍ","1975年1月1日 11:23:24"

コード


#MicrosoftはCSVファイルが和暦 H20.1.1 H20/1/1,昭和20年1月1日であるとAccessが日付型として読み込むことができないバグを20年以上放置している
#
#set-executionpolicy remotesigned -scope process -force
#http://www.vwnet.jp/windows/PowerShell/2018122702/ConvJC.htm
#[Powershell > for文と変数のカウントアップ](https://qiita.com/7of9/items/a6e8f66e19eed4b20a01)
#Get-Contet  UTF16、SJIS、UTF8(BOMあり）のテキストファイルを読み取れる。 #http://yanor.net/wiki/?PowerShell/%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E6%93%8D%E4%BD%9C/%E3%83%86%E3%82%AD%E3%82%B9%E3%83%88%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%81%AE%E5%85%A5%E5%87%BA%E5%8A%9B%E3%81%A8%E6%96%87%E5%AD%97%E3%82%B3%E3%83%BC%E3%83%89
#[Get-Content](https://docs.microsoft.com/ja-jp/previous-versions/windows/powershell-scripting/hh847788(v=wps.640)?redirectedfrom=MSDN)
#[Regex.Matches メソッド](https://docs.microsoft.com/ja-jp/dotnet/api/system.text.regularexpressions.regex.matches?view=netframework-4.8)
#全角を半角　例えばUTF8(BOMなし）なら Get-Content -Encoding UTF8
#[System.IO.File]::WriteAllLines("utf8-bom.txt", "utf8-nobom.txt")
#[Powershellでファイルの文字コードを変換](https://qiita.com/tworks55/items/b7884c4bb6ec84e73e57)
using namespace Microsoft.VisualBasic
using namespace System.Text.RegularExpressions;
Add-Type -AssemblyName Microsoft.VisualBasic

#$narrow = [Strings]::StrConv($wide, [VbStrConv]::Narrow)
#$wide = [Strings]::StrConv($narrow, [VbStrConv]::Wide)

#####FUNCTION BLOCK#################################### Function Block ####################
function ConvertTo-Narrow
{
    param
    (
        [Parameter(ValueFromPipeline=$true)]
        [string]
        $String
    )

    process
    {
        [Microsoft.VisualBasic.Strings]::StrConv($String, [Microsoft.VisualBasic.VbStrConv]::Narrow)
    }
}

function ConvertTo-Wide
{
    param
    (
        [Parameter(ValueFromPipeline=$true)]
        [string]
        $String
    )

    process
    {
        [Microsoft.VisualBasic.Strings]::StrConv($String, [Microsoft.VisualBasic.VbStrConv]::Wide)
    }
}
function ConvertTo-HourPMFormat
{
    param
    (
        [Parameter(ValueFromPipeline=$true)]
        $String
    )

    process
    {
        [Microsoft.VisualBasic.Strings]::StrConv($String, [Microsoft.VisualBasic.VbStrConv]::Narrow)
    }
}
#####FUNCTION BLOCK#################################### Function Block ####################

##### Character Setting Status ################## Character Setting Status ################
chcp 932
$OutPutEncodeStatus =  [Console]::OutputEncoding
Write-host $OutPutEncodeStatus
$enc = [Text.Encoding]::GetEncoding("Shift_JIS")
$vbCrLf = "`n`r" ; $vbTb = "`t" ; $vbCr = "`r" ; $vbLf = "`f"
$filePath = "C:\hoge\Inporttestdate.csv"
$importfile = "C:\hoge\rst.txt"
$outFilePath = "C:\hoge\Inporttestdate.txt"
#####FUNCTION BLOCK#################################### Function Block ####################

##### Start OF 行頭からフィールド名の行までを検索して指定行数削除 ######
$p = Split-Path -Parent $MyInvocation.MyCommand.Path
$pattern =  [RegEx]::Escape("""No"",""F01Name"",""F02Cur_Income"",""F03PurchaseDate"",""F04DatePaid""") 
$tglineNo = $(sls -Pattern $pattern -Path $filePath  -Encoding default　| ForEach-Object { $($_ -split":")[2] } )
#$LastLine = $DataLength = (Get-Content $filePath | Measure-Object -Line).Lines
##### Start OF 行頭からフィールド名の行までを検索して指定行数削除 ######
$p = Split-Path -Parent $MyInvocation.MyCommand.Path
$pattern =  [RegEx]::Escape("""No"",""F01Name"",""F02Cur_Income"",""F03PurchaseDate"",""F04DatePaid""") 
$tglineNo = $(sls -Pattern $pattern -Path $filePath  -Encoding default　| ForEach-Object { $($_ -split":")[2] } )
if($tglineNo -gt 1){
#$LastLine = $DataLength = (Get-Content $filePath | Measure-Object -Line).Lines
    $skip =  $tglineNo -1d
    New-Object System.IO.StreamReader ($p + "\Inporttestdate.csv") , $enc 
    $ins = New-Object System.IO.StreamReader ($filePath  , $enc )
    $outs = New-Object System.IO.StreamWriter ($outFilePath, $false , $enc) #拡張子をcsvにすると変なデータがきる
    try {
        # Skip the first N lines, but allow for fewer than N, as well
        for( $s = 1; $s -le $skip -and !$ins.EndOfStream; $s++ ) {
            $ins.ReadLine()
        }
        while( !$ins.EndOfStream ) {
            $outs.WriteLine( $ins.ReadLine() )
        }
    }
    finally {
        $outs.Close()
        $ins.Close()
    }
$x= $(Get-Content -Path $outfilePath  -Encoding string )
}ELse{
$x= $(Get-Content -Path $filePath  -Encoding string )
}
##### End OF 行頭からフィールド名の行までを検索して指定行数削除 ######
#1行目がフィールド名なら、行頭からフィールド名の行までを検索して指定行数削除 が不要なのでこちらを使う
#$x= $(Get-Content -Path $filePath  -Encoding string )

 write-host $x
 #$x=[regex]::replace($x,"[０-９]", { $args.value[0] - 65248 -as "char" }) #932だと改行が消える
 $x = $x -replace "０" , "0" -replace "１", "1" -replace "２", "2" -replace "３", "3" -replace "４", "4" -replace "５", "5" -replace "６" , "6" -replace "７", "7" -replace "８", "8" -replace "９", "9" 
 $x = $x -replace "ＡＭ", $(ConvertTo-Narrow("ＡＭ"))

 $x = $x -replace "ＰＭ",'PM'
 $x = $x -replace "[ＲｒRr]([0-9０-９元]{1,2})[年]([0-9０-９]{1,2})[月]([0-9０-９]{1,2})[日]",'R$1/$2/$3' -replace "R元",'2019'
 $x = $x -replace "(令和)([0-9０-９元]{1,2}[年]([0-9０-９]{1,2})[月]([0-9０-９]{1,2})[日])",'R$2/$3/$4' -replace "R元",'2019'
 $x = $x -replace "(令)([0-9０-９元]{1,2})[年]([0-9０-９]{1,2})[月]([0-9０-９]{1,2})[日]",'R$2/$3/$4' -replace "R元",'2019'

 $x = $x -replace "[ＲｒRr]([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'R$1/$2/$3' -replace "R元",'2019'
 $x = $x -replace "(令和)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'R$2/$3/$4' -replace "R元",'2019'
 $x = $x -replace "(令)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'R$2/$3/$4' -replace "R元",'2019'

 $x = $x -replace "[ＨｈHh]([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'H$1/$2/$3' -replace "H元",'1989'
 $x = $x -replace "(平成)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'H$2/$3/$4' -replace "H元",'1989'
 $x = $x -replace "(平)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'H$2/$3/$4' -replace "H元",'1989'
 $x = $x -replace "[ＨｈHh]([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'H$1/$2/$3' -replace "H元",'1989'
 $x = $x -replace "(平成)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'H$2/$3/$4' -replace "H元",'1989'
 $x = $x -replace "(平)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'H$2/$3/$4' -replace "H元",'1989'

 $x = $x -replace "[ＳｓSs]([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'S$1/$2/$3' -replace "元",'1'
 $x = $x -replace "(昭和)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'S$2/$3/$4' -replace "元",'1'
 $x = $x -replace "(昭)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'S$2/$3/$4' -replace "元",'1'

 $x = $x -replace "[ＳｓSs]([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'S$1/$2/$3' -replace "元",'1'
 $x = $x -replace "(昭和)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'S$2/$3/$4' -replace "元",'1'
 $x = $x -replace "(昭)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'S$2/$3/$4' -replace "元",'1'

 $x = $x -replace "[TtＴｔ]([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'T$1/$2/$3' -replace "元",'1'
 $x = $x -replace "(大正)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'T$2/$3/$4' -replace "元",'1'
 $x = $x -replace "(大)([0-9０-９元]{1,2})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'T$2/$3/$4' -replace "元",'1'

 $x = $x -replace "[TtＴｔ]([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'T$1/$2/$3' -replace "元",'1'
 $x = $x -replace "(大正)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'T$2/$3/$4' -replace "元",'1'
 $x = $x -replace "(大)([0-9０-９元]{1,2})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'T$2/$3/$4' -replace "元",'1'

 $x = $x -replace "([0-9０-９]{4})年([0-9０-９]{1,2})月([0-9０-９]{1,2})日",'$1/$2/$3'
 $x = $x -replace "([0-9０-９]{4})[．／\.]([0-9０-９]{1,2})[．／\.]([0-9０-９]{1,2})",'$1/$2/$3'
 $x=  $x -replace "([0-9０-９]{1,2}\:[0-9０-９]{1,2}\:[0-9０-９]{1,2})(AM|PM)" , '$1 $2'
 $x =$x -replace "(`"\D{0,1})([0-9]{1,3})\,([0-9]{3}.*)([\.\D])",'$1$2$3$4'
for($idx=1;$idx -lt 4;$idx++){
$x =$x -replace "(`"\D{0,1})([0-9]{3,})\,([0-9]{3})([\.\D])",'$1$2$3$4'
}
for($idx=1;$idx -lt 65;$idx++){
$G1 = "S" + $idx + "\/([0-9]{1,2})\/([0-9]{1,2})"
$G2= [string](1925d + $idx) + "/" + '$1' + "/" + '$2' 
$x =$x -replace  $G1,$G2
}
for($idx=1;$idx -lt 32;$idx++){
$G1 = "H" + $idx + "\/([0-9]{1,2})\/([0-9]{1,2})"
$G2= [string](1988d + $idx) + "/" + '$1' + "/" + '$2' 
$x =$x -replace  $G1,$G2
}
for($idx=1;$idx -lt 30;$idx++){
$G1 = "R" + $idx + "\/([0-9]{1,2})\/([0-9]{1,2})"
$G2= [string](2018d + $idx) + "/" + '$1' + "/" + '$2' 
$x =$x -replace  $G1,$G2
}
$x = $x -replace "([0-9]{4}\/[0-9]{1,2}\/[0-9]{1,2}) ([0-9]{1,2}\:[0-9]{1,2}\:[0-9]{1,2}) AM",'$1 $2'
$x = $x -replace "([0-9]{4}\/[0-9]{1,2}\/[0-9]{1,2}) ([0-9]{1,2}\:[0-9]{1,2}) AM",'$1 $2'
#PMのパターンを変換
$pattern ="([0-9]{4}\/[0-9]{1,2}\/[0-9]{1,2}) ([0-9]{1,2})(\:[0-9]{1,2}\:[0-9]{1,2}) PM"
$mc = [regex]::matches($x, $pattern,"ignorecase")
if(($mc.count -ge $mc.count) -eq $true){
for($idx=0;$idx -lt ($mc.count );$idx++){
$m= $mc.item($idx)
$ihour = [int]$m.groups[2].value +12
[string]$G3 = $m.groups[1].value + ' ' + $ihour + $m.groups[3]
$x = $x -replace $m.groups[0].value, $G3
}
}
#PMのパターンを変換
$pattern ="([0-9]{4}\/[0-9]{1,2}\/[0-9]{1,2}) ([0-9]{1,2})(\:[0-9]{1,2}) [PMＰＭ]" #[PMＰＭ],[PM|ＰＭ],[(PM)|(ＰＭ)]は失敗する
$mc = [regex]::matches($x, $pattern, "ignorecase")
if(($mc.count -ge $mc.count) -eq $true){
for($idx=0;$idx -lt ($mc.count );$idx++){
$m= $mc.item($idx)
$ihour = [int]$m.groups[2].value +12
[string]$G3 = $m.groups[1].value + ' ' + $ihour + $m.groups[3]
$x = $x -replace $m.groups[0].value, $G3
}
}
if((Test-Path $importfile) -eq $true ){Remove-Item $importfile}
#$x | Out-File -FilePath $importfile -Encoding utf8 -Force -NoClobber -NoNewline 
#$x>$importfile #UTF-16 LEになる
#$x | Out-File -FilePath $importfile -Encoding utf8 -Force -NoClobber -NoNewline #1行になる、ボツ
#$x | Export-Csv -Encoding default -Force -LiteralPath $importfile -NoClobber -NoTypeInformation -UseCultur
$x | Set-Content  $importfile -Encoding Default -Force


Write-Host $x

以下後半の文字列削除つきコードについて

StreamReaderがおかしい

またオカルト的だ。
これは次の次で使っている。教科書的にはこれでいいはずだ
https://qiita.com/sukakako/items/ede96f6227f010a0f328

$ins = New-Object System.IO.StreamReader ($p + "\Inporttestdate.csv" , $enc )
と書くとエラーになる。なんでなんでや。
ところが、

    New-Object System.IO.StreamReader ($p + "\Inporttestdate.csv") , $enc 
    $ins = New-Object System.IO.StreamReader ($filePath  , $enc )

むしろ普通はNew-Object System.IO.StreamReader ($filePath , $enc )はエラーになる。フォルダ名+\ファイル名
がお約束だからだ。+を使用しないとエラーになる。
ところが現在、正しく書くとエラーになる。おそらくNew-Objectの行で成功したものをつかんでいるらしい。

変なファイルができる

Inporttestdate.txt System.Text.DBCSCodePageEncodingというのができる。中間ファイルなんて作っていないのに。

Steamreaderで出力するとやっぱりUTF8

少し期待したのだが、ダメだった。
http://garicchi.hatenablog.jp/entry/2015/03/31/034824
以前はこういうのがあったけど、$Encを同じにしていてもUTF-8になる。やはりPowershellのVersion6は何か違う。

それでも文字コードが変わったらUTF8を指定すること

しかし、この指定はやはり中間ファイルのときには文字化けが確認されるので、念の為にあわせた方が良い。以下のSelect-String、上記にもあるとおりGet-Contetも同様。

Select-Stringは配列

https://cheshire-wara.com/powershell/ps-help/select-string-help/
https://blog.shibata.tech/entry/2015/08/20/234923
https://blog.shibata.tech/entry/2015/08/23/235304
なんとファイルから読み込んだ状態で、文字列とはちょっと違う。ファイルの中身をのぞき込んでいるような状態になる。
このため配列になり、データが：で区切られる。
この２番目の区切りが必ず行数になるという。

Select-Stringは文字コードにStringオプションが使えない

Get-Contetと違う。

Regexで特殊文字をエスケープするのは使えるが

特殊文字をエスケープする
$pattern = RegEx::Escape("""No"",""F01Name"",""F02Cur_Income"",""F03PurchaseDate"",""F04DatePaid""")
ダブルクォーテーションはエラーになるので、当然自分で二重にしなければならない。
そのかわり、エスケープが必要なものはエスケープする。ダブルクォーテーション以外はね。
しかしすごいテクニックだ。

行を削除するとき

今度はヒットした行を削除する方法
https://merseyside1892.blogspot.com/2014/07/search-keyword-and-delete-from-the-config.html
このやり方だと空業になる。なぜかこの人はブチ切れているけど、
https://codeday.me/jp/qa/20181127/45131.html
検索を行頭から行末にすればいい。
ところが今回の場合はなぜか行頭、行末文字が不要だった。
意地悪をして項目の行を含む行を作ったのだが、なぜかそこはSkipした。

今回参考にしたselect-string

【Powershell】対象文字列を含んだ行数（何行目か）を取得する
【Powershell】特定の文字列が記載された行を削除する方法
最初意味がわからなかったけど、各行はコロン区切りの文字列なのでSplitで分割して2番めが行数を表す数字になるという。
また指定行を配列であることを利用して削除する。これは2つがつながっている。たしかに、
https://codeday.me/jp/qa/20181127/45131.html
このワンライナーの方がすごいがやはり確実に行こう。上記の手法でも改行を含めて削除できる。
PowerShell/テキストファイルを１行ずつ読み込むサンプルコード

今回最も参考になったコード

複数行 PowerShellでテキストファイルの一番上の行を削除する
これは上の1行と言いながら、何行か削除することを想定している。
そして、前提として今回はデータの後ろには余計なものがないので、項目行がみつかったら、そこまで削除すれば良い。

Select-Stringの注意点

Powershellで特定文字で始まり改行も含む行全体を削除したい
-Rawオプションをつけると単一行になる。

応用系として

PowerShellのSelect-Stringコマンドレットで検索したキーワードの前後の行を表示する方法
前後の行を取得の取得もある。これで絞り込むことが可能そうだ。

最後の行数を取得

http://unkonoblog.hatenablog.com/entry/2017/08/20/151229
これも使う時がある。
これはデータの後半にも色々書いてある時だ。

ここの行の挿入の方法

https://orebibou.com/2015/01/powershell%E3%81%A7sed%E7%9B%B8%E5%BD%93%E3%81%AE%E5%87%A6%E7%90%86%E3%82%92%E8%A1%8C%E3%81%86/
これも重要だ。項目がないCSVファイルに項目名をいみする行を追加できる。

0
1
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
1