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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Mass update from an Excel Spreadsheet
 Reply to Topic
 Printer Friendly
Author  Topic Next Topic  

berndh
Starting Member

6 Posts

Posted - 08/03/2006 :  10:47:32  Show Profile  Reply with Quote
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

USA
391 Posts

Posted - 08/03/2006 :  14:15:50  Show Profile  Reply with Quote
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

Edited by - KenW on 08/03/2006 14:17:38
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 08/05/2006 :  06:21:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 08/05/2006 :  11:29:42  Show Profile  Reply with Quote
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 - 07/03/2015 :  01:47:53  Show Profile  Reply with Quote
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
   Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 4.98 seconds. Powered By: Snitz Forums 2000