CSV to DB but with a comparison inbetween!

[mdwebster]

:eek:
Guys having trouble with CSV to DB - any help would be appreciated!

Essentially its an automotive website in which the stock is supplied as a TXT / CSV file by a 3rd party and imported daily into the mysql db. The website also has a CMS in which the admin can specify what cars from the imported list they want as "car of the week" (done by a boolean flag), however at the moment everytime the csv gets processed the flags reset.

I am trying to create a script to keep the flags in the DB i.e. Currently read a CSV file and then checks if car on the CSV already exists in the database via the reg plate. If car exists then skip that line in the CSV file otherwise insert records

The code I have is


'open db
Dim sConnection, objConn , objRS
sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=; DATABASE=; UID=;PASSWORD=; OPTION=3"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(sConnection)

'declare variables
dim FSO, csv
Set FSO = Server.CreateObject("Scripting.FileSystemObject")

'check csv file exists
IF FSO.FileExists(Server.MapPath("cars2.txt")) = true then

Function SQLText(val)
SQLText = "'" & Replace(Trim("" & val),"'","''") & "'"
End Function

Set csv = FSO.OpenTextFile(Server.MapPath("cars2.txt"))
counted = 1
csv.SkipLine 'skip the csv headers

Do Until csv.AtEndOfStream
counted = counted + 1
line = csv.ReadLine
fields = Split(line,""",""")
SQLvehicleReg = SQLText(fields(2))
'response.Write("SQLvehicleReg=" & SQLvehicleReg & "<br />")
If SQLvehicleReg <> "''" Then

Set selectRS= Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT vehicleReg FROM tbl_products"
selectRS.Open strSQL, objconn
'if selectRS.EOF then
'response.Write("emptyDB")
'else
Do until selectRS.EOF

DBvehicleReg = SQLText(selectRS("vehicleReg"))
response.Write("DBvehicleReg=" & DBvehicleReg & "<br />")
'End if
If SQLvehicleReg = DBvehicleReg then
'response.Write("duplicates")
csv.SkipLine
else
'assign csv fields to variables
'car_colour = SQLText(fields(3))
'car_fueltype = SQLText(fields(4))
'car_year = SQLText(fields(5))
'car_mileage = SQLText(fields(6))
'car_bodytype = SQLText(fields(7))
'car_doors = SQLText(fields(8))
'car_make = SQLText(fields(9))
'car_model = SQLText(fields(10))
'car_variant = SQLText(fields(11))
'car_enginesize = SQLText(fields(12))
'car_price = SQLText(fields(13))
'car_transmission = SQLText(fields(14))
'car_photos = SQLText(fields(15))
'car_category = SQLText(fields(18))
'car_specifications = SQLText(fields(20))
'car_comments = SQLText(fields(21))

baseSQL = "INSERT INTO tbl_products (vehicleReg) VALUES ("
SQL = baseSQL & SQLvehicleReg & ")"
Response.Write "DEBUG " & SQL & "<HR>" & vbNewLine
objconn.execute SQL

End if

selectRS.movenext
loop

End If :eek:
Loop
ELSE
response.Write("no file exists")
End if


%>

[Old Pedant]

The easy way to do this would be to make vehicleReg the PRIMARY KEY of your tbl_products table. So then MySQL would not allow you to overwrite the existing record and would, instead, throw an error.

And so you would just change the line
objconn.execute SQL
into something like this, perhaps:

howmany = -1
On Error Resume Next
objconn.Execute SQL, howmany
On Error GoTo 0
If howmany <> 1 Then Response.Write SQLvehiclereg & " already in DB, skipped<br/>"

And stop doing the silly loop through the DB.

[Old Pedant]

Here, like this:

IF Not FSO.FileExists(Server.MapPath("cars2.txt")) Then
Response.Write "File cars2.txt does not exist...aborting"
Response.End
End If

Set csv = FSO.OpenTextFile(Server.MapPath("cars2.txt"))
skipped = 0
added = 0
csv.SkipLine 'skip the csv headers

Do Until csv.AtEndOfStream
fields = Split(csv.ReadLine,""",""")
'assign csv fields to variables
SQLvehicleReg = SQLText(fields(2))
car_colour = SQLText(fields(3))
car_fueltype = SQLText(fields(4))
car_year = SQLText(fields(5))
car_mileage = SQLText(fields(6))
car_bodytype = SQLText(fields(7))
car_doors = SQLText(fields(8))
car_make = SQLText(fields(9))
car_model = SQLText(fields(10))
car_variant = SQLText(fields(11))
car_enginesize = SQLText(fields(12))
car_price = SQLText(fields(13))
car_transmission = SQLText(fields(14))
car_photos = SQLText(fields(15))
car_category = SQLText(fields(18))
car_specifications = SQLText(fields(20))
car_comments = SQLText(fields(21))

SQL = "INSERT INTO tbl_products (list, of, fields,...) VALUES (" _
& ... put all those fields into the sql statement here ...
& ")"

howmany = -1
On Error Resume Next
objconn.Execute SQL, howmany
On Error GoTo 0
If howmany <> 1 Then
skipped = skipped + 1
' optional message
Response.Write SQLvehiclereg & " already in DB, skipped<br/>"
Else
added = added + 1
' optional message
Response.Write SQLvehiclereg & " added to DB<br/>"
End If
Loop
Response.Write "<hr/>Added " & added & " vehicles, skipped " & skipped & " vehicles<hr/>"

[Old Pedant]

Note: If you MUST have some other field as the PRIMARY KEY on that table, then just add
UNIQUE INDEX noDupeVehicles ( vehicleReg )

to the table, which will accomplish the same thing.