We are doing a new implementation of E10…so I need to export all the tables so that we can clean up the data.
Is there a script that I can run to export all the data in the tables?
For example:
For Each table not PartTran, ChgLog, GLJrnl, XXX, XXX
export table > date-table.xls
I am in progress E9
You have to pick and choose what you are going to clean up. If you are starting fresh you won’t have PartTran, ChangeLog, GLJrnl etc. It’s clean empty, you generally don’t just update and dump that kind of data back into a database. For the master tables, Customers, Parts, MOM, BOM, BOO, Open Orders, etc write BAQ’s that will query all the data and have it preformatted (column names etc) for DMT import so you don’t have to do that step separate. Right click Copy to Excel and away you go.
To answer your question is there a script you can run to export all the data, yes. Should you no.
We use ODBC when extracting data in preparation for moving our customers to a clean database. Excel and Microsoft Query allow you to pick the fields and save formulas. Then you can Refresh the next time you are ready to extract again, and there is less work to do the second time. If you are on Progress, you’ll need that Driver; if you are on SQL Server, you can use the standard Driver. BAQs are great too but take more time than a direct database connection.
Nothing as far as I know extracts ALL tables. You wouldn’t want to do that anyways. You should focus on the building blocks first (static tables) like Customer, ShipTo, CustCnt, Part, PartPlant, PartRev, PartOpr, PartMtl, and many others. You will need to import them using DMT, and in some cases, merge table data (for example, Resource Groups and Resources) or include additional fields like CustID when importing ShipTo or CustCnt.
I created process sets and used the BAQ exports to CSV.
Setup a seperate process set for Parts, Suppliers, Customers, etc.
Made it very easy to export the files quickly too - these will run faster than pulling up the query and copying it to excel.
After I have pushed the data out, Have run scripts to pull the data into E10.
Good luck.
Bruce Larson
Larson Solutions
Hey,
Our company is using 10.1. When I am in a hurry I like to use Excel and connect directly from the SQL server, then format to filter out any columns that are not required.
In E9 IT setup a system DNS (ODBC) connection to the server that allowed us to pull the entire table or exclude columns.
I hope this helps.
We migrated the data from old system to new system.
However if I can give you some suggestions watch the UOM and the upper lower case. Although the case may look the same EA vs ea the are not treated the same in E10.
As for the UOM it case a lot of issues on the Sales order release tab. The only way to fix was to add a new release and delete the old release. SO lines you could fix with DMT.
Sorry for the lengthy message.
Candy