There MUST be a better way! Updating table from an Excel list

[RRinTetons]

I often get an Excel spreadsheet with a column a up to several thousand PK's and a new value that needs to be updated for those rows. To date, I've written one UPDATE statement for the target table with placeholders for the PK and update value, pasted that into Excel and then used Excel's SUBSTITUTE function to create up to several thousand UPDATE statements. It works, it's flexible and to date it hasn't presented any performance issues.

It just seems too damn kludgy! Does anyone have a suggestion on a better way to do this? Again, these are one off's, rarely if ever repeated, applied to a variety of tables.

-
Richard Ray
Jackson Hole Mountain Resort

 

[PWise]

your excell table must look like this



oldpk newpk
1 75
2 86
3 329
......


if so look @ BOL linkedservers,openrowset,opendatasource

use this statment


update mytable
set myfield = newpk
inner join opendatasource()
on myfield =oldpk

 

[RRinTetons]

Actually, it's another column that I need to update, so the Excel sheet looks like:

PK newValueForColumn
1234 45
4567 15
4455 25
.....

It looks like it's the same idea though, so I'll go in that direction.

Thanks!!

-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

Close, but missing something.

Using SQL Server 2008 R2 64-bit, so I got the 64-bit provider from Microsoft's site: Microsoft.ACE.OLEDB.12.0. Excel file is from Excel 2010 in xlsx format.

My first try looks like:

SELECT * 
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\RRAdj.xlsx;Extended Properties=EXCEL 14.0')...[Sheet1$] ;

From which I get:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I understand the reference to "Could not find installable ISAM." to mean it's looking for a schema.ini file, but it seems that's only supposed to be required for a text file, I'm not certain what it would look like for an Excel file.

I'm also suspicious of heat 'linked server "(null)"'. Do I need to create a linked server of some sort?

-
Richard Ray
Jackson Hole Mountain Resort

 

[markros]

Do you know if this Excel file was created in Office 2010 64 bit or 32 bit version?

There is a blog post I know of that may help

http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/ace

PluralSight Learning Library

 

[RRinTetons]

The original Excel file was created in Office 2010 32-bit, but edited and saved in Office 2010 64-bit.

I followed through the config changes from the blog post. I'd already set the config for ad hoc distributed queries, but I added:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

The first one I understand, the second seems unnecessary, but...

Now I get:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Progress, I guess. Or at least a different error message.

-
Richard Ray
Jackson Hole Mountain Resort