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 StringstrCon =
"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
How do I insert data from a flat file or .csv file into an existing SQL database???
KTaylor
wyattblake
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
204198
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."
Kagan A
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
push_ebx
Very easy to do using sqlserver import feature..
u can use text option in sql server import...it 1 minute job.
write a DTS package for this...and call this DTS package from C#.NET or any .NET code.