Import a Journal from Excel to Microsoft Dynamics NAV

On the third day of Christmas our NAV elves bring to you a ‘how to guide’ on importing a Journal from Excel to Microsoft Dynamics NAV.

In this quick how to guide, we illustrate how easy it is to import journal data from Excel into Dynamics NAV 2017.
The Microsoft documentation for this is helpful, but we thought we would show you the tips and tricks to getting this working right first time.
Note that in Microsoft Dynamics NAV 2018, this feature is further enriched as you will be able to edit the data straight in the Excel application.
If you’re executing this routine for the first time, consider creating a template from a Dynamics Nav 2017 journal batch. This will provide you with all the fields that you might require when profiling your batch for the Excel export / import process.
To profile a journal ‘right click’ on a column heading, and select ‘choose columns’ from the menu you’ve invoked and profile your journal by adding or removing columns to suit your needs.

• Once the required columns have been selected, use the CTRL+E shortcut key to export your journal to Excel.
• Alternatively, highlight all the rows in the journal, ‘right click’ and select ‘copy rows’ from the menu option (CTRL+SHIFT+C is the keyboard shortcut.). If you prefer this option, you will need to open Excel and paste the rows in to an Excel worksheet.

• You can populate Excel (also taking advantage of Excel tools like formulas) with data ready to be copied back into the Dynamics NAV 2017.

• To copy your data back into Dynamics Nav 2017, highlight the journal area in Excel and ‘Copy’ it by using for example the ‘CTRL+C’ keyboard function. Note that the description and column heading rows are omitted from the copy selection. (Rows 1 and 2 in the following screen image)

• Select a new row in your Journal batch in Dynamics NAV 2017, ‘right click’ and select the “Paste Rows” option. (Alternatively, use the CTRL+SHIFT+V keyboard shortcut.) Note that you can append to a journal batch which already has lines it. It doesn’t have to be an empty journal.

Tips and Tricks
• Mapping
o Making sure the columns in the worksheet match the columns in the Journal is key.
• Consistency
o Option fields (For example Account Type and Account Number) should always be entered in Excel as they appear in Dynamics Nav 2017. To see your options, use the relevant drop-down menu in Dynamics Nav 2017.
o Code fields, or fields which validate to a different table must also mirror what you have in Dynamics NAV 2017.
The above illustrates how you can paste a journal into Microsoft Dynamics NAV 2017.

If you would like further assistance or would like to see how this works in Microsoft Dynamics NAV 2018 please do not hesitate to contact us.