指定したExcelをSQLテーブルに呼び込むには

質問

VB.Net初心者です。
Win2003server+VB.Net+SQL2000の組合せでやっています。

VBで新しいフォームを作りました。
そこにボタン1を作成しました。

そのボタン1のプロシージャーに
下記のような処理をさせたいと思っています。

1.指定したExcelFileを指定したSQLテーブルに呼込みたい。

AccessのVBAでは下記のような書き方で出来ていたんですが、
*******************************
Dim FName As String

FName = "\\WS55\出荷計画\NWF転用計画.XLS"

DoCmd.SetWarnings False
DoCmd.RunSQL "truncate table 合せB品在庫転用使用機種"　<--テーブルの中身を消す。
DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet , , "合せB品在庫転用使用機種", FName, True, "A1:C200"
<--指定したファイルの指定範囲のデーターをテーブルに呼込む

***********************************
VB.net初心者でやり方がわかりません。

どなたか教えてください。
お願いいたします。

回答No.8

NoMusicNoLife49 · ベストアンサー

>これで何とか書き込めました。
なんとかできて安心しました(汗)

気になったのは、
以下の部分の3行目
____________________For_Each_obect_As_Object_In_DR.ItemArray
________________________If_obect_IsNot_DBNull.Value_=_True_Then
________________________________DataPub(順番)_=_"'"_&_Convert.ToString(obect)_&_"'" ←ここが気になる
____________________________'MsgBox(順番_&_Data(順番))
____________________________str確認メッセージ_+=_"|"_+_Convert.ToString
________________________End_If

________________________順番_=_順番_+_1
____________________Next

「←ここ」の行でSQLを用いて文字列をインサートする時に必要な「'」を付加していますが、氏名以外の
項目のデータ型はINT型ですよね。INT型のデータをインサートする時は「'」を付加する必要は
ありません。

例
○) INSERT INTO test SELECT 1,'山田太郎',100,100,100,300
×) INSERT INTO test SELECT '1','山田太郎','100','100','100','300'

回答No.7

NoMusicNoLife49

こんにちは。

