2007年02月19日
今日は、いつもとはちょっと毛色の違う記事です。
たまには、こういう記事もいいかな?
イントラ上で、IISとMS-SQL Server を使ったWeb上のデータベースをASPを利用して作成しています。
このDBに、MS-Excelをデータソースにして、登録者がExcelのファイルを自分でアップロードし、そのままMS-SQL Serverに流し込むシステムを作る必要性がありましたので、作成してみました。
まずは、Microsoft のWebサイトから、必要な情報を収集です。
「[HOWTO] Visual Basic または VBA から ADO を Excel データで使用する」
というページがありましたので、「Microsoft OLE DB Provider for ODBC Drivers」を利用して、ExcelファイルをDBに見立てればデータにアクセスできることが分かりました。
Web Server側に、ODBCドライバの設定を施し、簡単なExcelのデータファイルをアップロードして、その内容を画面に表示させてみました。
(ASPのスクリプト言語はVBです。)
set con=server.createobject("ADODB.Connection")
con.open "DSN=Test;DBQ=filename"
これで、Excelファイルとのコネクションを確立して、後は、SQL文で読み込むだけでしたので、すんなりとデータが取り出せました。
基本の動作が分かりましたので、作り始めることに。
その後、ある程度まで作成したところで、Excelファイルのサンプル作って、動作検証を行います。
このようなデータを取り込んだ際に、Typeの12行目の「10」というデータだけが何故か取得できません。
その隣の「TEST」は取得できるのに、同じ取得ロジックで何故取得できないのか原因が分かりません。
いろいろを、中のデータを変えてテストしてみますと、データが数字で、しかも、最初の方にはデータが入っていない場合に取得出来ないことが分かりました。
ここで、ODBCドライバの設定を見ると、オプションの部分に「スキャンする行数」という設定項目があり、この行数までにデータがあればデータタイプを判定するらしいということが分かりました。
ODBCセットアップダイアログ:
分かったのは良いけど、「ここは文字でも数値でもいいし、しかも入力しなくても良いところだからな~」としばしまた悩みます。
Excelは、数字を入力すると数値としてくれるので、文字にするにはわざわざ先頭に ' を入力する必要があり、全員にこれを強要するのは無理です。
そこで、苦肉の策として、Excelのファイルは、専用のフォーマットファイルを用意し、その都度ダウンロードさせ、しかもフォーマットの改変は不可。データ取得部分は、フォーマットの別の部分として、しかも数値は文字に変換させました。
しかも、念のため、1行目はダミーデータを入れて。
こうして、ようやく安定してデータが取得出来るようになりました。
しかし、これだけでは不安ですので、スクリプト上で、フォーマットの改変をチェックする部分を追加して、しかも、数値データも逐次チェックするロジックを追加しました。
このエラーチェックに引っかかると、処理を中止させて、アップロード者にデータを確認するメッセージを表示する様にしてシステムを稼働させました。
いやー、Excelは手元で利用すると自由度が高くて非常に便利ですが、このようなDBのシステムだと、その自由度の高さが逆に足を引っ張りますね。
別のもっとスマートなやり方があるかもしれませんが、私が作成した例として記事にしました。