DOBON.NET DOBON.NETプログラミング掲示板過去ログ

OLEDBを使用してExcelファイルの読み込みについて

環境/言語:[C#]
分類:[.NET]

いつもお世話になっております。

今回質問させていただいたのは、題名にある、OLEDBを使用して、
Excelを読み込む際に、
DataTableのDataColumnのDataTypeを指定できないかというです。

今現在行っている方法は、かなり強引で、以下の通りとなります。

1.型付DataTableをSQL Serverの定義に合わせて定義をしておく。
カラム名などもSQL Serverの定義に合わせておく。

2.Excelファイルを一度OLEDBを使用して、読みこむ。
その際、Columの名称を全て取得し、型付DataTableのカラム名を一時的に
変更する。
これは、項目名が不明な為です。
順番として、項目の並び順のみ、DataTableの定義と同一という形式になっています。

3.読み込んだDataTableを破棄し、カラム名をExcelシート側の項目名に合わせて、
型定義を行っているもので、再度Fillする。

4.取得しなおした、DataTableに対して、順次カラム名を元のSQL Server側の
カラム名に変更する。

もともと項目名だけ合わせてあげれば、Mergeできると思っていましたが、
DataTypeの事を忘れていて、どうしたものかと手詰まりです。

上以外の方法でも、型付DataTable側のDataTypeで、Convert.ChangeTypeで
変換してしまおうかとも思いましたが、なんだかすっきりしません。

もし、読み込み時にカラムの型を指定する方法があれば、
教えて頂けないでしょうか。

実行環境：Windows XP Pro SP2 / Excel 2003
開発環境：Windows Vista Ultimate SP1 / Excel 2007 / .NET Framework 2.0

開発環境と実行環境が違うのは、諸事情の為です。

当然、Windows XPとExcel2003で開発すれば？というのはありますが、
この件については、大人の事情と、下っ端プログラマ(私)の事情です。

# カラム名称を固定で、型付DataTableに設定して、
# Fillすれば、うまいこといくんですが、肝心のカラム名が。
# どうにもならなければ、最悪固定してしまうというのも考え中。
> もともと項目名だけ合わせてあげれば、Mergeできると思っていましたが、
> DataTypeの事を忘れていて、どうしたものかと手詰まりです。
>
> 上以外の方法でも、型付DataTable側のDataTypeで、Convert.ChangeTypeで
> 変換してしまおうかとも思いましたが、なんだかすっきりしません。
>
> もし、読み込み時にカラムの型を指定する方法があれば、
> 教えて頂けないでしょうか。

　　結果的にどういうことを望んでいるのか、イマイチ理解できません。

　　Excelのデータを、SQL Serverに入れたいだけ？
　　SQL Serverがどう関わっているのか解りません。

　　ただ単、Excelからデータ読み込む際にDataSetやDataTableを
　　一旦経由してからSQL Serverに入れたいだけであれば・・・

　　SQL Server側でリンクサーバーの設定をしてExcelファイルを
　　読み込めるようにできるはずなので、後はSQL文一発で読み込
　　めます。

　　カラム名については、SQL文で指定できますし、その際に型変
　　換もできますし・・・

※　SQL Serverのリンクサーバー設定でExcelを指定するのは未確認
　　ですが、できなかった場合でも、OleDbでExcelが取得する段階
　　で、カラム名の変換は、SELECt文内でできますので、わざわざ
　　カラム名の付け替えをする必要はありません。

以上。
追伸

SQL Server のリンク サーバーおよび分散クエリで Excel を使用する方法
http://support.microsoft.com/kb/306397/ja

[HOWTO] DTS: Excel から SQL Server にデータをインポートする方法
http://support.microsoft.com/kb/321686/ja

参考まで・・・

以上。
返信ありがとうございます。

何が行いたいか、というと、以下のような事が行いたいです。

Excelファイルを読み込み画面上に表示する。
この時点で、DataSourceには、DataTableを指定し、
その後の処理もDataTableを使用します。

ユーザに、さらに登録(読み込んだDataTableのDataRowに対して、読込以外の
値を設定してもらいます。)

その後SQL Serverへの登録。

最終的には、DBへの登録が行われますが、
画面での入力が間に入るので、その前の時点で、
型付DataTableで、整合性を取っているだけです。


以上の流れになるのですが、SQLを流す際に別名というのも考えましたが、
読込時のカラム名わからなくても AS で別名きれます(列Aとかで)？
Excelから読み込む場合に列の指定で、別名が切れるなら、
問題は解決します。

あと、Excelは、動的に変更が入るうえ、ファイル名の保障もされません。
その状態で、SQL Server側からリンクを張るより、楽だからクライアント側で
処理を行おうとしています。

データのインポートについても同様です。

DTSとかでパッケージ作成して、処理できれば一番楽なんでしょうが、
仕様てきにそうもいかないところです。
> その後SQL Serverへの登録。

　　これは別マシンですか？

> 最終的には、DBへの登録が行われますが、
> 画面での入力が間に入るので、その前の時点で、
> 型付DataTableで、整合性を取っているだけです。

　　読み込んだ時点で登録して、編集後更新させると言う方法も
　　ありかと。

　　非接続型でやれば、更新処理はSQL文さえちゃんとしておいれ
　　が、自動的に行える・・・

> 以上の流れになるのですが、SQLを流す際に別名というのも考えましたが、
> 読込時のカラム名わからなくても AS で別名きれます(列Aとかで)？
> Excelから読み込む場合に列の指定で、別名が切れるなら、
> 問題は解決します。

　　実験して下さい！

　　select hogehoge as A, ・・・とかして
　
> あと、Excelは、動的に変更が入るうえ、ファイル名の保障もされません。
> その状態で、SQL Server側からリンクを張るより、楽だからクライアント側で
> 処理を行おうとしています。

　　リンクサーバー設定もＳＱＬ文流して行えますので、動的に
　　設定を登録して使用し、終わったら削除。なんてことも可能
　　です。

　　ただ状況的にサーバーは別マシンのようですので、リンク先
　　を共有フォルダとして公開しないと今回のケースは無理なの
　　で・・・

※　ローカル側はアクセスのMDBにして、そこにエクセルをリンク
　　させて・・・後、アクセスからSQL Serverへ・・・なんて事
　　も可能かと。

　　まくまでデータセットの管理部分と編集部分を自動で行わせる
　　ようにしてコードの縮小を行うのが目的で、ＤＢ連携に関わる
　　リスクは、逆にどうにでもなる・・・と思っているからです。

　　異常値の入力抑止等細かい話になれば、別ですが・・・
　　まぁ〜ＤＢ的に処理するか、グリッド上の編集段階で面倒見る
　　か・・・タイミングの問題かと。

> データのインポートについても同様です。
>
> DTSとかでパッケージ作成して、処理できれば一番楽なんでしょうが、
> 仕様てきにそうもいかないところです。

　　動的に・・・と言うことを考慮しないと、そうでしょう・・・

●　技術的な守備範囲とコード量的なバグのリスクの双方を検討して
　　適切な方法を見つけて下さい！

以上。
■No24303に返信(オショウさんの記事)
>これは別マシンですか？

別マシンになります。

>読み込んだ時点で登録して、編集後更新させると言う方法も
>ありかと。
>
>非接続型でやれば、更新処理はSQL文さえちゃんとしておいれ
>が、自動的に行える・・・

実際は、これも考慮に入れたんですが、
もともとの作りが大幅に変更する必要があるため、期間と予算の都合上
無しの方向になりました。

>実験して下さい！
>select hogehoge as A, ・・・とかして

単票のヘッダー側の列名としては、可変となります。
A列として指定できるのであれば、教えて頂けないでしょうか？

ASの置き換えでは、今のところ、以下のようなパターンはうまくいきますが、
Hoge1を列A(Excelの本当の列名)として記載した場合の指定方法がわからないのです。

SELECT Hoge1 UserName
,Hoge2 UserCode
FROM Sheet$....

>リンクサーバー設定もＳＱＬ文流して行えますので、動的に
>設定を登録して使用し、終わったら削除。なんてことも可能
>です。

>ただ状況的にサーバーは別マシンのようですので、リンク先
>を共有フォルダとして公開しないと今回のケースは無理なの
>で・・・

そのとおりです。
また、複数ユーザからのアクセスを可能とするには、ユーザ単位に
ファイル名を変更しておいてあげるなどというのも必要なので、
今回は、見送りです。
(たぶん実際は、そこまでする必要があるのであれば、
FTPサーバなどになりそうですが、予算と時間の都合上却下です。)

Accessもそうですが、MSDEなどのインストーラーに含めてしまい、
ローカルマシンに一度読みこませてしまうというのも考えましたが、
そもそもインストーラーに入れる事自体がNGでした(MSDE)。

以上。
DOBON.NET | プログラミング道 | プログラミング掲示板