The Dreaded import
When doing a new AX implementation, one of the tasks that must be done, and can be a real rough one is getting the customers data from their current system into AX.
The AX doc's and a lot of consultants would have people try and use the Excel import utility. That's great for small chunks of data, of 500 records of less, but most of the time the utility is truly bad, and very slow.
Another piece to the data import is that it will need to happen several times until the data looks right and the client signs off on it. I have found that the best thing to do is develop a project that houses classes that can bring in the data through ODBC. The way I do this is give the client the Excel spreadsheet, say for Vendors, so for the VendTable. I tell them to fill this spreadsheet, and get it back to me. From there I take that and create a DTS that can be re-ran over and over again that takes and creates a VendTable in SQL, that lives in a AXBaseData DB.
From there I have developed (continue to develop) a class system for connecting to the AXBaseData db through the ODBCConnection object, and populate the Vendor table through that way.
To give you a better idea of how much better this way is take Zips. if you were to load U.S. only zips (which is more than 65k records) into AX through the excel process it could take you up to 5 whole hours to load that data. Same data through my method of ODBCConnection object, and classes... 20 mins max!
Take that and times it by the amount of times you will have to re-run this and you can see how much time and how much more productivity can be obtained through the method I am talking about. In the my next few posts I will cover this process a little bit more and offer up an example class. Until next time!
Find a job at: www.DynamicsAXJobs.com
The AX doc's and a lot of consultants would have people try and use the Excel import utility. That's great for small chunks of data, of 500 records of less, but most of the time the utility is truly bad, and very slow.
Another piece to the data import is that it will need to happen several times until the data looks right and the client signs off on it. I have found that the best thing to do is develop a project that houses classes that can bring in the data through ODBC. The way I do this is give the client the Excel spreadsheet, say for Vendors, so for the VendTable. I tell them to fill this spreadsheet, and get it back to me. From there I take that and create a DTS that can be re-ran over and over again that takes and creates a VendTable in SQL, that lives in a AXBaseData DB.
From there I have developed (continue to develop) a class system for connecting to the AXBaseData db through the ODBCConnection object, and populate the Vendor table through that way.
To give you a better idea of how much better this way is take Zips. if you were to load U.S. only zips (which is more than 65k records) into AX through the excel process it could take you up to 5 whole hours to load that data. Same data through my method of ODBCConnection object, and classes... 20 mins max!
Take that and times it by the amount of times you will have to re-run this and you can see how much time and how much more productivity can be obtained through the method I am talking about. In the my next few posts I will cover this process a little bit more and offer up an example class. Until next time!
Find a job at: www.DynamicsAXJobs.com
posted by brandon | 8:00 PM
2 Comments:
For a job I did for BDCSoftware I bypassed the Excel import in a different way, without connecting to the database directly (a client requirement). See http://www.axaptapedia.com/Excel_Import_Workaround.
I comment on an old post because this post came up on google when I was hoping someone already had a solution.
I was interest to know if you ever posted up some examples?
Sorry to post to such an old thread but it sounds like you've found a good solution to the problems I am currently dealing with
Post a Comment
<< Home