Updating database from cvs file in VB.Net

I got a simple request to let shippers upload information with shipped dates in csv files .

Now this is rather simple request and problem especially in versatile framework like .Net . I figured just googling around would find me simple solution, however it took a little bit of more search and tweaking to get this working which is why I want to share this piece of code.

So there is a page with upload field and this is on click event of submit button:

Dim filename As String
filename = Server.MapPath(shippingUpdateFile.FileName)

Dim sConnectionString As String = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & Server.MapPath(“.”) & “;Extended Properties=Text;”
Dim cvs_db As New System.Data.OleDb.OleDbConnection(sConnectionString)
cvs_db.Open()

Dim cvs_select As New OleDbCommand(“SELECT * FROM ” & filename, cvs_db)
Dim cvs_adapter As New OleDbDataAdapter
cvs_adapter.SelectCommand = cvs_select
Dim csv_ds As New System.Data.DataSet
cvs_adapter.Fill(csv_ds, “test”)
Dim csv_table As New System.Data.DataTable
csv_table = csv_ds.Tables(0)
Dim r As Data.DataRow
Dim id, sql As String
Dim date_shipped As Date
For Each r In csv_table.Rows
id = r(“id”)
date_shipped = CDate(r(“shipped_date”))
sql = “Update users set ”
sql += “DateFulfilled='” + date_shipped.ToShortDateString + “‘”
sql += ” where id=” + id.ToString
If utilities_db.execute_sqlcommand(sql) <> 0 Then
Me.lblMessage.Text = “Error updating record:” + id.ToString
End If
Next

This could probably be written in a little more clean way, but this is how it is and you can pick it up from here.

One thing to note is that utilities_db is simple module with db functions. Hope you will find this useful.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s