Reading a .csv file with DotNet (and Collections!)

jensthomsen

Hi
I'm trying to read a CSV file that looks something like this:

A;1;;3;4;5;6;7;;8
B;1;;3
C;;;;

I'm using Nav 2015.

My goal is to be able to do the reading via DotNet. My idea is as follows:
1. Read the file line by line
2. For each line I'll split (by using 'Split' command) the line with ';' as separator, and store the content in a DotNet array - the array will be of variable size, according to the number of ';'
3. Read the Dotnet array from (2) and store the values in a table.

This is what I've come up with until now:

OBJECT Codeunit 70010 Read CSV
{
OBJECT-PROPERTIES
{
Date=23-11-15;
Time=14:58:43;
Modified=Yes;
Version List=;
}
PROPERTIES
{
OnRun=VAR
InFile@1160180000 : File;
inStream@1160180001 : InStream;
streamReader@1160180002 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.StreamReader";
encoding@1160180003 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Text.Encoding";
String@1160180006 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.String";
CSVFields@1160180007 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Collections.Generic.List`1";
txt@1160180004 : Text;
FileManagement@1160180005 : Codeunit 419;
BEGIN
InFile.OPEN(FileManagement.UploadFileSilent('C:\Temp\filetoread.txt'));

InFile.CREATEINSTREAM(inStream);

streamReader := streamReader.StreamReader(inStream, encoding.Default);

WHILE NOT streamReader.EndOfStream DO BEGIN
String := streamReader.ReadLine();
//do something with split....
CSVFields := String.Split(';');
END;
END;

}
CODE
{

BEGIN
END.
}
}


But I'm missing the Array-part:-(

EvR

Split is an extension method on an instance of String. You cannot use those directly from C/AL.
You could write a small assembly to do this for you in C#. Just return a generic list of a custom class and then loop through it in C/AL.

But honestly, why do do you want to do it like this? Why not an xmlport?

jensthomsen

"But honestly, why do do you want to do it like this? Why not an xmlport?"...Sure, C/AL and a XMLPORT could also do the job. But it's always great to explore new possibilities (although they may not be better than the old ones:-))

Fiddi

Hello,

so why do you not to use the new possibilities of C/AL?
This example:

OBJECT Codeunit 50003 testGet
{
  OBJECT-PROPERTIES
  {
    Date=24.11.15;
    Time=08:08:19;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=VAR
            cust@50000 : Record 18;
            InpString@50001 : Text;
            Size@50002 : Integer;
            NoOfFields@50003 : Integer;
            SplitArr@50004 : ARRAY [100] OF Text;
          BEGIN
            UPLOADINTOSTREAM('Import','',' All Files (*.*)|*.*',FileName,instr);
            WHILE NOT instr.EOS DO BEGIN
              Size := instr.READTEXT(InpString);
              IF Size <> 0 THEN BEGIN
                NoOfFields := Split(InpString,';',SplitArr);
                IF NoOfFields <> 0 THEN BEGIN
                // do something specialwith SplitArr
                MESSAGE('%1',NoOfFields);
                END;
              END;
            END;
          END;

  }
  CODE
  {
    VAR
      instr@50001 : InStream;
      FileName@50003 : Text;

    LOCAL PROCEDURE Split@50000(Splitstr@50000 : Text;SplitChr@50002 : Char;VAR SplitArr@50001 : ARRAY [100] OF Text) NoOfFields : Integer;
    VAR
      i@50003 : Integer;
    BEGIN
      FOR i:=1 TO STRLEN(Splitstr) DO BEGIN
        IF Splitstr[i] = SplitChr THEN
          NoOfFields+=1
        ELSE
          SplitArr[NoOfFields+1] +=COPYSTR(Splitstr,i,1);
      END;
      IF NoOfFields <> 0 THEN
        NoOfFields+=1;
    END;

    BEGIN
    END.
  }
}

should do the same. And if put into a report, you may get the same behavior as the old dataports, with a requestpage where you may enter or select a filename before you start the import.

Regards Fiddi

EvR

jensthomsen wrote: »

"But honestly, why do do you want to do it like this? Why not an xmlport?"...Sure, C/AL and a XMLPORT could also do the job. But it's always great to explore new possibilities (although they may not be better than the old ones:-))

There is nothing wrong with that at all. Don't get me wrong, I'm all for it.
But if there's a proven technology in the system that does everything you want while the new way has no added value and only brings extra complexity, you might think twice

btastic

You can do this:

Name	DataType	Subtype	Length
Type	DotNet	System.Type.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
strArray	DotNet	System.Array.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
ArrOfTypes	DotNet	System.Array.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
ArrOfValues	DotNet	System.Array.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
MethodInfo	DotNet	System.Reflection.MethodInfo.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
StringSplitOptions	DotNet	System.StringSplitOptions.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
Enumerator	DotNet	System.Collections.IEnumerator.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
currString	DotNet	System.String.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	

// get the type of the .net string
Type := GETDOTNETTYPE(newString);

// string array with size 1 to set our delimiter
strArray := strArray.CreateInstance(GETDOTNETTYPE(newString), 1);
strArray.SetValue('|', 0);

Now you need some reflection to create a .net instance of type string

//Create a type array of the size 2 (the string.split function has 2 parameters)

ArrOfTypes := ArrOfTypes.CreateInstance(GETDOTNETTYPE(Type), 2);
ArrOfTypes.SetValue(strArray.GetType(), 0); // first value (0) is the type of our string array
// second parameter is StringSplitOptions
ArrOfTypes.SetValue(GETDOTNETTYPE(StringSplitOptions), 1);

Now we use the MethodInfo object, to get the function from reflection:

// ArrOfTypes is used to indentify the correct split function we need (there are multiple overloads!)
MethodInfo := Type.GetMethod('Split', ArrOfTypes);

After we have the method signature, we can use MethodInfo.Invoke to invoke the function. The return value is a string array.
strArray := MethodInfo.Invoke(newString, ArrOfValues);

Now you have a "|" delimited string array which you can easily iterate over with:

Enumerator := strArray.GetEnumerator();
WHILE Enumerator.MoveNext DO BEGIN
currString := Enumerator.Current(); 
END;

To make a conclusion what this code does:

We create a type array to determine our function signature. After that we call the GetMethod function, to get our Method by the signature we initially declared. In our type array.

After that, we invoke the function we just got by GetMethod.

Best regards