+ Start a Discussion
MetalHeadMetalHead 

Price List Upload

Hello,
I am a new user, and I am not a computer programmer.
I am trying to import a 3-column price list into SF Pro.  1) Part #, 2) Part Description, 3) Price.
 
I thought this would be simple  So far I have been given a 6-page .pdf that makes no sense for what I am trying to do.  It says I have to use the Excel Connector to import products into the Products 2  table, ensure that each product is linked to a Pricebook ID, and then import into a price book entry table.
 
Sounds good, except I have no idea what any of that means.  I was able to get the Excel add-in for Salesforce connector to work, that seems to be important in the forums I have read.
 
Is there any manual, or procedure available for importing a simple 3-column price list, that a lowly Marketing Manager guy could understand?
 
Thanks for any assistance.
 
Best Answer chosen by Admin (Salesforce Developers) 
fifedogfifedog
And others of course...

However in my previous post, Price List upload - explained I did list in the very, very last section a link to my google doc as an examples. However I did what to point it out here just to call it out.  I explain in the doc about certian fields and forumulas I'm using so I hope this helps.

link to the example.
http://spreadsheets.google.com/pub?key=p67bXC7rKoqbr7GrP0yzdbg

All Answers

MetalHeadMetalHead

In case any one else had the same question.

I was told today that importing products can only be done manually, or through the data loader with Enterprise Edition.

 

 

TreblaTrebla
Hey, i am facing the same predicament as you.
I am new to salesforce, currently setting up the system for my company.
Now stuck with this massive work of keying in the products + price.

I have the data in excel.
But i dont understand this Excel Connector guide as well.
What is the easiest way to do this?

Sleepless.
MetalHeadMetalHead
Hi Trebla,
 
STOP TRYING!!
 
What you are trying to do is not possible.  It is exactly what I was trying to do, until I gave up, and decided to cancel with Salesforce.com since the implementation is near impossible.
 
I took one last chance at Tech Support, and spoke to Heaven, she is fantastic.
 
It was explained to me that the only way to get the information you require into SF is through the APEX DATA LOADER. 

Once I got the ADL, I was equally frustrated, until I talked to a Support guy named David Rozier, who has his own written procedure for using the Data Loader. 
 
If you stick with it long enough with your sales rep and support reps, and finally figure it out, the program is very good.  I now have all of my price lists in SF, and it works extremely well. 
 
Talk to Support and Sales to figure out an option to use the Data Loader with Pro version or Enterprise version, but give up on the Excel Connector unless you are a computer programmer, or you have a lot more time on your hands than the average sales or marketing person.
TreblaTrebla
Hi MetalHead

Thank you so much for the tip.
That excel connector is a waste of time.

I will download the ADL today, and perhaps try to locate this support guy David R. you mentioned.

In my excel spreadsheet, I just have like 5 columns (Product Family, Product Name, Product Code, Product Description, Product Price) and about 500 line of items. This is probably an easy task for the ADL to import the data into SF right?


MetalHeadMetalHead
Hey,
What you are trying to do is easy with the procedure.  IF you are like me, you may be wondering with this simple task is such a secret, and why there are no instructions anywhere for this, aside of course from the procedure I got from David Rozier in Support.  You would think 99.9% of customers need to do this.
 
In your file, create a new column called IsActive.  Type TRUE and copy this down to each row.
Create a new column called IsStandardPrice.  Type FALSE and copy this down to each row.  Don't ask why, just do it and save the 3 hours to figure this out.  Save the file as .csv.
 
Open ADL and sign in and select Import.  In the Object menu select Product2 and open the file.  You have to map the columns in your Excel sheet, to the fields in Salesforce, just drag and drop the matching columns.
 
When you save the file, it will create an ERROR file, and a SUCCESS FILE on the desktop.  In the Success file, there will be all your original columns plus one more called ProductID.
 
In ADL choose Export.  Go to PriceBook2.   This will save your price book IDs in a file on the desktop.
 
In your SUCCESS FILE, create a new column beside ProductID, called PriceBook ID.  Copy the Standard Price Book ID, and copy all the way down the rows.
 
In ADL choose import, and select PriceBookEntry.  Again, map out all of the columns to match.
 
All of the product are now in SF. 
 
In Excel, in the Price Book ID, Copy the Price Book ID from the PriceBook2 export, that matches to each row.  You need to create PriceBooks in SF before doing the export above.  You have to do the first PriceBookEntry to the Standard Price book, before asigning products to individual price books.
 
I hope this makes sense, it still doesn't make a lot of sense to me, but it works.  Aren't you glad you gave up on the Connector??
 
