Sunday, June 7, 2015

Paste General Ledger Transactions from Excel into Dynamics GP 2013 R2 and Later

There were so many features introduced in Dynamics GP 2013 and 2013 R2, it was genuinely hard to keep track of them all. One of these features was the ability to Paste from Excel to the General Ledger Transaction Entry window.

Pasting from Excel to the General Ledger Transaction Entry window is accomplished by clicking on the Paste button (complete with Excel logo), as shown in Image 1 below. The Transaction Entry window navigation is Financial > Transactions > Financial > General, and will look much like the image below. 

NOTE: Your user experience may vary - I have some 3rd party products installed, so my Paste button was small and hidden, yours may very well be front and center.


Image 1

Data cannot be pasted into the Header fields of a General Ledger Transaction - seem Image 2 below. So, it is important these required fields be filled out manually prior to pasting the transaction details in the Transaction Line entry portion of the window.


Image 2

Once the header information has been manually entered, you can select the data from Excel and paste it into the Transaction Line entry section of the Transaction Entry window. It is important to initiate the paste in the first field of the Line Entry section - see Image 3 below.


Image 3

Image 4 below, is a 'Paste from Excel Template'. The headers are simply to help identify what data goes where.  When pasting, only the data needs to be selected and copied.  

NOTE: The first column maps, in my opinion, counter-intuitively to the Distribution Reference (not the Account Description - I have seen some conflicting information online).


Image 4
Once you have your desired General Ledger transaction data is selected in Excel, simply right click and choose Copy from the menu (or use Ctrl + C). Then move to the first field of the Line entry section of the Transaction Entry window (make sure you have already filled in the required header data), and click the paste button, which is located in the Menu Bar or Action Pane in the header (right clicking and pasting will not work). If you are unsure of where to start your paste, refer to Image 3 above. If you can't find the Paste button, refer to Image 1 above.

Image 5
The data should look substantially like Image 5 above. I have collapsed the lines, using the chevron icons to the extreme right of the headers in the line entry section of the window in order to show the matching transaction. Expanding the lines will show the Distribution Reference data was also successfully imported - see Image 6 below.

Image 6

ERRORS & TROUBLESHOOTING

If you have your Excel file format wrong, or selected the headers during the copy process, you will be instantly rewarded with a Validation report indicating what's wrong with your data.


Image 7
In the example displayed in Image 7 above the description column of my Excel template file was pasted into the account field by the utility, which caused a cascading failure of all other data, putting it out of place.

NOTE: The Template format is critical to successfully copying data from Excel. It is also important, not to select the column headers when copying, as they are unnecessary, and will throw the import off. It is also unnecessary to have dollar values in every import field; therefore, zero dollar values should not be entered in the debit or credit columns. Do not select lines below your data set, creating blank rows when pasting data from Excel can cause errors when saving and posting

Infrequently for most, and more frequently for beginners you may encounter the following error: 
The stored procedure glpPostBatch returned the following results: DBMS: 0, Microsoft Dynamics GP: 20052. The "More info" button displays: glpPostAccount: Error occurred NULL parameter.
The typical cause for this error is the inclusion of a line or lines below the pasted data in the Transaction Entry Line entry section, typically blank line(s).  It is cheap insurance to right click and delete the last row in this window, after pasting your data from Excel.

Recently, a user reached out to me to report they had received the following error when attempting to paste a transaction:



Here is a link to a blog article, which ultimately resolved the issue:

Cut and Paste Blog Article

The core problem was this "new users" was not granted access to the error report in Image 7 above.

I hope this How To on the Excel Paste feature gets you where you're going.


6 comments:

  1. One of my customers also got the glpPostBatch error messages which was resolved by entering the required MDA information for the particular line.

    ReplyDelete
  2. Hello Harry,
    I was wondering if you have ever come across an instance where when you click on the Paste icon, nothing happens. No exception report, no paste, nothing. It does work at another machine in the same office. I attempted the steps logged in as 'sa' on both machines. The machine that would not work for the user also does not work for 'sa'. They are using Office 2013 with GP 2013 R2. They do not use AA.

    Any thoughts on the matter?
    Thanks,
    Sheila Jefferson-Ross

    ReplyDelete
    Replies
    1. Sheila -

      You did a fine job of troubleshooting 101. Trying the same transaction on another workstation and as the sa user is a good way to narrow down causation. This looks like a workstation install problem.

      Were I you, I would copy the workstation folder (GP2013) from the working machine to the machine with the problem. This may not be practical if one workstation is 32bit and the other is 64bit, but this approach has worked on prior occasions. Remember to back up the workstation folder on the problem machine prior to attempting this.

      Good Luck.

      Delete
  3. Thanks Harry, that is certainly worth a try. I'll let you know how it goes.

    Sheila

    ReplyDelete
  4. Just an FYI to Sheila's comment. I was running into the same thing and came across the following post. The Dex.ini row was needed in our environments.

    https://support.microsoft.com/en-us/kb/3119802

    ReplyDelete
  5. Hey! Could you please tell me what is the main difference between Dynamics GP and AX, provided by microsoft dynamics partner?
    I'm starting up my own company and thinking what software to choose.

    ReplyDelete