Updating SQL database from Excel

[theevilone]

I have some SQL Server 2008 databases hosted on a remote server. One of the databases has an employee table with x number of fields. A customer has supplied me with an excel spreadsheet which contains a large number of number of employee details which need updating.

The Excel spreadsheet contains some but not all the fields in the remote database. As there is no unique identifier between the two, I will use employee email addresses as the unique identifier. What I need to be able to do is to write a SQL script which goes through a loop for each record in the spreadsheet and says if the email address matches the email address of a record in the table on the remote database, then update the details of the record with the details in the spreadsheet. But, without removing the other fields in the table, which should remain unaffected.

I hope that I have made myself clear. I would very much appreciate help on this. Gratitude and thanks in advance.

Best regards

 

[pjw001]

I'm sure it is possible to do it from within Excel, but probably by writing a Macro.

An easier alternative might be to load the spreadsheet into a 'work' table, possibly via a csv file, and then write a SQL script to update the employee table from the 'work' table.

 

[theevilone]

This doesn't really help. As I said in my thread, if someone can help me write the SQL script, I would be extremely grateful.

 

[RiverGuy]

Typically in Excel, I will build the SQL script within the cells themselves, and then copy and paste the script into management studio to execute.

For simplicity's sake, let's say you have two columns in Excel: EmailAddress in Column A, and DepartmentID in Column B. In Column C, Row 1, I would write the following formula: ="UPDATE Name_Of_Table SET DepartmentID = " & B1 & " WHERE EmailAddress = '" & A1 & "'"

I would then drag the formula down to the other rows, copy, and paste into SSMS.

 

[drlex]

Is there an agreement of how the update spreadsheet is to be formatted?

Ideally it would be normalised with three pieces of data - employee identifier, field to be changed and the new value. Then, as pjw001 relates, one could do a two-stage process of reading the spreadsheet and then updating the employee table accordingly.

If it's a headed spreadsheet, such that the headers are the field names, it would make sense to construct a temporary table using the first row, to allow for a change between versions.

I'd also recommend that
1) any updates are logged, so as to provide a record in the case of subsequent issues arising.
2) your update procedure also identifies rows that provide no match and hence no update.

Help can be given here in writing the required SQL, but an understanding of the specifics of the process is a pre-requisite. Otherwise it will be generic assistance only.

soi là, soi carré

 

[pjw001]

I don't see why you want to loop over the records. Once you have the data loaded a single update statement should suffice.


update e
set field1 = t.field1,
field2 = t.field2,
etc
from employees e, tablefromexcel t
where e.emailaddress = t.emailaddress

 

[theevilone]

Thank you for your help, guys. I came across a script which I am trying to get working to do this.

Insert into dbo.customers (customerid,companyname,contactname)
Select customerid, companyname, contactname FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\sql\update3.xls;HDR=YES',
'SELECT customerid, companyname, contactname FROM [Sheet1$]')


The problem is that, the database in the SQL Server db has 20 fields, including customer names, email addresses, department etc. The customer sends me a damn Excel spreadsheet with just a few fields, any one of which she wants changed.

So, the script needs to access the Excel spreadsheet in one of its drives, does a query which says

If email address in the first record matches a record in the SQL db, amend the record in the SQL db with the fields in the Excel spreadsheet. And so on, for all the records in the Excel spreadsheet. The other columns in the sql DB, not in the Excel spreadsheet, should remain unaffected.

Many thanks in advance.

 

[drlex]

The customer sends me a damn Excel spreadsheet with just a few fields, any one of which she wants changed.

Will the header row, which identifies the fields, be
1) static
2) match the fields in your employee table
?

soi là, soi carré

 

[theevilone]

The last excel spreadsheet did not match the fields in the employee table at all. But I could manipulate it to do so, if need be.

I am guessing by the way this customer works, the header row will not be static.

Thank you.

 

[SkipVought]

This is really a question for forum707. Excel VBA is where the action is.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheBugSlayer]

In its generic form, your query should look like this:

UPDATE RemoteTable R
JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Projects\ExcelBook.xlsx;HDR=YES', 
     'SELECT * FROM [Sheet1$]') Excel
     ON Excel.EmailAddress = R.EmailAddress
SET R.F1 = Excel.F1Equivalent,
    ...
    R.Fn = Excel.FnEquivalent

But as suggested in another reply, it may be better to load the Excel sheet into a table, perform the necessary modifications then run the update statement above, replacing the whole OPENROWSET statement with the table that contains the imported Excel data. And if the columns the user wants updated each time are different then human intervention is required each time to run the update. If the column names are not the same between the Excel sheet and the table, the only way to automate this would be to create a mapping table then use dynamic SQL to build the update statement. A lot of work.

Good luck.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)