Gooooooood Luck!
 
TreblaTrebla
I am really glad i gave up on the connector early on and found you instead.

Thanks for taking the time to guide me.
It does not make much sense, but i will follow your instruction step by step.

Wait for my good news.
TreblaTrebla
Hi MetalHead

I managed to import the product list in, but not the standard price. Basically i just want to key in 0 on the Standard Price List for now.

I got stuck following your instruction starting here:

In ADL choose Export.  Go to PriceBook2.   This will save your price book IDs in a file on the desktop.
 
In your SUCCESS FILE, create a new column beside ProductID, called PriceBook ID.  Copy the Standard Price Book ID, and copy all the way down the rows.
 
In ADL choose import, and select PriceBookEntry.  Again, map out all of the columns to match.
 
All of the product are now in SF. 
 
In Excel, in the Price Book ID, Copy the Price Book ID from the PriceBook2 export, that matches to each row.  You need to create PriceBooks in SF before doing the export above.  You have to do the first PriceBookEntry to the Standard Price book, before asigning products to individual price books.

When exporting "PriceBook2", i  got stuck on step 3: Edit your query.
What am i suppose to do here?

What do you mean by "
In your SUCCESS FILE, create a new column beside ProductID, called PriceBook ID.  Copy the Standard Price Book ID, and copy all the way down the rows."

In the ADL choose import, i also cannot find "PriceBookEntry".

I really hope you can help me. I am like so close yet so far.

Thanks a lot.
MetalHeadMetalHead

Hey,

1)  For products, you have to have the column for prices, and map it to price.  You ALSO have to have a column called UseStandardPrice, that says FALSE in all the rows, and map this to UseStandardPrice in ADL.  If you want the price to be $0.00, use $0.00 as the price, but still creat the column called UseStandardPrice and make it false.

2) For Price Book Entry:   in the main menu of Import inADL there is an unchecked button that says "Show All" or something, you have to check this first, and then scroll down, it will be there.

For Step 3, just check all the boxes in the query fields.  It will generate a file called extract.csv on your desktop.  In this file will be the PRICEBOOK ID's that you need to make the file for PRICE BOOK ENTRY.

When you import the file for Price Book Entry, you need to have the Product ID, the Price Book ID, the Product Name, the Product Description, IsActive, and IsStandardPrice as columns in your Excel Sheet. 

The Price Book Id's will all be the same, since you have to import all the products into the Standard Price Book.  You can map products to individual Price Books later.

The Product Id's will all be different, and you get these from the succes file from importing Products2.

When you did the Products2 import, you should have got a file on your desktop called Success009......

If you didn't get this file, search for it on your computer, or try Heaven or David in support, since I have no clue.  I only know a very limited scope of this, and I can't believe that it is so complicated to do such a simple task as importing a price list.

Good luck.

Harry JamesHarry James

I have read through this thread and am scratching my head.  Everything that you are doing with the Data Loader can be done with the Excel Connector.  And much faster if you don't have thousands of products and numerous price books.  There are quite a few threads here about using the Connector to import and update Products and Price Books.  You can do a search with somethng like "excel connector products."

I can only surmise that the reason that Support told you that it cannot be done with the Connector is because the Connector is open source and not officially supported by Salesforce.

MetalHeadMetalHead

Hi Harry,

This thread was started be me by asking if anyone knew how to import a SIMPLE 3-Column Excel sheet into SF.com using the Excel Connector.  1) Part #, 2) Part Description, 3) Price

There were no replies.

The same question was asked of the sales team, where I was told that they could look into the situation for a fee of $245/hour.  I wasn't up for that, and found it a a disgraceful request.

When support was called, I was told that the Connector is unsupported as you mention.  My reply was that I didn't need support on the Connector, but that I needed support on how to Import a simple 3-column spread sheet.  There was no assistance.

Finally, I spoke to another support person, who told me that what I was trying to do, and what the first guy wanted to charge me for, was not possible in SF.com.

I finally got great support from David Rozier and Heaven in support, along with my Sales Rep, who helped me get set up.

This thread has been continuing because Trebla has had the same difficulty as I had.  Your comments are about as helpful as the orignal support group.

If you can find a procedure for importing a simple 3-column Excel sheet into SF.com with the Excel Connector, please post it here, as this is the original question on this thread.

There are still NO REPLIES, and there are now 2 people who have searched.  Thanks in advance for posting these instructions.

 

TreblaTrebla
Hi MetalHead,

Thank you so much again for your patience.
This is certainly more helpful than Harry's comments.

I am truly grateful.
MetalHeadMetalHead

Hi Trebla,

