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.
| 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 |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-08-05 : 06:21:25
|
| http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926MadhivananFailing to plan is Planning to fail |
|
|
|
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) |
|
|
|
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 |
|
|
|
|
|
|
|
|
Mass update from an Excel Spreadsheet