ACCESSインポート時のデータ型変換の不具合に関する調査 MaxScanRows

  



ACCESSインポート時のデータ型変換の不具合に関する調査


 1 現象
ACCESSへ、CSVファイルからデータをインポートするときに、ある列の小数の値が、小数点以下を切り捨てられた状態で、整数としてインポートされた。テーブルの該当列のデータ型はテキストになっていた。
データのインポートは、ファイルAを最初に行った。
ACCESSのインポートウィザードを使用し、ウィザード上で新しいテーブルを自動作成して、インポート時のデータ型をテキストに指定してインポートを行った。
Aのインポートの結果は、小数点以下も保持され、問題はなかった。
ファイルBのインポートに当たっては、Aのインポート時に自動作成されたテーブルにインポートした。テーブルからAのデータを削除し、空になったテーブルにインポートウィザードを使用して、Bのデータをインポートした。データ型の指定(インポート定義)は行わなかった。該当列のデータの小数点以下が切り捨てられてインポートされた。また、インポート時のエラーは発生しなかった。
ファイルC、D、E についても同様の作業を行い、インポートの結果は小数点以下が切り捨てられていた。

2 検証
インポート時に、データの上から~行目までのスキャンが行われ、データ型をACCESSが推測するという情報があったので、検証する。
 元のファイルをコピーし、データの上から順に、一つだけ小数の値を入れて、既存のからテーブルにインポートする。

元のファイル 小数点以下が切り捨てられる
    一行目のデータを小数に変更したデータ 小数点以下が切り捨てられる
    二行目のデータを小数に変更したデータ 小数の形が保持される
三行目のデータを小数に変更したデータ  小数の形が保持される
    四~二十五・・・・・・・・・・・・・ 小数の形が保持される
二十六行目のデータを小数に変更したデータ 小数点以下が切り捨てられる

結果としては、2~25行のデータに小数がない場合は、全て整数としてインポートされている。

 3 ACCESSの仕様と挙動 調査した結果を記述する
ACCESSの既定値では、インポートするファイルがテキスト(CSV)の場合、レジストリに設定されているMaxScanRowsの値を参照し、列のデータ型を推測する(詳細は後述)。
ACCESSのエンジンは、2~25行(既定値)のデータを調べ、データ型を推測してインポートを行う。
1行目が使われないのは、ヘッダ行である可能性があるのでスキャンされないか、優先度が落とされていると推測される(推測)。
インポート定義、もしくはscheme.iniを使用しない場合は、列のデータ型は推測される。

 インポート先のテーブルの列のデータ型は、インポート元の列のデータ型と関連しない。
テーブルのデータ型と、インポートするファイルのデータ型は、比較されたり考慮されることはない。ただし、インポート不能であれば、インポート中にエラーを表示する。
 これはインポート時に、テーブルの列のデータ型は考慮されず、無関係ということである。



4 インポート時の動作のイメージ
以下にイメージを添付する。

f:id:hsmtblue:20190312000019p:plain


f:id:hsmtblue:20190319033106p:plain
5 対策 と チェック
  小数が整数にされたりしないために、データ型を推測させない必要がある。
 そのために、できることを挙げる。
対策
  1 インポート定義、もしくはsheme.iniを使用する。
  2 データ型を推測させないように、レジストリを変更する。
  3 データ型推測時に、全行をスキャンするようにレジストリを変更する。
チェック
  4 計算可能なデータであれば、元のファイル、インポート後、各々合計を出して照合する。
  5 必要があれば、元のファイルと、インポート後のデータをすべて照合する。
  6 データ型が推測されてしまうものだということを、頭に止めておく。

対策1,2,3については、1のインポート定義を推奨する。scheme.iniについてはここでは記述しない(外部アプリからACCESSにインポートする場合に使用する)。
2,3のレジストリの変更も可能であるが、すべてのOFFICEアプリケーションに影響が出るために、あまり推奨はしない(試してみる価値はある)。

チェック 4,5については、時間が許す範囲となる。
 特に5については、都度VBAを組む必要があるが、必要があれば対応する。



 6 レジストリの詳細
 以下のレジストリの値は全て
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Text下にあるものをいう。
1:レジストリのMaxScanRwosの値はデフォルトで25になっている。これを0にすることですべての行がスキャンされる。ここでスキャンされたデータの型が混じっている場合は、次の2の値を参照して動作が決定する。混じっていない場合は25行めまでの型に確定する。

