We are trying to use the pricing service to update pricing on part numbers. The vendor sends us a pricing file with all of their part numbers, which we only have some of these in our system that we sell. The vendors files does not meet the required formatting to upload into epicor, and it does not like that missing part numbers. Anyone found a way around this? Pricing updates happen multiple times a year for each vendor, and to edit these files would take days manually.
Example of formatting issues - vendor has a lot of blank fields, text in prophet 21 required numeric fields, and the upc code is longer from the vendor then prophet 21 allows by 1 digit.
The days of editing are the part I’d fix first. Do the cleanup once in Excel Power Query and reuse it for every update from that vendor.
- Export the items you carry from that supplier out of P21 (a quick report or query), with the supplier part number.
- In Power Query, load the vendor file and your export, then Merge them on supplier part number with an Inner join. That keeps only the parts you sell, so the missing part numbers never reach P21.
- Change the numeric columns’ type to number. Anything that won’t convert shows as an Error, so you can see the text rows and replace or remove them. Fill blanks with what P21 expects, or drop the columns you aren’t updating.
- Keep UPC as text, or Excel will drop leading zeros. If the vendor’s UPCs are 13 digits starting with 0, they’re 12-digit UPCs padded to EAN length, and removing the leading 0 gives you the UPC. If they don’t start with 0, they’re real EANs and won’t fit. For price-only updates it may be easier to leave UPC out.
- Output the columns in the layout Pricing Service wants and save as CSV.
Next time, save the new vendor file over the old one in the same folder and hit Refresh. It’s also worth asking each vendor for a file of just your part numbers. Some will do it.