52475_en-US

liliconcom

How to process large amounts of Run Program with Read Function

I am trying to run an external application which is printing large amounts of comma-separated data to the console. The output is essentially a large datatable, coming from a .NET application. The "rows" are comma-separated and each row is followed by a newline character. The first row are headers and the datatable needs to be updated with these. I followed the example code which reads the console, but i'm running into issues where some of the lines are coming in partially complete (not separated at the newline). I am trying to then strip off the partial line and append it to the beginning of the next line, but I'm still missing a few lines (~35 out of 4338). If i write to a text variable and process once the program is finished, it works, but it's a lot slower (presumably because the string is really long and it's not concurrent with the external application, but that's just my guess). 

 

Are there any ways to control when the Read Function is invoked so that i can capture full lines at a time? Is there any known method to handle this (the data is 4000 rows by ~200 columns with probably about 50% of the cells filled and the data about 10-char strings per cell that is filled, in case that is relevant).

 

I do have access to the .NET source code and can modify the output to make it JSL friendly, in case that is needed.

 

I am pasting my code, replacing sensitive column names with "IDx". Thanks in advance!

results = Run Program(
  Executable(myExecutable), 
  Options(options), 
  Read Function(Function({this},
   fullValue = this << Read;
   lines = Words( fullValue,"\!n" );
   lineIndex = 1;
   totalLines = totalLines + N Items(lines);
   callCount++;
   words = Words(lines[1], ",");
   if (N Items(words) > 1,
    if (extraContent != "", lines[1] = appendExtraContent(lines[1],extraContent),);
    newExtraContent = getExtraContent(fullValue);
    if (N Items(lines) == 1, 
     extraContent = extraContent || newExtraContent, 
     extraContent = newExtraContent
     );
    if (extraContent != "", 
     print(lines[NItems(lines)]);
     Remove From(lines, N Items(lines)); 
     extraLines++;,
     );
    if (N Items(lines) >= 1,
     if (first == 1, 
      headers = Words(lines[1],",");
      For (i = 14, i <= N Items(headers), i++,
       dt << New Column(headers[i], Character);
      );
      headerCount++;
      first = 0;
      lineIndex++;
      cols = dt << Get Column Names();,);
     For( i = lineIndex, i <= N Items(lines), i++,
     //print(lines[i]);
      count++;
      word = break_string_by_sep(lines[i], ",");
      if ( N Items(word) > maxHeaders, maxHeaders = N Items(word),);
      if (N Items(word) >= 14,
       dt << Add Row({
        :ID1 = word[1];
        :ID2 = word[2];
        :ID3 = Num(word[3]);
        :ID4 = Num(word[4]);
        :ID5 = word[5];
        :ID6 = Num(word[6]);
        :ID7 = word[7];
        :ID8 = Num(word[8]);
        :ID9 = word[9];
        :ID10 = word[10];
        :ID11 = Num(word[11]);
        :ID12 = Num(word[12]);
        :ID13 = Num(word[13]);
        For (k = 14, k <= N Items(cols) , k++,
         if (N Items(word) >= k,
          AsColumn(dt,cols[k]) = word[k],
         );
        );
       }),
      );
     );
    ,);
   ,);
  );
 )
 );
 While (!(results << Is Read EOF), Wait(1));
 dt << ShowWindow(1);
);

appendExtraContent = Function({partialValue, extra}, {default local},
 fullLine = extra || partialValue;
 fullLine;
);
getExtraContent = Function({allLines}, {default local},
 lines = Words(allLines, "\!n");
 lastValue = N Items(lines);
 startValue = Contains(allLines, lines[lastValue]);
 last7 = Substr(allLines, -1);
 if (last7 == "\!n",
  extra = "",
  extra = Substr(allLines, startValue);
 );
 extra;
);

 

Re: How to process large amounts of Run Program with Read Function

try somthing like this (written for windows .bat file to simulate your external data source):

dt = New Table( "fetched", New Column( "a" ), New Column( "b" ), New Column( "c" ), New Column( "d" ), New Column( "bufsize" ) );
// https://stackoverflow.com/questions/2591758/batch-script-loop
bat = Save Text File( "$temp/data.bat", "@echo off\!nfor /l %%x in (1, 1, 100000) do echo %%x, 444, 555, 666" );
buffer = "";
runprog = RunProgram(
    executable( "CMD.EXE" ),
    options( {"/C", bat} ),
    readfunction(
        Function( {this},
            {data},
            While( this << canread,
                buffer = buffer || (this << read);
                While( Pat Match( buffer, Pat Break( "\!n" ) >> data + "\!n", "" ),
                    dt << addrow( 1 );
                    {dt:a, dt:b, dt:c, dt:d} = Words( data, "," );
                    dt:bufsize = Length( buffer );
                    If( Mod( N Rows( dt ), 1000 ) == 0,
                        Wait( 0 )
                    ); // show the updates
                );
            )
        )
    )
);

The 100,000 lines takes some time with the table updating (15 seconds, maybe). It runs a lot faster (~4 seconds) this way:

dt = New Table( "fetched", New Column( "a" ), New Column( "b" ), New Column( "c" ), New Column( "d" ), New Column( "bufsize" ) );
// https://stackoverflow.com/questions/2591758/batch-script-loop
bat = Save Text File( "$temp/data.bat", "@echo off\!nfor /l %%x in (1, 1, 100000) do echo %%x, 444, 555, 666" );
buffer = "";

dt << begin Data Update;

runprog = RunProgram(
    executable( "CMD.EXE" ),
    options( {"/C", bat} ),
    readfunction(
        Function( {this},
            {data},
            While( this << canread,
                buffer = buffer || (this << read);
                While( Pat Match( buffer, Pat Break( "\!n" ) >> data + "\!n", "" ),
                    dt << addrow( 1 );
                    {dt:a, dt:b, dt:c, dt:d} = Words( data, "," );
                    dt:bufsize = Length( buffer );
                );
            )
        )
    )
);
Write( "\!nWaiting\!n" );
While( !(runprog << isreadeof), Wait( .01 ) );

dt << end Data Update;

Write( "\!nDone" );

<