469,936 Members | 2,487 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,936 developers. It's quick & easy.

insert CSV into access

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")
%>
Jul 19 '05 #1
1 4604
> 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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by deancoo | last post: by
2 posts views Thread by Geoffrey KRETZ | last post: by
3 posts views Thread by MP | last post: by
6 posts views Thread by rn5a | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.