Read & process CSV files programmaticaly

LOCAL ReadCsvFile()
fmode := fmode.Open;
fstream := fstream.FileStream(‘c:\temp\test.csv’,fmode);
enc := enc.UTF8; // read text as UTF8 charset, can also be Default (Windows) or Ascii
reader := reader.StreamReader(fstream,enc);
REPEAT
Line := reader.ReadLine;
//MESSAGE(Line);
ColNo := 1;
REPEAT
CellValue := StringSplit(Line,’;’);
Column[ColNo] := CellValue;
ColNo += 1;
UNTIL Line = ”;
// Process columns
UNTIL reader.EndOfStream;
reader.Close;
fstream.Close;

//variables: all .net vars RunClient=Yes
CellValue Text
Column Text, Dim 20
Line Text
ColNo Integer
reader DotNet System.IO.StreamReader.’mscorlib
fstream DotNet System.IO.FileStream.’mscorlib
fmode DotNet System.IO.FileMode.’mscorlib
enc DotNet System.Text.Encoding.’mscorlib

LOCAL StringSplit(VAR Text : Text[1024];Separator : Text[1]) Token : Text[1024]
Pos := STRPOS(Text,Separator);
IF Pos > 0 THEN BEGIN
Token := COPYSTR(Text,1,Pos-1);
IF Pos+1 <= STRLEN(Text) THEN
Text := COPYSTR(Text,Pos+1)
ELSE
Text := ”;
END ELSE BEGIN
Token := Text;
Text := ”;
END;

//variables
Pos Integer

Also have a look at:
https://www.olofsimren.com/xml-buffer-and-csv-buffer-tables/
http://www.dynamics.is/?tag=csv-file

Advertisements

Access protected file share

There was a post, where a user wanted to get read/write acces to a file share outside the current domain from within Nav.

This can be done via .Net:

NetworkCredential := NetworkCredential.NetworkCredential(username, password, domain); // Credentials for domain 2
CredentialCache := CredentialCache.CredentialCache;
Uri := Uri.Uri(‘\\server’); // the server name from domain 2
CredentialCache.Add(Uri, ‘Basic’, NetworkCredential);
Dirs := SysDir.GetDirectories(‘\\server\directory’); // get folder list from the file share
FOR i:=1 TO Dirs.GetLength(0) DO
DirListTxt := DirListTxt + FORMAT(Dirs.GetValue(i-1))+’\’; //.net index starts with 0
MESSAGE(DirListTxt); // print out the dir list

variables
NetworkCredential DotNet System.Net.NetworkCredential.’System
CredentialCache DotNet System.Net.CredentialCache.’System
Uri DotNet System.Uri.’System
Dirs DotNet System.Array.’mscorlib
SysDir DotNet System.IO.Directory.’mscorlib
DirListTxt Text
i Integer

 

User bulk import with powershell

the ps command to add single users in nav is new-navserveruser. detailed info can be found here:
https://docs.microsoft.com/en-us/powershell/module/microsoft.dynamics.nav.management/new-navserveruser?view=dynamicsnav-ps-2018
https://community.dynamics.com/nav/b/magnosblog/archive/2015/08/26/creating-users-with-powershell

i found a great ps script for bulk import:
https://marcellusnav.wordpress.com/2017/07/31/import-nav-users-from-csv-with-powershell/

to work with that, here is a tutorial:

* start the windows powershell ISE in admin mode. this is needed, because only then your are allowed to create new users in nav.

* create a csv text file with comma as separator. semicolon does not work here. save it e.g. as c:\temp\users.csv. check, if the windows users in the list are created on your server (AD).

UserName,FullName,Email
domain\user1,John,john@test.com
domain\user2,Adam,adam@test.com

* create a new text file and copy the script into the text file, save it as c:\temp\Import-NavUsersFromCSV.ps1

* in the ps ISE write “cd \temp” to change to the working directory.

* Import-Module .\Import-NavUsersFromCSV.ps1
to load the new script

* $Permissions = (‘SUPER’)
to set the default user permissionset for the new users

* at last:
Import-NavUsersFromCSV -NavVersion 100 `
-NavServerInstanceName ‘DynamicsNAV100’ `
-AuthenticationMethod Windows `
-ImportFilePath ‘C:\Temp\users.csv’ `
-PermissionSets $Permissions

