insert CSV into access

[Joost]

Hi,

I use the following code to insert the data from my CSV into an access
database. But now I want the script to UPDATE the courses id they
already exist (so when the COURSENUMBER is known in the table),
otherwise he has to add just a new record.

How can I do this?

Thanks!
Joost

<%
'create instance of the Connection object
Set objRS = Server.CreateObject("ADODB.Recordset")
SQLstmt = "SELECT * FROM blaat"
objRS.Open SQLstmt, Conn , adOpenStatic , adLockOptimistic
'declare our variables for the file handling
Dim objFSO , strURL , objFile
'create an instance of the file system object
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
'this is the csv file
strURL = Server.MapPath("test.csv")
'open the file
Set objFile = objFSO.OpenTextFile(strURL)
'while we are not at the end of the file
Do While Not objFile.AtEndOfStream
'store the contents of the file in strText
strText = objFile.readLine
'split the strText
arrText = split(strText, ";", 6)
objRS.AddNew
objRS("coursenumber") = arrText(0)
objRS("place") = arrText(1)
objRS("time") = arrText(2)
objRS("day1") = arrText(3)
objRS("day2") = arrText(4)
objRS("day3") = arrText(5)
objRS.Update
Loop
'close and destroy objects
objRS.Close
objFile.Close
Set objRS = nothing
Set Conn = nothing
Set objFile = Nothing
Set objFSO = Nothing
Response.Write("status ok")
%>

[Aaron Bertrand - MVP]

> Set objRS = Server.CreateObject("ADODB.Recordset")

> SQLstmt = "SELECT * FROM blaat"

Stop doing this, for starters. Why do you need to select the entire table
back to the ASP page, just to add rows?

> Do While Not objFile.AtEndOfStream

Stop doing this, also. How about:

arrLines = split(objFile.ReadAll(), vbCrLf)
for i = 0 to ubound(arrLines)
arrText = split(arrLines(0), ";", 6)
...
next

> objRS.AddNew
> objRS("coursenumber") = arrText(0)
> objRS("place") = arrText(1)
> objRS("time") = arrText(2)
> objRS("day1") = arrText(3)
> objRS("day2") = arrText(4)
> objRS("day3") = arrText(5)
> objRS.Update

Ugh. As you're going through the loop, assuming CourseNumber is, in fact, a
number:



sql = "SELECT courseNumber FROM blaat " & _
" WHERE courseNumber=" & arrText(0)

set rs = conn.execute(sql)

if rs.eof then
sql = "INSERT blaat(coursenumber, ...) " & _
"VALUES(" & courseNumber & ", ...)"
else
sql = "UPDATE blaat SET ... " & _
"WHERE courseNumber = " & arrText(0)
end if
conn.execute sql, , 129