前回のソースの
>Fill(DT)
>End With
の直下に以下のソースをコピーしてください。(＊最後に重要な説明が書きましたので必ず読んでください。

'↓ここから
'変数宣言
Dim ＤＢ接続クラス As System.Data.SqlClient.SqlConnection = Nothing
Dim ＳＱＬ実行クラス As System.Data.SqlClient.SqlCommand = Nothing
Dim トランザクション As System.Data.SqlClient.SqlTransaction = Nothing

'※データベースに接続するためのUser IDとPasswordを指定してください。
Dim strDB接続情報 As String = "Data Source=WS70;" & _
"Initial Catalog=勤怠管理;" & _
"Persist Security Info=True;" & _
"User ID=ID;" & _
"Password=Pass;"

Try
'DB接続クラスインスタンス作成
ＤＢ接続クラス = New SqlConnection(strDB接続情報)

'DB接続
ＤＢ接続クラス.Open()

'トランザクション開始
トランザクション = ＤＢ接続クラス.BeginTransaction

'SQLコマンド実行クラス作成
ＳＱＬ実行クラス = New SqlCommand

With ＳＱＬ実行クラス
'パラメータ初期化
.Parameters.Clear()

'DB接続情報設定
.Connection = ＤＢ接続クラス

'トランザクション指定
.Transaction = トランザクション

'コマンドタイプ設定
.CommandType = CommandType.Text

'SQLコマンド設定
.CommandText = "INSERT INTO SELECT @No,@氏名,@国語,@数学,@英語,@合計点"

'I/Oパラメータ定義
.Parameters.Add("@No", SqlDbType.Int).Direction = ParameterDirection.Input
.Parameters.Add("@氏名", SqlDbType.NVarChar, 8).Direction = ParameterDirection.Input
.Parameters.Add("@国語", SqlDbType.Int).Direction = ParameterDirection.Input
.Parameters.Add("@数学", SqlDbType.Int).Direction = ParameterDirection.Input
.Parameters.Add("@英語", SqlDbType.Int).Direction = ParameterDirection.Input
.Parameters.Add("@合計点", SqlDbType.Int).Direction = ParameterDirection.Input

'SQL実行ループ
For Each DR As DataRow In DT.Rows
Try
'エクセルのデータをSQL実行パラメータに設定
.Parameters.Item("@No").Value = DR(0)
.Parameters.Item("@氏名").Value = Convert.ToString(DR(1))
.Parameters.Item("@国語").Value = DR(2)
.Parameters.Item("@数学").Value = DR(3)
.Parameters.Item("@英語").Value = DR(4)
.Parameters.Item("@合計点").Value = DR(5)

'SQL実行
.ExecuteNonQuery()

Catch ex As SqlException
'SQL例外処理部
'SQL実行時にエラーが発生した場合、このブロックに飛びます。

'エラーメッセージ出力
MsgBox(ex.Message & ex.StackTrace, MsgBoxStyle.Critical)
End Try
Next
End With

'コッミト
トランザクション.Commit()

Catch ex As Exception
'例外処理部

'ロールバック
トランザクション.Rollback()

'エラーメッセージ出力
MsgBox(ex.Message & ex.StackTrace, MsgBoxStyle.Critical)
Finally
If ＤＢ接続クラス IsNot Nothing AndAlso ＤＢ接続クラス.State <> ConnectionState.Closed Then
'DB接続切断
ＤＢ接続クラス.Close()
End If
End Try
'↑ここまで

※-------------------------------- ※
※----------- 重要 ---------------- ※
※-------------------------------- ※

ソースの前半で

>'※データベースに接続するためのUser IDとPasswordを指定してください。
> Dim strDB接続情報 As String = "Data Source=WS70;" & _
> "Initial Catalog=勤怠管理;" & _
> "Persist Security Info=True;" & _
>"User ID=ID;" & _
>"Password=Pass;"

の
>"User ID=ID;" & _
>"Password=Pass;"
の
「ID」と「Pass」をデータベース「勤怠管理」に接続するためのIDとPassに書き換えてください。

お礼

NoMusicNoLife49　さん続いてありがとうございます。
本当に細かく教えていただいてありがとうございます。

私も私なりに考えてNoMusicNoLife49のプログラムを下敷きに作ってみました。
<--- 追加　という所が追加したプログラムです。
勤怠管理DataSetをフォームに貼付けテーブルとつながった状況で
With OleAdapter
.SelectCommand = New OleDb.OleDbCommand
.SelectCommand.Connection = OleConnection
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.CommandText = "SELECT * FROM [sheet1$]"

'エクセルファイルのデータをメモリーテーブルに読込む
.Fill(DT)
Dim str確認メッセージ As String = String.Empty
If DT.Rows.Count = 0 Then
MsgBox("エクセルのデータを取得できませんでした。")
Else
'テーブルデーター削除
Call テーブルデーター削除()　<---追加
'データーを取込
For Each DR As DataRow In DT.Rows
str確認メッセージ = String.Empty
Dim 氏名 As String = ""
順番 = 1
For Each obect As Object In DR.ItemArray
If obect IsNot DBNull.Value = True Then
DataPub(順番) = "'" & Convert.ToString(obect) & "'"　　<--- 追加
'MsgBox(順番 & Data(順番))
str確認メッセージ += "|" + Convert.ToString(obect)
End If
順番 = 順番 + 1
Next
MsgBox(str確認メッセージ)
'テーブルデーター書込
Call テーブルデーター書込()　<---追加
Next
End If
End With

追加したプロシージャーは2つあります。

1つ目のプロシージャー

Private Sub テーブルデーター削除()
'コネクション指定
Using Connection As New _
SqlClient.SqlConnection(My.Settings.処理数ConnectionString)
'コマンド定義
Dim command As SqlClient.SqlCommand = Connection.CreateCommand()
'コネクションを開く
Connection.Open()
'コマンド定義と実行
command.CommandText = _
"TRUNCATE TABLE dbo.test"
'commandを実行
command.ExecuteNonQuery()
'コネクションをclose
Connection.Close()
End Using
End Sub

2つ目のプロシージャー

Private Sub テーブルデーター書込()
'コネクション指定
Using Connection As New _
SqlClient.SqlConnection(My.Settings.処理数ConnectionString)
'コマンド定義
Dim command As SqlClient.SqlCommand = Connection.CreateCommand()
'コネクションを開く
Connection.Open()
'コマンド定義と実行
command.CommandText = _
"INSERT INTO dbo.test(NO,氏名,国語,数学,英語,合格点) SELECT" & DataPub(1) & "," & DataPub(2) & "," & DataPub(3) & "," & DataPub(4) & "," & DataPub(5) & "," & DataPub(6)
'commandを実行
command.ExecuteNonQuery()
'コネクションをclose
Connection.Close()
End Using
End Sub

これで何とか書き込めました。

このプログラムでどこかアドバイスなりありましたらお願いいたします。
本当に細かく教えていただいてありがとうございます。
よろしくお願いいたします。

回答No.6

NoMusicNoLife49

こんにちは、

テーブルtestのカラム
No 氏名 国語 数学 英語 合計点
のそれぞれのデータ型を教えてください。

お礼

NoMusicNoLife49　さん続いてありがとうございます。

SQLのデータ型は次の通りです。

No INT
氏名 nvarchar(8)
国語 INT
数学 INT
英語 INT
合計点 INT

です。
よろしくお願いいたします。

回答No.5

NoMusicNoLife49

> 'エクセルファイルのデータをメモリーテーブルに読込む
>.Fill(DT)
この記述の直下に以下の処理を追加してください。そのままコピペしてください。
ここから↓
Dim str確認メッセージ As String = String.Empty

If DT.Rows.Count = 0 Then
MsgBox("エクセルのデータを取得できませんでした。")
Else
For Each DR As DataRow In DT.Rows
str確認メッセージ = String.Empty

For Each obect As Object In DR.ItemArray
If obect IsNot DBNull.Value = True Then
str確認メッセージ += "|" + Convert.ToString(obect)
End If
Next

MsgBox(str確認メッセージ)
Next

End If
ここまで↑

これ、読込んだエクセルファイルのデータを1行毎に表示します。

お礼

NoMusicNoLife49　さん続いてありがとうございます。

指示された通りやりました。

読み込まれたDataがMsgBoxに表示されました。
|1|浅野　純子|63|69|87|219|
です。

実際にExcelFは

No 氏名 国語 数学 英語 合計点
1 浅野　純子 63 69 87 219

で入っています。
詳しくおしえていただいてありがとうございます。

それで大変甘えて申し訳ないんですが

SQLでtestという名前のテーブルが

No
氏名
国語
数学
英語
合計点

というフィールドで作られていてそのテーブルに
このデーターを書き込みたいのですが、
続いて教えていただけませんでしょうか。

尚、サーバー名は WS70
データーベース名は　勤怠管理
テーブル名は test
フィールド名は前述の通りです。

何から何まですいませんが
よろしくお願いいたします。

回答No.4

NoMusicNoLife49

なるほど。

>VB.netのModule1.vbに貼り付けました。
>Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
><-----ここの書き方が判りません。
>End Sub

モジュールクラスに貼り付けるのではなく、フォームクラスに貼り付けましょう。
※画面（Form1）にボタン（Button2）を作成してください。
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'変数宣言
Dim OleConnection As OleDb.OleDbConnection = Nothing
Dim OleAdapter As OleDb.OleDbDataAdapter = Nothing
Dim DT As DataTable = Nothing

Try
'クラスインスタンス作成
OleConnection = New OleDb.OleDbConnection
OleAdapter = New OleDb.OleDbDataAdapter
DT = New DataTable()

'接続情報設定
OleConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\フォルダ名\読込むエクセルファイル.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

With OleAdapter
.SelectCommand = New OleDb.OleDbCommand
.SelectCommand.Connection = OleConnection
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.CommandText = "SELECT * FROM [読込むエクセルファイルのシート名$]"

'エクセルファイルのデータをメモリーテーブルに読込む
.Fill(DT)
End With

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class

>下記の2ヶ所は書換えました。
>"SELECT * FROM [sheet1$]"
>"Data Source=C:\test.xls;"
OKです。

>又、"Provider=Microsoft.Jet.OLEDB.4.0;"となっているんですが
>サーバーはSQL2000なんですがこの設定でよろしいのでしょうか。
>教えてください。
"Provider=Microsoft.Jet.OLEDB.4.0;"はクライアントPCに保存
しているエクセルを読込むための宣言です。SQL Serverに接続
するための宣言ではありません。

説明が足りなくて申し訳ないですが、上記のサンプルはローカル
PCに保存しているエクセルファイルからデータを取得する所まで
です。
SQLServer2000は使用したことがないので、自信もって回答する
ことができません。それでもよろしければ、お答えします。

お礼

NoMusicNoLife49　さん続いてありがとうございます。

Button2に

'変数宣言
Dim OleConnection As OleDb.OleDbConnection = Nothing
Dim OleAdapter As OleDb.OleDbDataAdapter = Nothing
Dim DT As DataTable = Nothing

Try
'クラスインスタンス作成
OleConnection = New OleDb.OleDbConnection
OleAdapter = New OleDb.OleDbDataAdapter
DT = New DataTable()

'接続情報設定
OleConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

With OleAdapter
.SelectCommand = New OleDb.OleDbCommand
.SelectCommand.Connection = OleConnection
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.CommandText = "SELECT * FROM [sheet1$]"

'エクセルファイルのデータをメモリーテーブルに読込む
.Fill(DT)
End With

Catch ex As Exception
MsgBox(ex.Message)
End Try
で貼り付けました。
デバッグにはひっかかりませんでした。
そのボタン2を押したんですが
何も出てきません。
MsgBox(ex.Message)
で、何かメッセージが出てくると思っていたんですが、
VB.net 全くの初心者です。
これでexにデーターが呼込まれているのでしょうか
すいませんが
続けて教えてください。

回答No.3

NoMusicNoLife49

こんにちは。
下記のように書けば、PCに保存したエクセルファイルを読込めます。

Public Overloads Function GetExcelData() As Boolean
'変数宣言
Dim OleConnection As OleDb.OleDbConnection = Nothing
Dim OleAdapter As OleDb.OleDbDataAdapter = Nothing
Dim DT As DataTable = Nothing

Try
'クラスインスタンス作成
OleConnection = New OleDb.OleDbConnection
OleAdapter = New OleDb.OleDbDataAdapter
DT = New DataTable()

'接続情報設定　※Data Sourceにエクセルファイルの保存先絶対パスを指定
OleConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\フォルダ名\読込むエクセルファイル.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

With OleAdapter
.SelectCommand = New OleDb.OleDbCommand
.SelectCommand.Connection = OleConnection
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.CommandText = "SELECT * FROM [読込むエクセルファイルのシート名$]"

'エクセルファイルのデータをメモリーテーブルに読込む
.Fill(DT)
End With

Return True

Catch ex As Exception
MsgBox(ex.Message)

Return False
End Try
End Function

お礼

NoMusicNoLife49　さんありがとうございます。
早々に
VB.netのModule1.vbに貼り付けました。
するとOverloadsの所が緑の波線になりました。
デバッグにはひっかかりません。

下記の2ヶ所は書換えました。

"SELECT * FROM [sheet1$]"
"Data Source=C:\test.xls;"

それでまったくの初心者なのでButton2_ClickでこのFunctionを動かすにはどう書いたらいいのでしょうか。

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
<-----ここの書き方が判りません。
End Sub

又、"Provider=Microsoft.Jet.OLEDB.4.0;"となっているんですが
サーバーはSQL2000なんですがこの設定でよろしいのでしょうか。
教えてください。

引き続きよろしくお願いいたします。

回答No.2

singlecat

手元にDelphiのものしかなかったので....

StrProvider := 'Provider=Microsoft.Jet.OLEDB.4.0;';
StrDBFile := 'Data Source=' + ExcelFileName +';';
StrExtended := 'Extended Properties=Excel 8.0;';
ADO.ConnectionString := StrProvider + StrDBFile + StrExtended;

※ADOこのコネクションストリングのデータソースをエクセルのファイル名(フルパス)
※この設定で、ADOを宣言して

ADO.SQL.Text := 'SELECT * FROM [Sheet1$]';

※シート名+"$"をテーブル名のようにSELECTしてみてください。

お礼

早々の返信ありがとうございます。
初心者で全く判りません。
Delphi用ということでVB化しようとしたのですが・・・

Dim StrProvider, StrDBFile, StrExtended As String

StrProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
StrDBFile = "Data Source=' + ExcelFileName +"
StrExtended = "Extended Properties=Excel 8.0;"
ADO.ConnectionString = StrProvider + StrDBFile + StrExtended;

'※ADOこのコネクションストリングのデータソースをエクセルのファイル名(フルパス)
'※この設定で、ADOを宣言して

ADO.SQL.Text = "SELECT * FROM [Sheet1$]"

デバッグするとADOは宣言できません。
となります。

全く手がでません。
それとこれはJetではないかと思うのですが?
何から何まですいません。
VBで何か資料があれば教えていただけませんでしょうか。

回答No.1

singlecat

ExcelのデータはADOで普通に"TABLE"と同様に扱えます。
なので、SQLでTABLEとしてSELECTすれば読めます。
※シート名の指定とか詳細は調べてみてください。

SQLサーバへは通常にSQLでINERTすればＯＫです。

お礼

すいません
まったくVB.Net初心者で、判りません。
>ExcelのデータはADOで普通に"TABLE"と同様に扱えます。
の部分が良く判っていないのですが・・

SQLでは簡単には

SELECT AAA
FROM DATABASE

ですが、これはSQL(MS SQL2000)のテーブルに対して
ですよね。

何か参考になる例かサイトでもあれば
教えていただければ幸いです。
よろしくお願いいたします。