We’re migrating a subsidiary company from Quickbooks to Epicor 10.2.300 this year, and I’m starting with a fresh empty database for testing. My plan was to use our existing company as a starting point.
Is there a quick way to export tables via BAQ in a DMT’able format in order to import them to a separate company? Or will I need to create hundred BAQ’s from scratch (select the required columns, re-name the friendly column display name back to the db field name, etc…).
Sorry to say, but there isn’t an easy button on many tables.
Some can be done without BAQs and DMT by just opening the table (eg. ShipVia) in the list view & Copy All and then go to the other company and Past insert into the list view.
When I build out a company I follow checklist that I have developed over the years to populate that company. Set the two companies side by side and start copying and pasting.
Unless you had many companies (over 10) that you needed to build out, this approach will take less time.
I usually just create BAQs with the columns/headers named formatted for BAQ, dump from one system, and bring them right back in through DMT in the other. So the answer to you question is mostly yes, minimal work required. Now if there isn’t a DMT template for it that’s a hand move, or a very crafty UBAQ. I should add I get that it’s a lot of BAQs but it’s better than straight up Excel
I suppose if you’re savvy enough you could just dump the tables from SQL directly and rename fields vs the time to create BAQs but i’m not sure it’s that much shorter time wise.
We are deploying a few new companies in Epicor this year, part of a distribution network where the companies will share a lot of commonality and are part of a parent child structure.
I have created DMT files to import almost everything and use DMT playlists to load it all. While I am still refining this process, I have found I need two playlists as I have to do some GL Control Code updates manually.
If you needed to create a one new company, this would not be a good solution.
I have about 120 BAQ’s setup for the DMT process, which covers most of the setup. I also have a batch file system for importing the files using the DMT tool. I have used this process for the last 9 months to bring in 7 new companies into our multi-company environment.
One tool I used a lot during the building of the BAQ scripts, was called TinyTask. Basically it is a mouse/keystroke macro recorder, I used it to automate the renaming of the columns to make them match what DMT is expecting. You could probably do the same thing by using the flag during the BAQ export to output column headers instead of the BAQ headers. But since some of the combined column headers are more complex, I was not sure if that would work or not.
Holy cow, Paul. You rule! I never noticed that. Thank you (and for sharing on GIT too). Time to dig in some more.
I tried with and without “Output Labels” - Checking it adds the BAQ Labels as the first row. If it’s unchecked, the first row in the csv file is the data. Is there a different place to select column headers instead of the BAQ headers?
Thanks again - this will be a huge time saver (and thank you for your Insights presentation too). You mentioned it there, but the whole week was drinking from a fire hose!
The easiest way I found was using the TinyTask to automate setting the field to the same as the data table field name. Once you do it once, it is pretty well done. I put up a solution file with my BAQ’s in the github so you can see the results. My naming mechanism is in flux right now, as I decided to take the sequencing out of the names and name the BAQ’s according to functional areas. I also just added some file splitting code into the github.
Paul - Thanks! I downloaded TinyTask. Seems like a great lightweight macro recorder. But how do you use it to rename the BAQ columns? Do you have your screens always in the same location, and you use it to highlight text, Ctrl+C, then Ctrl+V on the other column? Or somehow grab it from the General tab’s SQL code?
Thank you very much for sharing the CAB solution with all the BAQ’s. That is a huge time saver! I just installed the solution and did my first BAQ Export and DMT Import… Time to check out the bat files now.
It will also record keystrokes, so I use tab. I start in the first good column, tab 4-5 times to move to the next row down, ctrl+a to select the entire column I am in, Ctrl+C to copy it, tab, then ctrl+p to paste it. I then hit home to go to the beginning of the label, then delete the right amount of characters to get to the main field name. Once you do it 2-5 times, it becomes very fast. Then I have it play back at 2x speed.
Cool. I haven’t tried out the command line yet. First pass I’m going one file at a time to figure out errors & address them. 2nd pass will be bulk. I’m learning a lot - Thanks for your help!
These are great they are really helping me get data from one company to another. I’m going to refresh and have no data in Prod. Can you create a company thru DMT without going into Admin console to add it.?
You can, I experimented with that at one time. There is a ‘Company Creation’ template you can use. Since it only works once, I did not have it as part of the refresh templates. I tend to prefer to do the company creation in the Admin Console/
You could also use another utility to clone a company like the Smart Data Migration tool that lets you first select the entity that you want to work with (i.e. master files). Then you can select (by using some filters if needed) the data to export to an excel file and finally import them again under a new company.
A demonstration on how to clone a Complete Job information is shown bellow.
I have used the BAQ Export process (out of V8.03 and E9), but used the XML output.
This allowed getting comments and other fields to come out clean (even if they have commas)
Setup process sets in V8 and E9 to save these to run later (and edit by double clicking on the export row.
Pulling these into Excel using PowerBI - i was able to do some secondary file changes either changing “SUB” for part.Subcontract to TRUE or FALSE for example.
Adding addition columns like company or plant also is easier.
Makes it very repeatable when you update the XML as required.
Thank you for your generosity in sharing Paul. I have not used DMT script mode before and I am trying to do a new company load. I downloaded what you have on github and I am wondering if in there is what would pull the data out of Epicor as well? Or is it just the templates to load the data?
Big thanks to @Paul_Millsaps! We successfully migrated our second company from Quickbooks to Epicor last month, and these BAQ / DMT files helped a lot.
Hi @dkavvouras do you know where I can get that tool?
I need to create 5+ companies in Epicor with the same basic information, so that will help us a lot to just “copy/paste” them.