result:
Creating domain\user1
Verbose: Opening admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’
Verbose: Admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’ has been opened
Verbose: Ausführen des Vorgangs “New-NAVServerUser” für das Ziel “ServerInstance = MicrosoftDynamicsNavServer$DynamicsNAV100, Sid = S-1-5-21-236
3956306-4252412874-2321867446-1054, WindowsAccount = domain\user1”.
Verbose: Closing admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’
Verbose: Admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’ has been closed
Verbose: Opening admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’
Verbose: Admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’ has been opened
Verbose: Ausführen des Vorgangs “New-NAVServerUserPermissionSet” für das Ziel “ServerInstance = MicrosoftDynamicsNavServer$DynamicsNAV100, Permi
ssionSetId = SUPER, Sid = S-1-5-21-2363956306-4252412874-2321867446-1054, WindowsAccount = domain\user1”.
Verbose: Closing admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’
Verbose: Admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’ has been closed
Creating domain\user2
Verbose: Opening admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’
Verbose: Admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’ has been opened
Verbose: Ausführen des Vorgangs “New-NAVServerUser” für das Ziel “ServerInstance = MicrosoftDynamicsNavServer$DynamicsNAV100, Sid = S-1-5-21-236
3956306-4252412874-2321867446-1055, WindowsAccount = domain\user2”.
Verbose: Closing admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’
Verbose: Admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’ has been closed
Verbose: Opening admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’
Verbose: Admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’ has been opened
Verbose: Ausführen des Vorgangs “New-NAVServerUserPermissionSet” für das Ziel “ServerInstance = MicrosoftDynamicsNavServer$DynamicsNAV100, Permi
ssionSetId = SUPER, Sid = S-1-5-21-2363956306-4252412874-2321867446-1055, WindowsAccount = domain\user2”.
Verbose: Closing admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’
Verbose: Admin connection to ServerInstance ‘MicrosoftDynamicsNavServer$DynamicsNAV100’ has been closed

the “change password at next login” flag is not set. this is only set if you use the -ChangePasswordAtNextLogOn option with the New-NAVServerUser command.

 

Performance compare tests between Nav 2013 and Nav 2017

There were some discussions about the newer Nav versions performance. As for my own experience, i can say Nav 2017/2018 releases are quite slow.

To get a better feeling about that i did the following:

I converted Nav 2013 Cronus db (build 46056) to Nav 2017 (build 13682) by simply run the convert job in the dev. env. nothing more. then created a nav service for the converted nav2013-db.

test scenario: i wanted to test the load time for the item list page. in many huge database there can be 1000s and 1000s of items. it’s a very important page, very often loaded per day by many people. so high peformance would be a good thing. in both databases are the same no. of items: 145. in both pages are the same columns/fields in the same order shown. i have the same nav system, same binaries, but different db. so the situation is quite ok for a valid comparison.

next step: i wrote a simple test cu for loading the item-list page.

[Test] TestItemList()
// ItemList / TestPage / item list
// dt / DateTime
dt := CURRENTDATETIME;
ItemList.OPENEDIT;
MESSAGE(FORMAT(CURRENTDATETIME – dt));

i started the test cu with the simple test function and measured the time:
1. run: 270ms
2. run: 230ms
3. run: 170ms

now copied that test cu to the nav2017 db, ran the same test, measured the time:
1. run: 380ms
2. run: 320ms
3. run: 300ms

WOW!!!!!! simple test, the numbers tell the story!
What’s about other main list pages (customers, vendors, ledger entries and so on)? What about performance of posting processes? What would i measure, if i continue that kind of tests? i think better not measure …

Is that really a progress??? What is the advantage of new features, if the performance lowers and lowers from release to release ???

i think here is a todo for the nav microsoft team.

cheers

 

 

MVP Award part 2 … more or less

mvp-logo

Got mail … not from Microsoft, but from FedEx …

Bildschirmfoto_2017-07-06_04-16-53

So, what does that mean ? Do i get my 2. MVP Award ? A bit Yes and a little bit more of a No. After clarification with Cristina Herero (MVP Program Manager Europe) will MVP Awards given on Oct/16 or later – normally valid for one year, then the candidate will be reevaluated – be extended til July/2018. Veeeery strange, does not feel like a real MVP Award and it’s a bit unfair. But we have to accept that.

All of that because of the changes in the MVP Award Renew Cycle some of us new MVPs get that info in that way.

Like last time: Many, many thanks to my girlfriend for supporting me for so many years in so many ways, bringing light, love and friendliness into my life.

Many thanks to the readers of my Nav blog and the members of the Nav community.

cheers