Best Way to Fix Inconsistent Data

(0) Share

Report

Posted on by edwardl

We have a large amount of inconsistent data throughout our records in our NAV database. An example of this is our customers phone numbers. We have 1000 records with dashes (the incorrect way) and the rest are without dashes. We search for customers by phone number without dashes so this has us creating duplicate customers in our system. There are many other fields such as postal code which are inconsistent as well.

Would the best way to fix this be by creating a dataport and export the data into Excel, fix it there, and then import the data back into NAV or is that not worth the trouble and we should simply have some of our part-timers spend a few days opening up each record within NAV and do it manually?

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (3)

Suggested answer

keoma 32,729 on at

Like (0)

Report

best (and common) way to fix that is creating a processingonly report or a codeunit, means a batch job. the custom code should run through all customer records, check the phone number and fix the wrong, save, go on. no need to export the records to excel.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

I would think a 1,000 records could be fixed in Excel waaaaay faster than a custom program effort.

Download into Excel, highlight the phone number column, "REPLACE" dashes, with a blank (not a space, a blank), and you're done.

If you want, Excel has a Custom Number Format that's specifically for phone numbers, but it doesn't sound like you want any dashes in your records.

Upload back into NAV.

If you expect to do this cleanse every so often, then that is where the custom programming might come in, though it would probably still be faster to just write down the step-by-step procedure someplace: download/Replace/Upload.

Was this reply helpful? Yes No
edwardl 2 on at

Like (0)

Report

"Upload back into NAV".

How would you recommend I go about this?

Was this reply helpful? Yes No
Verified answer

keoma 32,729 on at

Like (0)

Report

i prefer using custom reports/codeunits. this is in my opinion fast enough and usable for NAS jobs. as you suggested is using a dataport one of the options to export/import data to/from an excel file (csv). other solution: using table excel buffer.

Was this reply helpful? Yes No
Verified answer

Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (0)

Report

I am with you, since there are many fields involved just not the phone number, it would be best for you to export the data to the excel and then make all the necessary changes and import back.

Was this reply helpful? Yes No
keoma 32,729 on at

Like (0)

Report

other possibility is to edit and modify the data using smss (sql db mgmt. tool). you have then sql/tsql for mass modifying, no export/import needed.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

you're talking developer/programming tools...I don't get the impression so far that he is either...

Some of us here are just users.

God! why is everything so complicated? Oftentimes it seems like NAV is the asylum built by its inmates.

Was this reply helpful? Yes No
edwardl 2 on at

Like (0)

Report

I do have SSMS but I don't like the idea of using SQL scripts when a simple export to excel can get my people on it right away and we don't have to worry when doing this on a Live system.

Was this reply helpful? Yes No
Suggested answer

Tharanga Chandrasekara 23,118 on at

Like (0)

Report

You can export data to excel and then do all the modification you required and then upload it back to Dynamics NAV. You can use dataport for this option. (Always export the primary key to excel since you are planning to upload data back)

As Jonathan said codeunit or process only report also a good option if your data have a pattern that you need to correct.

Was this reply helpful? Yes No