Is there an easy way to import this type of file? I know I will need CustomerID and corrected dates…but, anyway to upload this type of grid as opposed to line by line?
Today is the DMT’s time to shine! This is another great use for the data management tool. You have to purchase the module from Epicor. It is well worth it if you do regular large imports of data.
Good luck!
Nate
We have DMT but no idea how to convert that image to a format DMT would use to upload.
Whoa there! converting images into data is a whole different can of worms. I am assuming that you have the data in n excel file, CSV, or some other data file. Otherwise you better get your data entry fingers ready.
If you have lots of data in image form and can’t afford to wait for data entry, your next best bet is to find an OCR tool that will help you out. Even then, you’re in for some work, as cleaning OCR text into a nice grid is not always easy.
You can use the DMT to import all that data into a UD table. Then you can use the UD table and link it to other epicor tables to produce usable reports.
I dont mean to convert the image…but rather an excel file that is formatted like that…
OK Good. Then you need to pick a table in DMT to pull the data into. Make sure to use one that you aren’t already using. You can check your UD table contents with a simple BAQ. Lets assume you are going to use UD01. In DMT, you have to tell it where (which field) to save all the data from your excel file.
The most important fields are the Key fields. Based on your image I would make Company = Company name (same as in epicor), Key1 = PartNumber. Ignore the other keys and put your forecast value into UD01.Number01, and your forecast date into UD01.Date01.
In the end your excel file might looks something like this:
| Company | Key1 | Number01 | Date01 |
|---|---|---|---|
| AVNAN | 10000 | 356 | 2/1/2023 |
| AVNAN | 10000 | 1002 | 3/1/2023 |
| AVNAN | 10000 | 1002 | 4/1/2023 |
| AVNAN | 10000 | 1000 | 5/1/2023 |
This doesn’t do anything with the data, it just adds it to a UD table in Epicor for you to do something with.
Cory, are you wanting to simply ‘store’ this data in Epicor, or actually have it affect the system somehow?
If it’s the former, follow Nate’s suggestion above to store in a UD table. If the latter, it’s going to be a lot more complicated.
1 Like
AFAIK, DMT requires line by line so you’ll need to have the data transposed.
We have several customers that provide us data in formats that we have to manually change to one line one forecast record.
We’ve worked with some of them to modify the formatting a little but usually the customer can only change their reporting so much. The heavy lifting comes from our team manipulating the data formatting ourselves so DMT can process it. It’s a labor intensive and time consuming task.
Oh I know that’s true. Very labor intensive. That’s why I asked if Epicor could use the data as is.
@Clambert here are the built in formats and they all are line by line.
You could make a macro to split that format into line by line for DMT or you could DMT that into a UD into NumberX-XX and then have a bpm that does the update on the UD update. Neither of these is horrible,
If you want to get tricky then you could have the macro just do a REST update in forecast entry.
I’m often given the forecast in a similar format, I DMT it into Epicor
My steps would be to verify the data checking for incorrect part numbers/inactive (salesman 
)
Change the format of the Month across the top to date and format it as a table in excel
Use Data - From Table to get it into power query
Select the month columns
Transform Unpivot
You now have a flat file for import
Ours is pretty simplistic as we don’t forecast per customer but rather as a site.
I agree with Melinda. Al the transforms and Excel-FOO need to be done in excell. You can actually bypass DMT and add into forecast vie Import Forecast from Forecast Entry . Then you get the options Clear and reload, Add and replace or Add If new. you can select Sites and a start date. Just need that flat file to import from…
Yes…thanks. The issue is creating that flat file from that one “grid” forecast file.
Its been a while since I set one up but the juxt of it is a pivot table pointed to a range ( Tables work good for this) . you would return no rows and no columns. Double click on the Pivot table and a flat file will be generates in another sheet ( just like double clicking on any pivot table to get the underlying data). Then that flat file will be the base for your input into the forecast ( adding Company ,plant Customer ( 0 in our case, not Customer specific) and another column for consumed ( Epicor will figure that out) .
Googling Excel Pivot Table and Pivot Chart Wizard would explain much better than I could .
Dean