This guide assumes that you have exported an Excel file from Microsoft Dynamics NAV in the following format, as detailed in the Microsoft Dynamics NAV: Export the general ledger guide.

Transform the data

The problem

1. In order to ingest a file into MindBridge, the data needs to be in an ungrouped format. This means that the columns have data present on every row. As you can see from the screenshot above, the entries from the MYOB AccountRight Excel export are grouped by Account, with all of the individual postings sitting below a single account reference.
2. Ideally the first row of the file should be the header rows. Any extra rows above the header row should be removed.
3. The data for the Source No. column is not located on the same row as the rest of the entry.

The solution

1. Save a copy of the original export file in the .csv format.
   
   
2. Close Excel and re-open the newly saved .csv version of the file.
   
   
3. Remove the rows above the column headings.
   
   
4. Insert two new columns for the Account Number and Account Description. Add these as blank columns to the left of your data set. This can be accomplished by right clicking on the "A" on the top left corner of the spreadsheet just under the ribbon and selecting "insert". Repeat this twice to insert 2 new columns.
   
   
5. We will grab the "Account Number" starting in cell "A4" using the following formula:  =IF(IFERROR(SEARCH("account:",C4),FALSE),RIGHT(C4,5),A3)
   
   
   
6. To grab the "Account Description", we will utilize the following formula starting in cell 'B4": =IF(IFERROR(SEARCH("account:",C4),FALSE),F4,B3)
   
   
7. The data for the Source No. is found on the row below the row containing the rest of the entry information. To bring the Source No. to the correct row, we will create a new column and name it "Source Number". If using column "R" as the designated column, input "Source Number" in cell "R1" as the column header. In cell "R9" input the following formula: IF(ISTEXT(H9),J10,"")
8. Copy and fill both the newly created Account Number, Account Description, and Source No. rows to the bottom of the workbook. This can be accomplished by double clicking the bottom right handles the cells containing the formulas. 
   
   
9. Copy the "Account Number" column by clicking on the letter "A" just below the ribbon, right click, then select "Copy".                        
   
   
10. With the column still selected, right click anywhere within column A, right click, select the clipboard icon with numbers under paste options to paste as values.
    
    
11. Repeat steps 9 and 10 for both the "Account Description" and "Source Number" columns.
12. We now need to delete any blank and unused rows. To do this, use the filter on the date column and de-select any date values.
    
    
13. Then Delete all filtered rows by right clicking anywhere inside the highlighted cells and selecting "Delete Row".
    
    
14. Clear the filter by navigating to the Data ribbon and selecting "Clear". Finally, delete any unused columns. To do this, use the filter on columns that appear to be blank and check that no data is present.

Previous step: Microsoft Dynamics NAV: Export the general ledger

Next step: Microsoft Dynamics NAV: Import the general ledger into MindBridge

What do you think of this guide? If you have any feedback, or you think you can help us improve it, then we would love to hear from you! Please contact us at support@mindbridge.ai. We are offering rewards for any information that leads to the improvement of a guide or the creation of a new one.

Anything else on your mind? Chat with us or submit a request for further assistance.