Harry is probably a developer or computer programmer.   A lot of these people are very condescending towards customers and people with limited computer skills. 

For some reason they assume people like you and I are not intelligent enough to use a search function on a website.  They forget that the reason people choose programs like SF.com is for the exact reason to avoid IT people.  I think they also forget that it is an expensive program, and that people like me are succesful enough in other areas to be able to afford the program in the first place.

After toiling over the problems I have described at length in this thread, the reply is that he is scratching his head at why we didn't just search for Connector in the communtiy website??

THANKS SOOOO much, should have thought of that.  Apparently he missed the part about the company's own support group stating that it is not even possible.  Also, my ORIGINAL question about inserting a simple 3-column spreadsheet is now 3 weeks old, and is still unanswered by anyone on here or in the company in sales or support by email and telephone.  As I said before I found 2 people in support who were good people, otherwise I would have been the shortest subscription on record with this company.

Hope you got your products in so you can get back to business.

fifedogfifedog
MetalHead and Trebla,
I would like to see if I can help not by giving you "how" to do it, for it appears your on track and I don't want to derail you. However I want to try and help with the Why, not that you may care at this point but I've found sometimes knowing the whys helps with the hows. What your doing is what I think the HARDEST thing about Salesforce, less the APIs, in the 5 years I've been an admin using Salesforce.

I chuckle at the thread and the issues you're having for I've gone through them myself and that was right after they introduced these product2 and pricebook2 tables, what was that all about? I think it was just for these reasons I hope to explain because Salesforce got it wrong the first time around, however it introduces your comments MetalHead,
"I can't believe that it is so complicated to do such a simple task as importing a price list."

There are three fundamental objects/tables you need to understand: product2, pricebook2, and pricebookentry. A key point to remember and understand is we need the ability to have multiple prices for any given product. From what I can assume you don't have have this scenario but let me try and explain why.

Let's say we have products Zit and Pipi. We sell into different industries: Education, Government, Service Providers and Enterprise. For the Education we sell Zit for $120, and Government and Enterprise for $160, Service Providers $200. However Pipi is only sold to Education for $100.  So the question I have for you is: How do we manage this? MetalHead as you stated you have price in your excel which you want to add to your price to your products, however we can't store the price on the product because there can be multiple prices per product. Also what about specifying that Pipi is only for the Education?  I think if you understand this one concept you'll start to understand why it's done this way and what needs to happen for you to in upload products and prices.

The Product2 table just stores the product information like, product code, name, description, product family and other custom fields. We use the Price Book table to create those different price books one for each of our industry as above. All it contains is the names of the price books, Edu, Gov, SP, and Ent.  So we can have a product, Zit, sold in multiple industries. As well as an industry can have multiple products being sold to it. This is known as a many-to-many which we need a third table which in our case is the pricebookentry table. Below I hope this diagram helps.

Products Table

Price Book
Zits

Edu
Pipi

Gov


SP

PricebookEntry
Ent

ZitsEdu - 120


ZitsGov - 160


ZitsSP - 160

ZitsEnt - 200

PipiEdu - 100

As you can see the pricebookentry record has all three key elements, the product, price book and price. This allows one product to have multiple prices, and allows a selective product(s) to only show up in one pricebook.  Therefore when you create your price list you need to first create all products. If your going to have multiple price list then you need to create them second. If not then just use the Stand Price list. So that's the fundamental piece however now let's more to the nuances of Salesforce.

In Salesforce there is one price book which is called Standard. You can think of this as the master price book which should have every product you plan to sell. This price book comes into play with that pesky "IsStandardPrice." Let's say the Zits price for Gov and SP is the base price from which we uplift or discount all other industry prices. In doing so we want to then set the price of the Zits product in the Standard Price list as 160.

Products Table

Price Book
Zits

Edu
Pipi

Gov


SP

PricebookEntry
Ent

ZitsEdu - 120
Stan (standard)

ZitsGov - 160


ZitsSP - 160

ZitsEnt - 200

PipiEdu - 100

ZitsStan - 160


PipiStan - 120





Here I've added two other line items ZitsStan and PipiStan to show the base price for our two products. Now let's say the base price for Zits has changed. What would we have to do to reflect that change for the Gov and SP price books. We would have to make two changes. Wouldn't it be nice if we could just change one price and have it reflect across ALL price books?  And that is why there is a flag called Is Standard Price.  When you change the "Standard Price Book's" price for a given product that price will change in the other price books.