2:レジストリのImportMixedTypes の値が「Majority Type」であった場合、多数決によって決まる(実際には、少数と整数があった場合には、表現力の大きい小数になる)。この値が設定されていない場合は、Majority Type になる。

 レジストリの変更による対策を行う場合には、MaxScanRows=0、ImportMixtedTypes=Text に変更する。
 変更後の挙動は、全行をスキャンし、データ型が混じっていればテキスト型でインポートされる。
 CSVではなくXLS等のエクセルファイルをインポートする場合は、レジストリ内のTypeGuessRows と、ImportMixedTypes の値が参照される。TypeGuessRowsがデータ型を推測するためのスキャン行数となっている。こちらの既定値は8で、デフォルトでは8行しかスキャンせずに推測されてしまう。

OFFICEのバージョン、OSの環境によってレジストリの場所は異なるが、Access Connectivity Engine を検索することで発見できる。

2007と2010以降が混在している場合は、レジストリも複数の場所にAccess Connectivity Engineがあるので注意すること。

以上がレジストリの変更による対応の詳細だが、手順、実際の挙動のチェックなどが雑然としており、あまりお勧めはしないが、一度設定がうまくいけば、スムーズに業務が運べるかもしれない。

レジストリの画面を添付しておく。


f:id:hsmtblue:20190311235506p:plain



7 参考URL

調査したURLを列挙しておく

https://blog.esrij.com/2010/06/18/schemaini-301f/
https://dobon.net/vb/bbs/log3-43/26012.html
https://social.msdn.microsoft.com/Forums/ja-JP/e4c45cae-bf09-4f8b-8f28-e9ef3c5ca929/29305234501239865328653151239120316251041237512383653156533165?forum=vsgeneralja
https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_access-mso_winother-mso_2007/regedit%E3%82%A8%E3%83%87%E3%82%A3%E3%83%83/98fda91e-7343-441a-ac89-155ae4462c4d?messageId=4e05b3a2-86f7-41c6-a52e-7e0368e30700
https://support.microsoft.com/ja-jp/help/968580
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1313045738


f:id:hsmtblue:20190312000019p:plainf:id:hsmtblue:20190312000038p:plain

VB.NET Office360 Excel エラー 外部参照

PCが壊れたので復旧しました。
すると、今まで動いていたVB.NET のアプリでエラー。

インターフェイス型 'Microsoft.Office.Interop.Excel.**** にキャストできません」

というのが出るが、他の環境でチェックすると出ない。

1: エラーが出る環境はWindows10をクリーンインストールしている。
   また、Office360のみをインストールしている。
2: エラーが出ない環境は、Windows7からWindows10へアップデートしている。
   また、Office2007が最初にインストールされ、その後Office360がインストールされている。

そのような違いで、エラーが出てているようですが。

エラーの出るエクセルをよくよく調べてみると、開くときに、「このブックには更新できないリンクが 1つ以上含まれています」と表示されます。
・・・他のブック内のセルを関数が参照していて、
そのブックにパスワードがかかっている。

これだ!
ということで、参照先のブックを開いてから、目的のブックを開いたらエラーが消えました。

うーん、環境によってはこのエラーが出ないということですね。
.net のアプリケーションからは外部参照しているシートもセルも使っていないので、
バージョンによってはエラーが出ないのでしょうか?

書式指定文字列 脆弱性 サイバー攻撃 

ポイントは、%2$n が二つ目の引数で指定した、アドレスに書き込むというところ。

 int secert = 'secert';
printf(%100c$2$n,'a',&secert);

これは ポインタ変数 secret のアドレスに100を書き込んでいる。

それでは攻撃の場合はどうなっているかというと、

