表計算ソフトExcelにもDB同様データ型の定義があった
表計算ソフトとしてソロバン代わりに使われているマイクロソフトのエクセル（Excel）だが、現在では単なる表計算だけでなく、業務システムの集計・印刷加工のためのツールとしての使い方も広まっている。
入力したデータをAccessやPostgreSQL,MySQL,Oracleなどのデータベースに格納し、クエリーなどプログラム処理で抽出した後、印刷や集計をExcelに任せるという使い方だ。プログラムで集計処理や印刷レイアウトを実行するのに比べて開発工数が圧倒的に削減できる。加えてユーザ側に処理を委ねることで非定型処理などへの対応が柔軟に行えるメリットもある。
ところがDBからExcelにデータを引き渡す際に困ったことが起こってしまう。
Excelとデータベースの連動時に発生する困ったこと
当然のことながらデータベースは各フィールドごとに文字型・数値型・日付型・論理型などのデータ型を厳密に定義している。DBから抽出したデータもDBの定義に従ったデータ型に適合している。ところがこれらのデータをExcelで受け取ると予想外のデータ型として処理されてしまう。
DBの数量や単価のデータは数値型で定義されている。このデータをExcelに渡す。Excelでの表示は 12,345.78 などのカンマ編集付きの書式を設定する。これで大丈夫のハズだが・・・
数値型のデータが文字列に化けた！
ところが一定行（実験では４０５６行）以降のデータが文字列として表示、認識されている。本来なら数値なので右詰めで12,345.78 と表示されるハズのところが、左詰めの表示となり、このデータを使ってDBを更新すると、データ型違反のSQLエラーが発生する。
DBから抽出したデータなので数値型以外のデータは存在しないにもかかわらず、何回読み込み直しても、書式を再設定しても、結果は同じである。
どうもExcelは読み込み時に一定行までしか実データを検査していないようだ。しかも一定行以降は文字として処理を強行している。困ったことだ。
問題の回避策が見つかった
困ってばかりもいられないのでネットで検索すると下記のサイトが見つかった。
Excel ワークシートをリンクする際のデータ型問題を回避する方法
このサイトではExcelのシートをAccessのリンクテーブルとして使用する際の不都合だが、原因は同じだ。
Excelにもデータ型の指定があった
詳細は上記サイトを見ていただくとして、佐藤が理解したところを簡単にまとめると
- 実はExcelにもデータ型という概念がある
- データ型と書式はまったく別のモノである
- データ型は入力時あるいは確定時に決定される
- セルの値が確定した時点でExcelが入力値からデータ型を決定する
- 従って、いくら書式を設定してもデータ型には影響ない
- つまりExcelが一旦文字型だと決定したら書式設定では数値型には戻らない
ここからは佐藤の実験による補足
- 他のDBからデータを取り込む際にExcelは一定行までしかデータを検査しない
- Excelは一定行以降は文字型として取り込みを強行する
一定行以降のデータが勝手に型変更されてしまったらデータ加工用のツールとしてはExcelは使えない！！！
Excelによるデータ型の一方的な変更に対する回避策
上記サイトによると、『何とExcelでもデータ型が指定できる！』 らしい。
Excelのデータメニューから文字区切り（？？）を選択し、ウィザードで標準（＝数値）をチェックして確定すると、指定のセルの値が数値型として処理されるようになった！！
表示も右詰めの 12,345.67 になり、データベースへの更新でもエラーは起こらない。
しかし、何で 区切り文字 の設定でデータ型を指定しなければならないのか？ 発想が理解できない。
Excelをデータ加工ツールとして活用する場合の注意事項
データベースからデータを取り込んだ後、Excelによる勝手なデータ型の設定を打ち消すために、セル範囲を指定して、区切り文字ウィザードで数値型なら標準を、文字型なら文字を指定しておくこと。
上記の指定は数値型のセルすべてに対して行う必要がある。結構面倒で時間がかかる。こんなことをするよりExcelが取り込んだデータすべてを検査するか、一定行で検査を打ち切るなら以降は同じデータ型にしておいてくれれば問題はないのだが・・・。Office2007に期待するしかないか。
