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)
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
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
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.