For one time analysis of data in NAV I always take the quick and easy route of copying data from NAV to Excel using the windows clipboard. Running the relevant table(s) from Object Designer, copy (preferably) all records and paste the whole bulk into an Excel sheet. It can take some time depending on the number of records in the table, but once copied into Excel I can filter and aggregate whatever and however I want to. And no need to write any code in NAV.
Recently I did encounter an issue I had never come across in all these years. Once the data were copied to Excel it looked like this:
Note that I have setup this example for this blog as the real data were more complex and of course somehow business sensitive, but for sure making more sense. Nevertheless the example above is plainly displaying the issue, i.e. Description fields containing control (and non-printable) characters like TAB (hexadecimal 09), LF (hexadecimal 0A) and CR (hexadecimal 0D), inserted directly after the word description (and before the opening parenthesis), and when copied to Excel these control characters seem to mess up the orderliness in my records as TAB, CR and LF are interpreted as columns delimiters!
Ouch, this wasn’t fun as, in my real data, I had many records contaminated with these characters. As it was a one-time-only thing I decided to clean up my data manually. At the same I was wondering what the source of this contamination was and why NAV was not able to copy the data to the clipboard right, i.e. handle field values as coherent blocks of data. I learned that the real data was (partly) imported from WORD files thus accounting for the presence of the control characters.
BTW: this is how my data looked like in NAV:
To get the data into this Test Clipboard table I had created a .csv file with the 6 records inserting TAB, CR and LF (and CR/LF) at the relevant spot and imported the data using a straight forward dataport (no code, no customized properties,1 dataitem and three dataport fields).
Yes and No.
Yes, I was just surprised and I would have expected the data to show right in Excel.
No, of course the clipboard isn’t a major NAV feature and for exchanging data between NAV and other applications like Excel we have other possibilities (even though you might want to have a look at the To Increase clipboard feature).
Fiddling around with my clipboard test in NAV classic just to investigate and understand what I had come across, some interesting anomaly showed up when copying data from classic form clipboard or a RTC page to the windows. Have a look:
Do you see the difference? (If not, compare row 3 in both screen shots.)
Funny that CC is straight forward apparently not taking into account control characters in text fields, but RTC seems to deal differently between TAB on one hand and CR and LF on the other.
Of course this is what I expected:
Yes and No. Still, did report it to MS. You might want to give your vote to it here.