Update SQL from Excel - newbie question and a challange

[berndh]

Hi,

I have a need to update all price fields in an SQL database. The new prices are in an excel spreadsheet (c:\db\update.x ls).

Unfortunately the structure of the Excel file is not the same as the SQL DB, as it is formatted for printing hardcopies and hence contains merged cells (purple) and formatting etc....(see excel.jpg) Also it uses different sheets as categories.

The only real similarity that can be used is the ISBN number in column A. This number will always be the same in Excel and in SQL. The latter field is called "isbn" in the SQL DB (see sql.jpg)

How on earth do I read the Excel spreadsheet, match each ISBN number and then update the price for that specific ISBN, then move to the next sheet and do it all over again?? A staright import into SQL is not an option as the SQL DB contains other fields as well that are not contained in the spreadsheet.

step by step if you can please..

Thanks
Bernd

[moldster]

Hi,

Why don't you import the excel to it's own table and write a stored proc to do the updates for you?

Cheers
C

[berndh]

Hi,

Now please excuse me for sounding dumb, I've been dumped in the deep end here and am treading water, no SQL experiance, rading book after book....

You mean to import into a temp table (got this working) and then update, sounds fine, but how on earth do I update, that is my big problem. How do I get this thing to match the ISBN numbers and then accordingly extract the price....

STUCK.

Thanks

[chandra2001]

Hi,

You refer this link this may solve your problem.

Thanks,
- Chandra

[chandra2001]

http://www.sqlteam.com/forums/topic.asp?TOPIC _ID=49926

[chandra2001]

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Mic rosoft.Jet.OLED B.4.0',
'Excel 8.0;Database=D: \testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Mic rosoft.Jet.OLED B.4.0',
'Excel 8.0;Database=D: \testing.xls;HD R=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Mic rosoft.Jet.OLED B.4.0',
'Excel 8.0;Database=D: \testing.xls;HD R=YES',
'SELECT * FROM [SheetName$]')

[berndh]

Thank you,

Very interesting read. I shall get into this and see if I can make it work for me.

Regards