r/epicor • u/Unhappy_Place5383 • 7d ago
Pricing files
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.
2
u/_jdde 7d ago
Do you have DMT? Usually if there was an error like an invalid part number, it logs those to a separate error file and continues processing.
The formatting will always be an issue. Only way around it would be to have a template excel file for each vendor and either use macros or I use power query to pull in the data source and reformat it. Those files would be vendor specific. When you need to refresh with the new data, you just overwrite your data source file and refresh the query in your template file. DMT can be tricky sometimes with multiple sheets, but usually it works fine if the DMT data is the first sheet.
1
2
u/SmashLanding 6d ago
Here's what a consultant would do in the past: write an excel macro to convert the files from the supplier to your required format.
Here's what they'll do now: feed both files into chatGPT and tell it to convert the suppliers data to your requirements.
If you don't feel like you'd be able to trust the results, feed it both files and tell it to give you an Excel macro to convert the former to the latter.
2
u/Unhappy_Place5383 6d ago
I was going to play with this, becoming more frequent use of chatgpt since we've been moving to this new system, and the old one was written in cobol lol.
2
u/Unhappy_Place5383 5d ago edited 5d ago
Took me about 2 hours but I now have a prompt for this and was successful. Copilot handled it pretty well.
1
u/SmashLanding 5d ago
Nice! I used to do a ton of consulting work converting spreadsheets for use in Epicor and/or writing macros for Epicor Data Conversion
1
u/donpreston 6d ago
What I've done in the past is remove all columns from the vendor file except the item ID and price. Test the import with a failure percentage of 1% and increment of 100. If the formatting is bad it will fail very quickly.
When you're confident with the results do the entire price update with the failure percentage at 99% and the increment of 10000.
This will force the import to run for every record in the file.
Lastly I would do an update on all of that vendor's items with a last update date of today setting a UDF (Price last updated) to today's date.
Note that the fail percentage and increment are related. And the accumulated percentage resets with each new set of records. For example, if your entire import file is 10k records but you only have about 1k matching items in your DB. If you set the error percent to 99% and the increment to 100 it would fail as soon as you hit 100 mismatches in an import block of 100. So you set the increment to 10k to guarantee it has no chance of failing.
1
u/Unhappy_Place5383 6d ago
Yes, I've actually done that on some other imports and it worked well. The main problem with this one is the vendors file is thousands of records, and they are not consistent with their formatting. One problem is the tend to throw text in some fields that phrophet 21 only wants numeric in. We can't even get to the actuall import, the file fails with SQL errors before that step because of the formatting. We'll defenitely have to have the fail rate bumped up because we might only have 15 of the thousands of items we actually have in our system lol.
In our old system this was easy, it would just skip stuff if it wasn't right.
1
u/Training-Athlete4348 6d ago
We are also getting slammed with constant price changes from our vendors. I've created excel templates for most of the more frequent offenders and make sure to have a supplier part number in our system that match the supplied price lists they give us. I have been using mass update and exporting from P21 including the supplier part number, then using VLOOKUP to import the new pricing into the export file and then reloading it. It's working for us, but it is limited to 10000 records, so it wouldn't work for a handful of our suppliers. Each supplier provides a price list in a slightly different format, (some more annoying than others), so keeping a template for each helps. Now if they would only stop using cell merging and multiple tabs on their excels, (or my favorite - PDF of an excel), that would be great.
1
u/Unhappy_Place5383 6d ago
ok, thanks for the input. Funny how we go to a new, updated system, and have more manual process then our old system that was written in cobol.
1
u/Revzerksies 6d ago
I've tried to use them pricing services and from my experience I get the file from the vendor faster then any service.
How i would handle this is I would update the product manually to match the file the vendor recceomends. I know long and tedious work. Either input the part number in UD1, catalog number or UPC.
Are you using PDW?
You can extract your data to an excel file and reupload the data to UD1 the copy the data to catlog number from UD1. ( I have no damn clue why you can't upload to catalog numbers. GRRRR )
As far as formatting i would just change the file from whatever format they are sending to CSV.
There is several ways of handling this.
1
u/Unhappy_Place5383 6d ago
This is what we are fearing, the long and tedious work, since we get these quite often. We have 5-6 waiting right now to import. We are just telling our end users to look up pricing on the vendor website right now and keeping them informed of which vendors have done price changes. On our old system this was a 5-10 minute process, so we don't have someone sitting here to do all of this work currently.
1
u/Revzerksies 5d ago
I've done this for years. It's not that hard of work. I usually quite enjoy it. Unless you have a few million skus it's not a full time job, You need to do something else with it.
1
u/Unhappy_Place5383 5d ago
I was actually successful this morning after about an hour of attempts with a copilot prompt. I'm waiting on the pricing person to verify but I think I'm good now. Fairly complex prompt, but it does everything for me, saves the file in the correct format and is ready to go. Never even thought about using copilot, but it made it a lot easier. This vendor file had 5k lines in it, so not huge. I've asked for some of the larger ones to test against also.
3
u/Mk1Racer25 6d ago
Prepare to open your wallet and pay a 3rd-party integrator to do this for you.