Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Mass update from an Excel Spreadsheet

Author  Topic 

berndh
Starting Member

6 Posts

Posted - 2006-08-03 : 10:47:32
Hi all,

I have a slight problem. I need to update the price field in a database with the new prices in an excel spreadsheet.

1. The spreadsheet uses multiple sheets.
2. The spreadsheet does not use indices, but a similarity is an isbn number (ie the database uses book_id as index and the spreadsheet does not have this)

How on earth do I get a sql script working that can search through the Excel spreadsheet for a specific ISBN number and the extract the price for that number and update itself??

isbn is always in coloum A, price is always in tha same row as the ISBN, but in coloum C. The fileds in SQL are - ISBN is isbn and price is price, the table these files are in is books.

I'm unfortunately no fundy (newbie) and need some detailed steps.

Thanks

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-03 : 14:15:50
berndh,

Use a staging table. Import the Excel spreadsheet into the staging table; you can then use UPDATE statements to match the data to your database records.

Forgot to mention it... You can find examples of importing the spreadsheet by searching these forums for Excel+OpenRowset.

Ken
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-05 : 06:21:25
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

berndh
Starting Member

6 Posts

Posted - 2006-08-05 : 11:29:42
Thanks Ken and Madhivanan,

What a read, thanks, but this does not really help me.

I need (MUST) use an update query to get the prices into the SQL DB.

Below are two screenshots of what the Excel file looks like and the fileds in SQL(not all)
The purple field is a header they use to devide the lists into different categories, these are merged cells. The ISBN (this is the ONLY number that will match in both Excel and SQL) is always in Column A, followed by the Title and price, the second row for a book only contains the Authors.

Now you see my dilemma, How do I get a script to read through the Excel file looking for the ISBN numbers, then reading the price for that number and finally, it needs to find that number in the SQL DB and update the price. To make matters worse, or maybe not, some of the ISBN numbers in the spreadsheet may not be contained in the DB (on can be fancy here and insert all the details in this case as well as make a field called active=0, but this would be nice to have, main worry at the moment is the price though)

it would make sense to use a staging table, but how does the rest work...

Boy oh boy.... I'm stuck


as you see, ISBN always in column A and alternate Rows (sometimes more as the headers are also there)

Go to Top of Page

micagordon
Starting Member

6 Posts

Posted - 2015-07-03 : 01:47:53
Search ISBN in Excel, I guess you may means scan ISBN from Excel, I only know how to create ISBN in Excel, and for reading ISBN in Excel, you can have a look at: http://windowssecrets.com/forums/showthread.php/74002-Scanning-ISBN-into-excel-%28Excel-2002%29
Go to Top of Page
   

- Advertisement -