CSVFileToArray combined with Separate delays DelArray

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Enricoys
Junior Coder
Posts: 24
Joined: Mon Jan 06, 2014 1:18 pm

CSVFileToArray combined with Separate delays DelArray

Post by Enricoys » Tue Mar 19, 2024 10:46 am

After noticed a remarkable delay in using DelArray in some large scripts I spend some time to nail it down.

In the test script below 'c:\testfile1.csv' contains 100 identical lines of:
1,28/02/2010,50
And 'c:\testfile2.csv' contains 5000 identical lines of that same line.

When I run the test script below the time difference between t4 and t5 is about 4 seconds and between t1 and t2 close to zero milliseconds although its is the same code. If I remove the CSVFileToArray both intervals are the same. So its proven that CSVFileToArray heavily effects the efficiency of DelArray although it is an array not be used in CSVFileToArray. Doubling the size of 'c:\testfile2.csv' does also double the delay between t4 and t5 and even shows the message 'The application is not responding', actually means that the script hangs for a few seconds which can be longer by expanding the file. How to solve this massive slow down of DelArray?
(I am using Windows 10 64-bit and MS 15.0.24)

[snippet=]
ReadFile>C:\testfile1.csv,outputdata
Separate>%outputdata%,%CRLF%,outputlines
Timer>t1
DelArray>outputlines
Timer>t2
CSVFileToArray>C:\testfile2.csv,CSVdata
Timer>t3
ReadFile>C:\testfile1.csv,outputdata
Separate>%outputdata%,%CRLF%,outputlines
Timer>t4
DelArray>outputlines
Timer>t5
[/snippet]

User avatar
Grovkillen
Automation Wizard
Posts: 1021
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: CSVFileToArray combined with Separate delays DelArray

Post by Grovkillen » Tue Mar 19, 2024 12:06 pm

I suggest you don't use the delete array command and instead just make the ".._count" variable = zero.

Code: Select all

Let>outputlines_count=0
Let>ME=%Script%

Running: 15.0.24
version history

Enricoys
Junior Coder
Posts: 24
Joined: Mon Jan 06, 2014 1:18 pm

Re: CSVFileToArray combined with Separate delays DelArray

Post by Enricoys » Mon Mar 25, 2024 10:27 am

Thanks for this suggestion, however this does not work in case of using ArraySort after reusing the same array and refill it with a smaller number of elements.
Furthermore, nobody would expect a strong delay of combining CSVFileToArray with DelArray. Therefore my suggestion is to really fix this problem and when this is not possible to remove the DelArray function or at least to put a warning in the manual. It really took me quite some time to figure out the cause of this strong delay in my script.

User avatar
Grovkillen
Automation Wizard
Posts: 1021
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: CSVFileToArray combined with Separate delays DelArray

Post by Grovkillen » Mon Mar 25, 2024 11:05 am

I understand your point, a warning might be a good way of informing this.

In the mean time: loop the array using a sub routine where you set all values to a blank and then make the ..._count variable equal zero. That way you can still use the sort and omit the blank values in the new array.
Let>ME=%Script%

Running: 15.0.24
version history

User avatar
Grovkillen
Automation Wizard
Posts: 1021
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: CSVFileToArray combined with Separate delays DelArray

Post by Grovkillen » Wed Mar 27, 2024 9:03 am

Here's a proof of the problem you're facing:

Code: Select all

Let>k=0
Repeat>k
  Let>LIST_FOR_MDL=
  Let>k=k+1
  LabelToVar>unsorted_list_%k%,TEMP_list
  Separate>TEMP_list,CRLF,TEMP_array
  ArraySort>TEMP_array
  Let>r=0
  Repeat>r
    Let>r=r+1
    Let>TEMP_row=TEMP_array_%r%
    ConCat>LIST_FOR_MDL,%CRLF%%TEMP_row%
  Until>r=TEMP_array_count
  Trim>LIST_FOR_MDL,LIST_FOR_MDL
  MDL>LIST_FOR_MDL
Until>k=2

/*
unsorted_list_1:
A
a
12
aaa
c
x
b
*/

/*
unsorted_list_2:
22
13
X
A
*/
In the second list you will have a "b" instead of "X" as the last entry.

Here's a fix for the issue you're facing and perhaps something that could be native inside the ArraySort command in the future?

Code: Select all

VBSTART
Function CreateGUID
  Dim TypeLib
  Set TypeLib = CreateObject("Scriptlet.TypeLib")
  CreateGUID = Mid(TypeLib.Guid, 2, 36)
End Function
VBEND
VBEVal>CreateGUID,UNIQE_VALUE_TO_IGNORE
Let>k=0
Repeat>k
  Let>LIST_FOR_MDL=
  Let>k=k+1
  LabelToVar>unsorted_list_%k%,TEMP_list
  Separate>TEMP_list,CRLF,TEMP_array
  ArraySort>TEMP_array
  Let>r=0
  Repeat>r
    Let>r=r+1
    Let>TEMP_row=TEMP_array_%r%
    IfNot>TEMP_row=UNIQE_VALUE_TO_IGNORE
      ConCat>LIST_FOR_MDL,%CRLF%%TEMP_row%
    Else>
      Add>TEMP_array_count,1
    Endif>
    Let>TEMP_array_%r%=UNIQE_VALUE_TO_IGNORE
  Until>r=TEMP_array_count
  Trim>LIST_FOR_MDL,LIST_FOR_MDL
  MDL>LIST_FOR_MDL
Until>k=2


/*
unsorted_list_1:
A
a
12
aaa
c
x
b
*/

/*
unsorted_list_2:
22
13
X
A
*/
Let>ME=%Script%

Running: 15.0.24
version history

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts