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

Question

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初心者でやり方がわかりません。

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

NoMusicNoLife49 · Accepted Answer

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

気になったのは、
以下の部分の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'

NoMusicNoLife49 · Answer

こんにちは。

前回のソースの
>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 · Answer

こんにちは、

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

NoMusicNoLife49 · Answer

> 'エクセルファイルのデータをメモリーテーブルに読込む
>.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 · Answer

なるほど。

>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:	est.xls;"
OKです。

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

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

NoMusicNoLife49 · Answer

こんにちは。
下記のように書けば、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

singlecat · Answer

手元に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してみてください。

singlecat · Answer

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

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

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

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

こんにちは。

こんにちは、

> 'エクセルファイルのデータをメモリーテーブルに読込む

なるほど。

こんにちは。

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

ExcelのデータはADOで普通に"TABLE"と同様に扱えます。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング