I've looked just about everywhere and pieced together as best as I can the code to update the tracking number in a CSV file for an order that has shipped out from our company so we can upload the file to our online shopping cart. I have 1 file with 3 columns that is an export from our shipping program, and the file that needs to be updated that we download from the shopping cart, insert the tracking numbers and re-upload.
File 1:
OrderNumber CustomerName TrackingNumber
280 PRISCILLA JOHNSON 1Z9R6V790355154706
271 SPENCER NEVEU 1Z9R6V790355180991
2208902 PATRICK KAUFMAN 1Z9R6V790355200423
286 DIANA DOETZEL 1Z9R6V790355454758
279 BARRY NUSSBAUM 1Z9R6V790355727294
257 JOHN LACY 1Z9R6V790356178288
270 MEGAN JOSE 1Z9R6V790356316235
202 BENJAMIN MACLENNAN 1Z9R6V790356624652
266 LEONARDO RODRIGUEZ 1Z9R6V790357024183
268 BRENT ROOZEN 1Z9R6V790357179016
287 KIM PIPPINGER 1Z9R6V790357311167
204 ANGELA EPSTEIN 1Z9R6V790357347049
File 2:
Order Number Customer Name Order Date Total Tracking Number Order Status
270 megan jose 7/7/2008 38.74 Shipped
239 WENDI SANDELL 7/6/2008 298.49 Shipped
240 Glenn Shane 7/6/2008 61.82 Shipped
268 Brent Roozen 7/7/2008 88.97 Shipped
269 william rohacik 7/7/2008 33.32 Shipped
242 magnus ekstrand 7/6/2008 227.25 Shipped
245 ALLISON CASTILLO 7/6/2008 104.27 Shipped
249 Kevin Lusinski 7/6/2008 86.06 Shipped
252 SEVAG KASARDJIAN 7/6/2008 77.94 Shipped
254 matthew kilmon 7/6/2008 63.92 Shipped
265 Filipe Azevedo 7/6/2008 83.44 Shipped
255 nabila wassef 7/6/2008 50.9 Shipped
256 Christopher Petro 7/6/2008 111.49 Shipped
257 John Lacy 7/6/2008 130.99 Shipped
258 Brandon Bletzacker 7/6/2008 77.49 Shipped
258 Shamae Stephens 7/6/2008 77.49 Shipped
259 Lisa A Washabaugh 7/6/2008 47.51 Shipped
Code:
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\CustomPrograms2\WebOrders1\output_files\ ;Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Dim strLine As String = ""
Dim trimChars() As Char = {ControlChars.Quote}
Dim iOrderNum As Integer = 0
Dim sTracking As String = ""
Dim inWebCSV As IO.StreamReader
Dim olecon As OleDb.OleDbConnection
Dim oleadpt As OleDb.OleDbDataAdapter
Dim olecomm As OleDb.OleDbCommand
Dim oleadptcomm As OleDb.OleDbCommand
Dim sqlQry As String = ""
Dim ra As Integer = 0
Dim ds As DataSet
'Try
olecon = New OleDb.OleDbConnection
olecon.ConnectionString = connstring
oleadptcomm = New OleDb.OleDbCommand
oleadptcomm.CommandText = "SELECT * FROM orders.csv"
oleadptcomm.Connection = olecon
ds = New DataSet
oleadpt = New OleDb.OleDbDataAdapter(oleadptcomm)
olecon.Open()
oleadpt.Fill(ds, "orders.csv")
With OpenFileDialog1.ShowDialog()
OpenFileDialog1.InitialDirectory = "c:\kona\data\dataport\ship\"
inWebCSV = New IO.StreamReader(OpenFileDialog1.FileName)
End With
'Read the first line of text from the web csv
'This is the header stuff
strLine = inWebCSV.ReadLine
'Read the first interesting line
strLine = inWebCSV.ReadLine
'Continue to end of the file.
Do While Not strLine Is Nothing
'Split the line into parts using the "," as the delimiter
Dim strArray1() As String = Split(strLine, ",", 3)
iOrderNum = strArray1(0).Trim(trimChars)
sTracking = strArray1(2).Trim(trimChars)
sqlQry = "UPDATE orders.csv SET `Tracking Number` = '" & sTracking & "' WHERE `Order Number` = '" & iOrderNum & "'"
olecomm = New OleDb.OleDbCommand(sqlQry, olecon)
oleadpt = New OleDb.OleDbDataAdapter(olecomm)
oleadpt.UpdateCommand = olecomm
ra = oleadpt.Update(ds, "orders.csv")
MessageBox.Show("Records affected: " & ra)
strLine = inWebCSV.ReadLine
Loop
MessageBox.Show("File Created", "Success", MessageBoxButtons.OK)
'Catch errorVariable As Exception
'MessageBox.Show(errorVariable.ToString(), "Error", MessageBoxButtons.OK)
'Finally
inWebCSV.Close()
olecon.Close()
'End Try
End Sub
I commented out the try-catch part temporarily for easier debugging. I'm always returning 0 rows updated and I would like to know why and how I can fix it.