Array loading with ASCII File Process vs Split

[Cory]

For one of my clients we have a repository of CSV files which are used in most of the macros by all the users. Recently I discovered that MEP is locking these files when the ASCII File Process command is running. This caused other user’s macros to fail because the file was not available. I made a feature request or bugrep some time ago not to have MEP lock the file but as an aside I did a little testing out of curiosity and thought I would share the results.

 

First let me give one bit of advice. Many of my macros I’ve changed so that instead of doing a lot of actions in the ASCII File Process loop I only load the arrays in the loop. This minimizes the time the file is locked. However it does increase the amount of code.

 

There are two essential approaches I’m evaluating. One is to use ASCII File Process to load a couple of arrays from a 250k CSV file. The other is to load the entire file contents into a text variable then split the array on the EoL (CRLF) and then do a repeat process to split those lines by their delimiter into a temp array. Then take two of those and load the arrays. A lot more code than ASCII File Process to be sure but not too crazy.

 

220mS average ASCII File Process on a local file with no array loading ops

240mS average ASCII File Process on a network file with no array loading ops

292mS average ASCII File Process on a local file with array loading ops

004mS average to set a variable from the local file

008mS average to set a variable from the network file

224mS average to set a variable from the local file and perform array loading ops

 

My conclusion is that one can perform the same array loading task using the split method and it’s slightly faster. But more importantly the part of the macro run time that accesses the file is only a few milliseconds and after some more testing I determined it does not lock the file! Also if one wants to do some extra operations within the splitting process one can and not affect other users or performance and avoids another repeat. But if there is no possibility of the files being read only in your environment it would be simpler to use ASCII File Process method. Of course it would be really cool if MEP simply did not lock the files when doing ASCII FP but until they have a chance to address that I’ll have to deal with code bloat.

[redwards]

You could copy the file to a temp folder on the local hard drive, then do your processing. When your done, delete the file.

 

This assumes your shared CSV files are static.

[Cory]

You could copy the file to a temp folder on the local hard drive, then do your processing. When your done, delete the file.

 

This assumes your shared CSV files are static.

Thanks for the suggestion, it had crossed my mind but I didn't want the added delay.