Updating SQL from Excel 1

[Guest_imported]

I have a database full of Contact information, and I want to mass update the phone number field with data that I have purchased that is in an Excel Spreadsheet. I have a row id in the spreadsheet that matches to the database, so all I need to do is add an update statement that finds the row id on the spreadsheet, matches it to the row id in the database, and updates the phone number field. There are roughly 24,000 records, so I was wondering if I could do this via a batch? Anyone evey done this? Thanks.

Duff

 

[MaffewW]

are the same 24000 records in both tables. If so this should work (this presumes you have imported the excel sheet into a new table called newtable and have 2 columns, rowid and phonenumber). If not may need to play about with the join so pls provide more info

update oldtable
set oldtable.phonenumber = oldtable.phonenumber
from newtable inner join oldtable
on newtable.rowid = oldtable.rowid

I havent tested it, so pls try yourself first before running (or at least backup your tables first!)

Matt

Brighton, UK

 

[NoCoolHandle]

Duff, it sounds like a great place to use Automation.. (Like the big words)

What I mean by this is that you need to write some ado code and some vba code to loop thorugh the rows on your spread sheet and update the columns in your database...

You could do this by referencing adodb in ("microsoft ado&quot in the visual basic editor of excel and then opening a connection object to your database.

assuming that you have an open connection you could then add code that looks something like

Dim x As Integer

For x = 1 To 2500
cn.Execute "update maillist set phonenumber = '" _
& Cells(x, 4) & "' where maillistnumber = " & _
Cells(x, 1)
Next

Where cn is the name of your open connection and column 4 has the phone number and column 1 has the maillist id..


HTH


Rob

 

[essa2000]

I think there should be newtable.phonenumber on the right side of the set expression !

update oldtable
set oldtable.phonenumber = newtable.phonenumber
from newtable inner join oldtable
on newtable.rowid = oldtable.rowid


All the best !


Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[MaffewW]

thank you for correcting my typo Essa, hope it didnt cause you a problem Duff

Matt

Brighton, UK

 

[Guest_imported]

Thanks guys, I will look into the VBA code to loop through the spreadsheet, as I think this will be a common occurance. Thanks again.

Duff