So when you're inserting records for the Standard Price book just be sure to mark your product as active, isActive = True, and isStandardPrice = False. You need to set it to false because it is the standard you're not pulling the standard. When your inserting to a different price book and want to use the Standard price then you need to set your "UseStandardPrice" as True and the price (I can't remember which but make price either the same as the standard price or nothing, I think it's the same)

So that's what I can offer and unsure this helps however it's here for posterity. As for as my opinion I like the excel connector because it removes that one step about the success records file. Once you insert your records the file you're working with has the ID right there.

Here is my example of the excel connector template I use any time I deal with products and price books.  To me it's just easier since I'm already there in Excel.  One key point to note is you have to say 'new' in the ID column to insert your records. (I've changed all prices to reflect dummy prices)

Message Edited by fifedog on 09-05-2007 10:09 PM

fifedogfifedog
Metal and Trebla,
Just a brief note about your experience with Harry and Support, that sucks and a bummer. However not all unexpected. As far as Harry goes you're right he sounds a bit more technically and some times in our rush we forget the audience, however I knew this would be the case when I started the board 5 years ago.

I'm not a SFDC employee nor have I ever, nor do I think I would. I only hope that you've found the boards a valuable resource in getting your questions answer.  I was a user just like you wanting more help since SFDC support didn't have all the answers for me to become successful. There are answers to their program then there are real world answers which we can only glean from one another and for posterity sake these conversations can help the next wave of users.
Harry JamesHarry James

MetalHead and Trebla,

While this forum is valuable, you have to remember that only a small number of users visit it.  And most of them, myself included, probably only take a look when time permits and may not have time to read and/or respond to all the entries.

I kept my response short because you had found a solution and I simply did not have sufficient time to go into the details as fifedog did.  (Thanks, fifedog.)  My "head scratching" was about why SFDC support had told you that it "could not" be done with the EXCEL Connector.  And my intent was to encourage you to not give up on that tool.  While I do some VBA and can bungle through a smidgen of HTML, I am hardly a programmer.  Heck, I take a look at the code behind the Connector and can barely figure out what is going on.  But I don't have to understand it all to use it. 

You do have to understand that the data in Salesforce is arranged in tables and how various tables are related to each other.  But the Connector is helpful just for that purpose.  Sometimes I simply fire it up to see what fields are in each table and the relationships to other tables.

Since the Connector is an SFDC "stepchild," information about using is scattered around and you will need to search it out.  But, once you become familiar with it, it is a valuable timesaver.   

 

fifedogfifedog
And others of course...

However in my previous post, Price List upload - explained I did list in the very, very last section a link to my google doc as an examples. However I did what to point it out here just to call it out.  I explain in the doc about certian fields and forumulas I'm using so I hope this helps.

link to the example.
http://spreadsheets.google.com/pub?key=p67bXC7rKoqbr7GrP0yzdbg
This was selected as the best answer
KringoKringo
MetalHead thanks for the instructions. It took me more hours than I want to admit but I finally got it going and figured out what was going wrong. I've been our company's sole SFDC admin for 1 1/2 years and I was starting to get a little frustrated.

I will try to consolodate what I learned here to give back to this forum as it has helped me many times.

Fifedog- thank you as well. Yesterday I was all over the place and couldn't get this to work even with MetalHeads excellent notes.I understand enough of the API structure that it really came together when I reread the post today. I 've spent so much time pouring over the WIL thread and many others over this past year that I feel like I know you.

This is an indispensable resource and I really appreciate all the input. (especially after suceeding with this after about 8 unsuccesful hours)

-Kringo


jisaacjisaac
Pete,

Thanks SO much for sharing this - it is very helpful. I am interested in your opinion - we sell multi-year licenses and asp software services to financial institutions and have tiered pricing based on the size of the institution. There are probably around 1,000 different products an FI could buy from us.

Each "product" has potentially at least 4 separate components to price (unique products in SFDC) - such as license, annual maintenance, implementations, training. They are all different types of revenue and thus recognized differently by our accounting folks. So for every product we sell, I need (in this example) 4 unique entries in the pricebook. And that's just the licensed version. If they want they ASP version, I have monthly fees, implementations fees, training fees at least. So now we are up to at least 7 entries into a pricebook per product from us.

Since those 7 items have different prices for the different tier ( we have 7 tiers) sizes (and we rarely sell anything at list price - its typically less than list price), I have only entered the items once with a standard price of $0 in order to avoid the pain of updated and maintaining. The reps know that they have to enter the products into their opps and then enter the selling price, based on the size of their prospect.

You can imagine how long the list of products is for some opportunities if they buy (and they usually do) multiple products from us.

I'd be interested in your thoughts on our practice and if you would set it up differently.

Thanks,
Jane