Urgent: Help needed with Importing Excel data into Access DB using .asp

[kessa]

Hi Guys,

I've got quite an urgent issue which I'm really hoping someone may be able to help me with as I need to try and do this tonight and it's all new to me (so not asking much there then!:o :rolleyes: )

OK, here goes:

What I need to do is update some data in my Access database, using .asp to grab info from an Excel spreadsheet (tab delimited)

It's basically a load of item codes which have changed and need to be updated.

So in my DB I have a field called "Code".
And in my Excel Spreadsheet I have and column called OldCode (containing - you guessed it - the old codes ;) ) and a column called NewCode (... you get the idea!)

What I need is a script that:

1) goes through each item in the database to see what the code is
2) see if it can find a match in the Excel spreadsheet (OldCode)
3) if it finds a match, update the record in the Database with the NewCode
4) If it doesn't, move onto the next record in the DB and repeat

Any ideas?

Thanks
Kessa

[Chikara]

lol good luck with this one.

http://www.connectionstrings.com/?carrier=excel

1.) Connect to Excel.
2.) Connect to Access using another ConnectionString.
3.) Look for values in Access based on Excel data.
4.) Update.

There is virtually no way you will be able to do this in one day.

[kessa]

Hi Chikara,

Thanks for the link - I'll take a look at that.

There is virtually no way you will be able to do this in one day.

Yeah, that's what I thought - still, I've got to try :(

I think I'm going to need a couple of beers to help me through this one!

If anyone else has any ideas on this please let me know!

Cheers
Kessa

[Chikara]

Sorry I posted an incorrect link. Take a look at the new one.

*MIGHT* not be as complicated as I first thought. You'll just have to test it out though.

Although the error checking will kinda suck for this one. It's probably going to be wise to "map" excel colums to columns in access.

[russell]

save the spreadsheet as a tab delimited text file
loop thru the text file using FSO
update records where different. Easy.

actually, this approach is wrong anyway. should do it from the db:

manually import the file as a new table in access and then write one query to join the two tables and update the original as needed.

update table1
set code = t2.newcode
from table1 t1
inner join table2 t2
on t1.code = t2.oldcode

this should take a few minutes is all

[kessa]

Hi Both,

Thanks ever so much - I didn't have a clue where to start and so really appreciate it!!!!!

I've never had to work with the FSO before and so I think it's probably best if I stick clear of that (unless I can't get anything else to work ;) ) but I do like the idea of importing the data into a temporary table and then looping through that, so I think I'll give that a shot as I think that's probably the limits of my knowledge at the moment.

Cheers again!!!
Kessa

[russell]

glad 2 help. but notice that if u import the file to access, u never need to loop at all. one query will update all the records u need. :)

[Chikara]

Depending on your comfort level of Query statements there are a lot of options it seems. Russell's create a new table and join is certainly the best approach here.

I don't think the FSO is the way to go even though that is an option.

[kessa]

but notice that if u import the file to access, u never need to loop at all. one query will update all the records u need

Apologies in advance if this seems like a silly question :o - doesn't it just import the data rather than find/replace?

If there's a way for Access to do the find/replace for me the that's even better :D , but if it doesn't then I'm guessing I'll still need to loop through them to perform the comparison.

K

[kessa]

Hi Guys,

OK, so here where I've got to so far - problem is, I can't seem to get my looping right - either it find no matches, or I get a buffer error.

Any ideas? (I'm still an .asp newbie)

<%

'connect to the db and select codes from the table - get all item ids for 117
strsql = "SELECT code FROM items WHERE ownersid=117 ORDER BY code ASC"
set rs_existingitems = objconn.execute(strsql)

strsql = "SELECT * FROM newcodes ORDER BY OldCode ASC"
set rs_udateitems = objconn.execute(strsql)

'for each item in the DB, loop through and see if the item code matches OldCode
response.Write("<ol>")

rs_existingitems.MoveFirst
Do While Not rs_existingitems.EOF

' loop through each item in the new codes to see if there is a match
rs_udateitems.MoveFirst
Do While Not rs_udateitems.EOF

if rs_existingitems("code")=rs_udateitems("OldCode") then
response.write("<li><strong>Match</strong> Found</li>")
else
response.Write("<li><strong>No</strong> Match</li> "&rs_existingitems("code")&" - " &rs_udateitems("OldCode"))
end if

'go to the next item in the new code list
rs_udateitems.MoveNext
Loop

' go to the next code in the DB
rs_existingitems.MoveNext
Loop
response.Write("<ol>")
%>

Cheers
Kessa

[kessa]

Update:

Issue sorted (kind of! - most of the records have been successfully updated so I just need investigate what went wrong with the remaining few.)

Cheers
Kessa

[Chikara]

I'm sure if this is relevant to your problem, but if you are comparing strings make sure that you change them to lower or upper.

One != one