Here is the .NET code I wrote to import the CSV file, which has already been downloaded by the Cloud Stream dashboard's 'Details' widget.
The process is in three steps:
- List the CSV files residing in the target directory
- Pick and load one of the CSV files to be imported
- Import the selected CSV file into the Database
'======================================================================================
Sub listFiles()
Dim root As String = lblLocationCSV.Text
Dim fileEntries As String() = Directory.GetFiles(root)
lstFiles.Items.Clear()
Application.DoEvents()
For Each fileName As String In fileEntries
If Microsoft.VisualBasic.Right(fileName, 4) = ".csv" Then
lstFiles.Items.Add(fileName)
End If
Next
End Sub
'======================================================================================
Sub readFile(ByVal thisFile As String)
Dim fileName As String = thisFile
lstFile.Items.Clear()
Application.DoEvents()
If System.IO.File.Exists(fileName) = True Then
Dim objReader As New System.IO.StreamReader(fileName)
Do While objReader.Peek() <> -1
lstFile.Items.Add(objReader.ReadLine())
Loop
Else
MessageBox.Show("File Does Not Exist")
End If
If lstFile.Items.Count = 0 Then '
bttnImportFile.Enabled = False
Else
bttnImportFile.Enabled = True
End If
End Sub
'======================================================================================
Sub importfile()
Dim i As Integer
Dim arr() As String
Dim intPK As Integer
If lstFile.Items.Count = 0 Then
Exit Sub
End If
intAdded = 0
For i = 0 To lstFile.Items.Count - 1
lblAdding.Text = ""
Application.DoEvents()
lstFile.SelectedIndex = i
arr = Split(lstFile.Text, ",")
If arr(0) = "pk" Then
Else
intPK = CInt(arr(0))
'FIRST QUERY IF pk ALREADY EXISTS:
If gotPK(intPK) = False Then
'add to DB:
Call importToDB(lstFile.Text)
End If
End If
Next
lblRecordsAdded.Text = "Records added: " & intAdded
lblAdding.Text = ""
Application.DoEvents()
Call copyFile()
End Sub
'======================================================================================
Function gotPK(ByVal thisPK As Integer) As Boolean
gotPK = True
thisSQL = "SELECT * FROM tblLPR WHERE pk = " & thisPK & " "
Application.DoEvents()
Dim cmd As New SqlClient.SqlCommand
With cmd
.CommandText = thisSQL
.CommandType = CommandType.Text
.CommandTimeout = 30
.Connection = conn
End With
Dim dtwait As New DataTable
dtwait.Load(cmd.ExecuteReader)
If dtwait.Rows.Count = 0 Then
gotPK = False
Else
gotPK = True
End If
dtwait = Nothing
cmd = Nothing
End Function
'======================================================================================
Sub importToDB(ByVal thisLine As String)
Dim arr() As String
Dim cmd As New SqlClient.SqlCommand
bolImported = True
arr = Split(thisLine, ",")
'SKIP: 1, 2, 7, 8, 16
thisSQL = "INSERT INTO tblLPR(pk,best_confidence,best_index,best_plate,best_uuid,direction_of_travel_degrees,direction_of_travel_id,epoch_time_end,epoch_time_start,hit_count,region,region_confidence,vehicle_body_type,vehicle_body_type_confidence,vehicle_color,vehicle_color_confidence,vehicle_make,vehicle_make_confidence,vehicle_make_model,vehicle_make_model_confidence,vehicle_region_height,vehicle_region_width,vehicle_region_x,vehicle_region_y) "
thisSQL = thisSQL & "VALUES (" & arr(0) & "," & arr(3) & "," & arr(4) & ",'" & arr(5) & "','" & arr(6) & "'," & arr(9) & "," & arr(10) & ",'" & arr(11) & "','" & arr(12) & "'," & arr(13) & ",'" & arr(14) & "'," & arr(15) & ",'" & arr(17) & "'," & arr(18) & ",'" & arr(19) & "'," & arr(20) & ",'" & arr(21) & "'," & arr(22) & ",'" & arr(23) & "'," & arr(24) & "," & arr(25) & "," & arr(26) & "," & arr(27) & "," & arr(28) & ") "
With cmd
.CommandText = thisSQL
.CommandType = CommandType.Text
.CommandTimeout = 30
.Connection = conn
.ExecuteNonQuery()
End With
cmd = Nothing
intAdded = intAdded + 1
lblAdding.Text = intAdded & " - Adding " & arr(5)
Application.DoEvents()
End Sub
'======================================================================================
UPDATE: Here's the function which converts OpenALPR's universal time to local time:
Function convertUtoL(ByVal thisDate As String) As DateTime
Dim strDate As String
Dim strTime As String
Dim eDate As String
'SAMPLE thisDate, from the database . . .
'2019-01-26T19:14:06.136Z
strDate = Microsoft.VisualBasic.Left(thisDate, 10)
strTime = Mid(thisDate, 12, 8)
eDate = strDate & " " & strTime
Dim expenddt As DateTime = DateTime.ParseExact(eDate, "yyyy-MM-dd HH:mm:ss",
System.Globalization.DateTimeFormatInfo.InvariantInfo)
dteUTC = expenddt
Dim d As DateTime = DateTime.Parse(dteUTC) 'this is universal
Dim u As DateTime = d.ToLocalTime 'this is is local central daylight
d = u.ToUniversalTime 'double check
dteLocal = u
convertUtoL = dteLocal
End Function
'======================================================================================
