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