SQL Server - Excel からのデータインポートで 255 文字より多い文字数のデータをインポートする方法

SQL Serer Management Studio の SQL Server インポートおよびエクスポートウィザードを使って Excel ファイルのデータを SQL Server のデータベースにインポートすることがあると思います。

便利な機能ですが、セルに 255 文字より多い文字数が入っている時に、255 文字で切れてインポートされることがあります。

この記事では、255 文字でデータが切り捨てされないようにする方法をご紹介します。

Excel インポートで 255 文字で切り捨てされる現象の再現

まずは、Excel インポートで 255 文字で切り捨てされる現象を再現してみます。

次のような Excel ファイルを SQL Server インポートおよびエクスポートウィザードを使って、ローカルにある Test1 というデータベースにインポートします。

Item20 の ItemDescription には、長さが 380 の文字列が入っています。

この Items.xlsx ファイルを SQL Server インポートおよびエクスポートウィザードを使ってインポートします。

データソースに Items.xlsx ファイルを指定します。

変換先にローカルの Test1 データベースを指定します。

変更先のテーブル名を Temp_Items に変更して、[マッピングの編集] ボタンをクリックします。

ItemDescription の Size が 255 になっていて、後で結果がわかりますが、ここで max に変更してもデータは 255 文字で切り捨てられてしまいます。

この状態でウィザードを進め、データをインポートします。

Test1 データベースを確認すると、Temp_Items という名前のテーブルができていて、ItemDescription のデータ型は NVARCHAR(MAX) になっています。

ですが、Temp_Items テーブルのデータを確認すると、Item20 の ItemDescription の長さは 255 になっています。

Excel インポートで 255 文字で切り捨てされる原因と対処方法

このように SQL Server インポートおよびエクスポートウィザードを使って Excel ファイルのデータを SQL Server のデータベースにインポートしたときに 255 文字で切り捨てられることがある原因は、ドライバーがデータ型を決定する際の設定にあります。

デフォルトでは、カラムのデータ型を調べるのに、最初の 8 行分のデータを確認して、最も多いデータ型に決定されるようになっています。

ですので、最初の 8 行目までに文字数の多いデータが入っていれば大丈夫なのですが、そうでないと、今回のように 255 文字で切れてしまいます。

データ型を調べる行数は TypeGuessRows というエントリで設定されています。

[参考] Microsoft Office Excel ドライバーの初期化

ドライバーはレジストリにある TypeGuessRows の値を使うので、その値を変更することによって、データ型を調べる行数をかえることができます。

Office 2016 の場合は TypeGuessRows の値は、以下の場所にありますが、HKEY_LOCAL_MACHINE の下で TypeGuessRows で検索すると出てくると思います。

[MSI インストールの場合]
* For 32-bit Office on 32-bit Windows or 64-bit Office on 64-bit Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

* For 32-bit Office on 64-bit Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

[システムのクイック実行インストールの場合]
* For 32-bit Office on 32-bit Windows or 64-bit Office on 64-bit Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

* For 32-bit Office on 64-bit Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

私の環境では HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel にありました。

TypeGuessRows のデフォルトは 8 になっています。この値を 0 に設定すると、ドライバーは最初の 16,384 行をチェックします。

もしインポートする行数が 16,384 行を超える可能性があり、すべての行を確認したい場合は Excel の行の最大値の 1,048,576 を設定してください。

ここでは TypeGuessRows を 8 から 0 に変更します。

Test1 データベースの Temp_Items テーブルを削除して、もう一度インポートしてみます。

[マッピングの編集] 画面では先ほど 255 だった部分が自動的に max になっています。

そのままウィザードを進め、データをインポートします。

Test1 データベースを確認すると、Temp_Items テーブルができていて、ItemDescription のデータ型は NVARCHAR(MAX) になっています。

そして、Temp_Items テーブルのデータを確認すると、Item20 の ItemDescription の長さは、ちゃんと 380 になっています。

最後に、長い文字列が入ってくる可能性のあるカラムがひとつしか存在せず、レコードの順番が重要でない場合、レジストリを変更せずに、一番長い文字列が入ってるレコードを上にもってくるように Excel でソートしてから、SQL Server にインポートするのもひとつの手かと思います。

以上、Exce から SQL Server へのデータインポートで 255 文字より多い文字数のデータをインポートする方法をご紹介しました。