I recently received an Excel spreadsheet from a client with historical invoice data that needed to be imported into Microsoft Dynamics GP. I converted the spreadsheet to a text tab delimited file and imported the data using a created SOP eConnect program.
When importing, I noticed there were some errors on some of the invoices and the data was out of alignment. I opened the text file and located the area where the invoice failed. Looking at the data, at first there didn’t seem to be anything wrong. The data was in a readable format. When I used the arrow key to move across the row, I found between one of the fields there was a stop movement—an extra carriage return on one of the fields. I wrote a script to read the text file, find any field that has 2 carriage returns, and remove one of them.
Here are a few other tips for importing historical invoice data into Dynamics GP:
- Historical invoice data should be brought in as drop ship so it does not affect the inventory and posting will run quickly.
- Create a dummy Cash Account for the historical invoice payment. This way it will not affect the live Cash Account.
- Import files for transactions should be small and manageable so if there’s a problem, you don’t have to wait for the entire file to finish importing to delete the batch and start over again. With small files, it’s much easier to manage any issues. Maintenance data files should be fine if it’s a large file.
- These files should always be in text format which makes it much easier to find strange characters as I had to do in my example above.
Hopefully these tips will help you and we invite you to contact us at MIBAR if you need additional support with Dynamics GP.