How do I insert data from a flat file or .csv file into an existing SQL database?

How do I insert data from a flat file or .csv file into an existing SQL database?

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)

insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @.Event " & _

",[Year] = @.Year " & _

",[Contract Loss] = @.ConLoss " & _

",[Company Loss] = @.CompLoss " & _

",[IndInsured Loss Prop] = @.IndLossProp " & _

",[IndInsured Loss WC] = @.IndLossWC " & _

",[Event Info] = @.EventInfo", dbconn)

upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")

************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

Take a look at this post. I have given an example using OleDbCommand and SqlCommand.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1

Note that I am reading from excel and inserting into SQL database.

Hope this helps

|||

Sample code below:

Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\;" + "Extended Properties=""Text;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"

cn.Open()
sqlcn.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from test.csv"
adapter.SelectCommand = cmd
adapter.Fill(dtset, "MyTable")
dt = dtset.Tables("MyTable")

sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into MyParts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text

sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)

For Each dr In dt.Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next

cn.Close()
sqlcn.Close()

Hope this helps

|||Raj you are a life saver... with a few minor conversions that was exactly what I needed... Thanks Again|||

Too much work... Look up Linkserver/Excel under help. After this you can query the excel file as a table and if you like you can use normal sql to update your other table from the excel file which is now linked to your server.

Ed

"I don't know what I don't know."