QuoteWerks Support Portal

 We have had a price increase of 10% that applies to certain customers, but not all.  When I export the product database to a text file and open in excel the prices in the PricingLevelX fields all have a | instead of a decimal so for example a price of 5.55 shows up as 5|55.

I use the export of the product database to take the old prices and increase some (but not all) by 10% using excel and then I put in a csv file and use the Import Wizard to update the existing products with this new price for the PriceLevel.

I have used several excel formulas to extract everything to the right and left of the | and then put back together with a . in the middle instead.

Does anyone know of a better way to work around the | characters if you are not increasing every price in the database by a percentage?

Use Excel to do a find and replace?

Find: "|" .. Replace: "."

Thanks Matt for the suggestion.  I forgot in my post to mention that I had already tried the find and replace. Excel says it does not find anything.

Does anyone at Quotewerks have a good reason why the prices are stored in this format (5|55 instead of 5.55).  I noticed that the List price does not get stored with this format and is kept as a decimal.  This problem is only on the PricingLevel fields.

It might be a while until you get a direct response from QuoteWerks.

Having you submitted the query to [email protected]?

I note that the field contains more than one bit of information, this is probably the cause of needing a | instead of a .

Matt is correct: that pipe-looking / square / box character is a kind-of NULL character in the database used as a separator which Excel doesn't really understand, hence it's displayed as a box. Not sure why QuoteWerks stores it like that, but that's what it is. What you should be able to do is copy that box character to the clipboard then paste it into the Find/Replace window to then replace it.

Thanks for the idea Hilltops...but pasting into the Excel find box does not seem to work.  I tested with notepad and it pastes just fine into notepad...but no luck in Excel.  As mentioned above I was able to workaround this by using several excel formulas to extract everything to the right and left of the | and then putting back together with a . in the middle instead.  This is a more tedious process than a simple find and replace but it works to at least get the job done.

I have sent an e-mail to [email protected] to see what they have to say about why the prices are kept in this format and will put a note on this topic as to their reply as soon as I hear back from them in case it helps other Quotewerks users in the future.

Unfortunately, no solution to replacing | with . from Quotewerks or reason Quotewerks choose to combine several fields into 1...

Here is reply from Quotewerks Tech Support:

So you will want to separate the data in this column and place each bit of data into individual columns, as they are macro fields and this data is combined when exported, Price Level Lower Qty, Upper Qty and Price.

I would just include the Items you want to update the Price of and when re importing the price list choose the update option. Make sure you match the items on MPN# or equivalent. 

You might try Notepad++.  It has a very good search and replace functionality.  I used it to clean up a big CSV file I had to get imported into Excel.  My main problem was spurious returns that messed up the CSV report export.

Thanks Alan!

I tried Notepad++ out and it worked!

I have to change the prices once a year and this will be a real timesaver!

Notepadd++ has an awesome find and replace function.  It really made it easier for me to clean up my CSV export from QuoteWerks so it imported into Execl cleanly.