Three Excel Tricks for Lining up Data for Import into Dynamics NAV, GP, AX

image

Recently, I have had to import lots of different data for clients.  They give me Excel worksheets that are full of names, numbers and telephone numbers.  If you are working with Dynamics NAV or GP, you know that some of the fields can be very fussy in the import.  Dynamics AX is more forgiving.  I clean up the data, and get it ready. 

Most clients ask me how I clean up columns, especially phone numbers. If you use Excel a lot, you will know these formulas or tricks.  This post is for those who do not have to do this very often, and need some help.

1. Using Excel to change Words from All CAP to All lowercase, or to PROPER

    • you can use the UPPER, LOWER, or PROPER functions to automatically change the case of existing text to uppercase, lowercase, or proper case.
    • First, insert a temporary column next to the column that contains the text you want to convert
    • In the temporary column, for example,  cell B2, type =PROPER(A2)
    • The other choices are  =UPPER(A2) or  =LOWER(A2)

Now, all of the names, countries, states, provinces, etc. can have standardized type.

2. Removing spaces and symbols in a phone number

    • Often we get a list of telephone numbers that look like this

image

    • To get rid of the (, ), –, Ext. 0000, use the find and replace function
    • To get rid of the space, use the find and replace, using the space bar
      1. Press Ctrl + Space to select all cells in a column.
      2. Press Ctrl + H to open the “Find & Replace” dialog box.
      3. Press Space bar in the Find What field and make sure the “Replace with” field is empty.

3.  Filling in the extension on a phone number

    • Now  you want to add trailing zeroes to make all the numbers with a fixed length, such as 14 digits for a telephone number in Canada and the US
    • Create a blank column right of the existing phone number
    • Use the EXCEL function =A2&REPT(“0”,14-LEN(A2)) where “A” represents the column with the telephone number in it
    • The results are

image

      And, the number now becomes easy to import.

New “How To” Book on Dynamics AX Implementation

Microsoft Dynamics AX Implementation Guide

As Dynamics AX  implementations become more complex because of changing software technologies, you may benefit from this “how to” guide  by long time AX implementers, Yogesh Kasat of Real Dynamics and JJ Yadav of Ignify.  The book is available from Packt Publishing at https://www.packtpub.com/application-development/microsoft-dynamics-ax-implementation-guide

The Table of Contents covers:

Table of Contents

1: PREPARING FOR A GREAT START

2: GETTING INTO THE DETAILS EARLY

3: INFRASTRUCTURE PLANNING AND DESIGN

4: INTEGRATION PLANNING AND DESIGN

5: DATA MIGRATION – SCOPING THROUGH DELIVERY

6: REPORTING AND BI

7: FUNCTIONAL AND TECHNICAL DESIGN

8: CONFIGURATION MANAGEMENT

9: BUILDING CUSTOMIZATIONS

10: PERFORMANCE TUNING

11: TESTING AND TRAINING

12: GO-LIVE PLANNING

13: POST GO-LIVE

14: UPGRADE

This book is very useful for newcomers to the Dynamics AX world.