【TypeGuessRows】C#でExcelファイル取り込み(OleDB接続)時の注意事項とは【ImportMixedTypes】

本日はC#でエクセルをインポートした時に取り込めないカラム、と言うか空白になってしまい正常な取得が出来なかった時の対処法の話し。

コード全体図
原因は？
対処法
まとめ

コード全体図

        //////////////////////////////////////////////////////////////////////////////
        // 概要：エクセル→データテーブルインポート                                 
        // はまみ：2019/05/30                                                                                                        
        //////////////////////////////////////////////////////////////////////////////
        public Boolean ExcelToDataTable(string strPath,string strSheetName, ref DataTable Dt)
        {
            Boolean bRet = true;

            string strExcelVer = "Excel ";           // Excelファイルver確認

            // ファイル拡張子
            if (strFileEx == ".xls")
            {
                strExcelVer += "8.0;";
            }
            else if (strFileEx == ".xlsx" || strFileEx == ".xlsm")
            {
                strExcelVer += "12.0;";
            }

            // 接続文字列
            string strConString = "Provider=Microsoft.ACE.OLEDB.12.0;"    // プロバイダ設定
                                + "Data Source=" + strPath + "; "         // ソースファイル指定
                                + "Extended Properties=\"" + strExcelVer  // Excelファイルver指定
                                + "HDR=YES;"                              // ヘッダー設定
                                + "IMEX=1;"
                                + "\"";

            OleDbConnection con = new OleDbConnection(strConString);
            String strCmd = "SELECT * FROM [" + ObjWorkBook.Sheets[nSelectSheet].Name + "$]";

            // 読み込み
            OleDbCommand cmd = new OleDbCommand(strCmd, con);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet dataset = new DataSet();

            // SQL実行
            da.Fill(dataset);
            dataset.Tables[0].TableName = ObjWorkBook.Sheets[nSelectSheet].Name;
            Dt = dataset.Tables[0];

            return bRet;
        }

//////////////////////////////////////////////////////////////////////////////

// 概要：エクセル→データテーブルインポート

// はまみ：2019/05/30

//////////////////////////////////////////////////////////////////////////////

public Boolean ExcelToDataTable(string strPath,string strSheetName, ref DataTable Dt)

{

Boolean bRet = true;

string strExcelVer = "Excel "; // Excelファイルver確認

// ファイル拡張子

if (strFileEx == ".xls")

{

strExcelVer += "8.0;";

}

else if (strFileEx == ".xlsx" || strFileEx == ".xlsm")

{

strExcelVer += "12.0;";

}

// 接続文字列

string strConString = "Provider=Microsoft.ACE.OLEDB.12.0;" // プロバイダ設定

+ "Data Source=" + strPath + "; " // ソースファイル指定

+ "Extended Properties=\"" + strExcelVer // Excelファイルver指定

+ "HDR=YES;" // ヘッダー設定

+ "IMEX=1;"

+ "\"";

OleDbConnection con = new OleDbConnection(strConString);

String strCmd = "SELECT * FROM [" + ObjWorkBook.Sheets[nSelectSheet].Name + "$]";

// 読み込み

OleDbCommand cmd = new OleDbCommand(strCmd, con);

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet dataset = new DataSet();

// SQL実行

da.Fill(dataset);

dataset.Tables[0].TableName = ObjWorkBook.Sheets[nSelectSheet].Name;

Dt = dataset.Tables[0];

return bRet;

}

上記、よくあるインポートですが、これで取り込んだ時に本来データがあるハズのカラムのデータがまるっと空っぽになる現象に見舞われました。

原因は？

取り込み元のエクセルを見てみたところ、最初のレコードからしばらく空白が続き、その後文字列が出現するような感じ。ん？なんかこれ前にも合ったような…

エクセルVBA×ADOでDB操作【Recordset】

VBAでデータを取り込むならADO！コピペでOKなコード紹介と、注意点等も細かく解説しています。

【プロパティの不一致】MySQLのCASE式の罠【データ型】

同じテーブルに対して2回SQLを実行し、その後ローカルのデータテーブルでマージを行った所、なぜかデータ型の不一致と怒られた話し。

そう、このへんで触れた内容ですね。もう原因ははっきりしてますけども、念のためデータ型を確認してみる

string SQL1 = Dt1.Columns["カラム名"].DataType.ToString();

1	string SQL1 = Dt1.Columns["カラム名"].DataType.ToString();

↑で見てみたところ、案の定stringでいて欲しいのにDoubleと判断されてしまい、文字列が抹殺されてしまった模様でした。
以前も記載しましたが奴らは頭から8行(デフォルトの場合。 1 ～ 16に変更も可)をスキャンして、
カラム毎のデータ型を自動で判断してきやがります。

で、対処法として接続文字列strConString に追加してる様な記事を良く見かけるんですが、
あれ一切効果ないです。
ImportMixedTypesやTypeGuessRowsは接続文字列で設定したとしても、PC側のレジストリを見に行くからです。

対処法

接続方式を変更すればいいんですが、共通で使わされてて変更も出来ない！みたいな場合もあるかと思うので、
今回は素直にレジストリをいじってみます。

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

ここでTypeGuessRowsと ImportMixedTypes の変更を行います。
ImportMixedTypesは単純にテキストで問題無しですが、TypeGuessRowsの設定はちょっと人によるかも。

0にするとフルスキャンするので、単純に走破時間がレコード数に比例して膨れ上がります。
1にすれば、単純にヘッダーが文字列ならもう文字列としてくれるけども、ヘッダーが無い腐れエクセルを扱わなければ行けない場合だとまた話は変わってしまう…　まぁ今回は基本ヘッダーがあるデータしか取り込まないので１にして無事解決しましたとさ。