./overwrite2 $(python -c 'print "\xb4\xec\xff\xbf" + "%96C%6$n")


6番目の引数のあるはずのメモリの場所にある値を、アドレスとして解釈して書き込む。

まず、アドレスの文字列がメモリのどこかに書き込まれる。
そのアドレスは、調査の結果6番目の引数の場所である。
なので、

1: /xb4\xec\xff\xbf がメモリのどこかに書き込まれる。そのアドレスは、調査の結果6番目の引数の場所である。
2: %96Cで 出力した文字数は合計100になる。
3: %6$nで六番目の引数のメモリの値をアドレスとして解釈し、そこに100が書き込まれる。(1:のアドレスに100が書き込まれる)

XSERVER で メール着信を通知する。

さて、メールサーバをXserver で使用していますが、

共有サーバで、メール着信の通知を別のアドレスに送る方法です。

 

まず、perlスクリプトを用意します。

はまったポイントがいくつか。
@マークは、ダブルクォーテーション内だと、配列変数として展開されるようです。
シングルクォーテーションで囲うか、エスケープして下さい。

※ 完成後、icloudのメールアドレスに送ると文字化けしました。(gmai yahoo は問題なし)
$subject = jcode($subject)->mime_encode;を追加したら解消しました。(多分)

#!/usr/bin/perl

use Jcode;
use MIME::Words qw(:all);
use POSIX qw(strftime);

$SENDMAIL;
$sendmail_cmd = "/usr/sbin/sendmail -t";

#=====================================================
# DECODEする関数
#=====================================================
sub DecodeFld($) {
    my($sBuff) = @_;
    my($sRes, $sWk);
    chomp($sBuff);
    foreach $sWk (decode_mimewords( $sBuff, )){
        ($sTxt, $sCode) = @$sWk;
        $sRes .= $sTxt;
    }
    return $sRes;
}

#=====================================================
# 本体開始
#=====================================================
$r_subject = '';
$r_mail_to = '';
$r_mail_from = '';
$flg_subject = 0;
$flg_read_end = 0;
$flg_go = 0;

#open(DATAFILE, ">", "log.txt") or die("Error:$!");
#print DATAFILE "開始\n";
#close(DATAFILE);

#ヘッダ部から情報抽出
while (<STDIN>) {

    #ヘッダ情報抽出終了後、パイプを空送りしてSTDINを終了させる
    if($flg_read_end){
        next;
    }

    #ヘッダ部の終了チェック
    if(length == 1){
        $flg_read_end = 1;
        next;
    }

    #Subjectの取得
    if($flg_subject == 1){
        if(/^[ \t](.+)/){
            $r_subject .= $1;
        }
        else{
            $flg_subject = 2;
        }
    }
    elsif($flg_subject == 0 && /^Subject: (.+)/){
        $r_subject = $1;
        $flg_subject = 1;
    }

    #FromやTo、その他フラグの取得
    if(/^From: .*<(.+)>.*/){
        $r_mail_from = $1;
    }
    elsif(/^From: (.+)/){
        $r_mail_from = $1;
    }
    elsif(/^To: .*<(.+)>.*/){
        $r_mail_to = $1;
    }
    elsif(/^To: (.+)/){
        $r_mail_to = $1;
    }
}

#       open(DATAFILE, ">", "log.txt") or die("Error:$!");
#       print DATAFILE "$mail_to\n";
#       print DATAFILE "$subject\n";
#       close(DATAFILE);


#Subjectのデコード
$r_subject = &DecodeFld("$r_subject");  #Subject欄デコード(JISコードのまま)

#       open(DATAFILE, ">>", "log.txt") or die("Error:$!");
#       print DATAFILE "$mail_to\n";
#       print DATAFILE "$subject\n";
#       close(DATAFILE);
#文字コード変換(JIS→EUC)
#&Jcode::convert(\$subject, "euc");

#       open(DATAFILE, ">>", "log.txt") or die("Error:$!");
#       print DATAFILE "$mail_to\n";
#       print DATAFILE "$subject\n";
#       close(DATAFILE);

#通知メール対象チェック
if($r_subject =~ /^\[SPAM\]/){
    $flg_go = 1;
}

#通知メール送信

#open(DATAFILE, ">>", "log.txt") or die("Error:$!");
#print DATAFILE "mail送信前\n";
#close(DATAFILE);

    if($flg_go == 0){

        open(MAIL, "| $sendmail -t") or $err = 1;
        if($err == 0){
                $nowStr =  strftime("%Y_%m_%d_%H_%M_%S", localtime(time));
                $subject =$r_mail_to.'に新着メール'.$nowStr;
                $from = $r_mail_to;
                $to = $ARGV[0];
                $cc = "";

$message =<<__E_O_F__;
$r_mail_from から メールが着信しました。
件名: $r_subject
        このメールは自動送信されています。
__E_O_F__

&Jcode::convert(\$subject, 'jis');
$subject = jcode($subject)->mime_encode;
&Jcode::convert(\$message, "jis");

&Jcode::convert(\$subject, 'jis');
$subject = jcode($subject)->mime_encode;
&Jcode::convert(\$message, "jis");

open($SENDMAIL, "|$sendmail_cmd") or die "$sendmail_cmd [$!]";

$text =<<E_O_M;
From: <$from>
To: $to
Cc: $cc
Subject: $subject
Content-Transfer-Encoding: 7bit
Content-type: text/plain;charset="ISO-2022-JP"

$message
E_O_M

print $SENDMAIL $text;

close($SENDMAIL);

#       open(DATAFILE, ">>", "log.txt") or die("Error:$!");
#       print DATAFILE "mailSent\n";
#       close(DATAFILE);
        }
    }

(※各所にコメントで入っている
# open(DATAFILE, ">>", "log.txt") or die("Error:$!");
# print DATAFILE "XXXXX\n";
# close(DATAFILE);
デバッグ用。ログの出力される場所は、~/ドメイン/mail/ドメイン/メールアドレス のディレクトリ内です。)

で、これをmail_alert2.pl として保存します。
場所は ~/ドメイン名/script ディレクトリに置きます。
chmod 700 で権限を設定してください。 chmod 700 ~/ドメイン名/script/mail_alert2.pl

最後に~/ドメイン名/mail/ドメイン名/メールアドレス  にある、 .mailfilter の最終行に、以下を追加します。

cc "| ../../../script/mail_alert.pl 通知先メールアドレス"

これで無事、文字化けなしで通知メールが送られました。
初めてのPerlでした。

参考アドレス
Xserver、Sixcoreでメール着信通知を設定する方法 | 冨山陽平のブログ “思いきり やりぬく”
http://faq.sakuratan.com/wiki/wiki.cgi?%c3%e5%bf%ae%c4%cc%c3%ce%a5%e1%a1%bc%a5%eb%a5%d7%a5%ed%a5%b0%a5%e9%a5%e0%ce%e3
Perlで日本語のメールを送信する方法

エクセルで 貼り付け時 日付にならないようにする

エクセルにデータを張り付けるときに、

分数や、ハイフンでつながった数字(住所等)を張り付けると、

日付に勝手に変換されてしまうことがあります。

 

これを避けるには、

① 貼り付け先で、日付になってしまう列の書式を文字列にしておく

② 張り付けるときに、右クリックして 貼り付けオプションの[貼り付け先の書式に合わせる] を選択します。

 

これでできます。

Office 365 で 再認証したい

Offce365 で 一度非アクティブ化してもう一度再認証したい場合。

 

アカウントに登録されたデバイス名をすぐに変更したい場合などに有効です。

(コンピュータ名を変更すると、そのうち自動で変更されるそうです。)

 

エクセル等を開いても認証画面が出てこない場合は、

 

時刻を一か月以上先に進めて、エクセルを開きなおすと、認証画面が出てきます。

 

時刻がそのままだと、アカウントにログインできないので、

認証画面がでて、サインインをクリックし、IDを入力する画面になったら、

時刻を正常な時間に戻します。

これで、待つことなく再認証できます。

.NET の DataGridViewが遅すぎる。

何度も苦しんできた、.net の DataGridViewのパフォーマンスですが、

ここで一度パフォーマンスを上げるために(下げないために)必要なことを記録しておきます。

 

プロパティ

 ColumnHeaderHeightSIzeMode

    RowHeadersWidthSizeMode

 

の二つは、「絶対にAuto」にしてはいけません。

この二つでパフォーマンスは激落ちです。3秒→ 5分くらい。

 

後は、DataSource にDataTableを突っ込むときは、

一度Visible = False にしたほうが速いような気がします。

Visible = False で処理できる部分は、なるべくDataGridView.Visible = False で

処理を行います。

 

列幅、行高さの調整は、うまくいかなければ、自前で作ったほうがいいです。

DataGridViewのプロパティやメソッドは、visible との兼ね合い、別のプロパティ変更の順番とか、結構色々なところに依存していて、うまくいかないことが多いです。

 

ちゃんとしてあげれば、

3000行を超えるデータも1秒くらいで表示できたりします。