DMT Batch File

Kinetic ERP
,
21

Thank you for that! I have now created a new access scope containing only the BAQs I want. I added a new API key limited to that access scope, and I am trying to use the REST help to generate the URL I will paste into Excel. In the tech ref for REST V2 I found this talking about connecting to Excel:

Important To be able to get data from a Service/BAQ feed into Excel using REST API v.2, you must disable the default API Key requirement for REST requests by setting the value of the
EnforceApiKeyForRestApiV2 property in the appSettings section of the web.config file to false

I havenā€™t messed with any web.config files before. Is this an on prem thing? Can I access the web.config as a cloud user?

1 Like
22

Personally, I would not reduce my companyā€™s security profile just so I could use Excel. :person_shrugging:

It is only for on-prem users and the cloud team will not do that for you.

If youā€™re just after CSV, consider what @josecgomez did here:

REST to email - ERP 10 - Epicor User Help Forum (epiusers.help)

23

I thought REST was supposed to be the way to go!? Why bother having all this API crap if you consider it not secure enough to even use it?

24

The insecure part in this equation is the desktop app written in the 90s - not REST. Which by the way, Excel is the largest and most expensive JSON to CSV converter one can use. Why is it needed for this archiving project? :thinking:

If you wanted to do some scripting yourself, you could use v2. Itā€™s just that Excel out of the box doesnā€™t work with v2. Excel isnā€™t about security, itā€™s about convenience.

1 Like
25

:rofl:
I see. I will look into some scripting approaches. I didnā€™t get that Excel doesnā€™t work well with v2. I will probably end up just skipping this export attempt and trust that my snapshot of live into my pilot database is good enough in the event that my DMT commands go awry.
Thanks for all your help and suggestions! I really would like to learn more about REST and OData. It looks like I will need to know that stuff for working in Kinetic.

26

Hi Nate,
If this is something you plan on doing regularly, one other option to improve overall speed may be to output the files to a shared location on your cloud host server perhaps a well crafted folder under EpicorData, and if possible, have some server side compressor script, written, with security reach and other access considerations taken, that could be triggered by you or your host admin to compress and wrap them up. Then you could access the share once the zip archive is complete for a smaller transfer. very much a traditional approach, I know, but weā€™re an onprem installation, so we face different considerations, but we do have lots of sharing and transferring of data all over the place.
Just ideas, not sure what access and services you have with your host. One last thought, do you have access to Automation Studio? That might unlock additional options, but not sure what can do done in a hosted environment.

2 Likes
27

I donā€™t plan on doing this ever again, ideally. We are working on updating all of our part rev masters. This involves using DMT to delete all the part operations, and reimport them with corrected opcodes. I also will be doing this to open jobs. My DMT processes are now working perfectly without any errors. I just wanted to have an easy way to snapshot some critical tables in case something goes wrong and I need to reference a specific part or field. I thought that getting an export would be a lot easier. but clearly this is not a trivial task. I think my pilot database will be enough of a security blanket for me to move forward and try this out in Live!

I donā€™t think I have Automation Studio, but I havenā€™t really looked into it.

28

Donā€™t underestimate this idea! When we think of system reliability, itā€™s a good strategy to know what the most recent work was in case of an incident of some kind. It fills the gaps between snapshots and you can get some interesting insights into the current work if you wanted to.

1 Like
29

Why not use the REST v1 API instead of v2? I use that all the time to pull data directly from Epicor BAQs into Excel. No API keys required.

2 Likes
30

Heā€™s on the cloud and cloud thinks more about security than on-prem users.

2 Likes
31

I was able to get v1 working in Excel, but it is soooooo slow as to be virtually unusable. At least for this task.

32

Copying Joseā€™s code here. Itā€™s going to be way faster without all of that Excel overhead.

#Epicor user name
$user = "epicor"
#Epicor Password
$pass= "epicor"

#BAQ End point URL
$uri = "https://YourEpicorServer.TLD/EpicorProduction/api/v1/BaqSvc/zCustomer01/"

#Create encoded password
$secpasswd = ConvertTo-SecureString $pass -AsPlainText -Force

#Create Credential
$cred = New-Object System.Management.Automation.PSCredential ($user, $secpasswd)

#Rest Call
$data = Invoke-RestMethod $uri -Method Get -Credential $cred -ContentType "Applicatin/Json"

#Dump results as CSV
$data.value | Export-Csv -Path './test.csv' -NoTypeInformation

For v2, you would pass in the api-key.

5 Likes
33

Thanks Mark, nice and clean!

34

Most of Kinetic is using the V1 api. In fact, I donā€™t think Iā€™ve seen it use V2 at allā€¦

35

Yeah, the debugging shows that. Only Epicor Functions require V2 as far as I can tell.