...

Execute Excel-like Formulas outside Excel

 
excel(99)

The content of data.csv contains Excel-like formulas:  

1;;2.74;0

=A1+C1;=A2;=C1

The computing goal is to process this CSV file outside Excel (not using Excel). Execute the above formula according to Excel rules to get the following result:

1;;2.74;0

3.74;3.74;2.74

This requires dynamic parsing of a string formula that contains Excel-like variables. It will be fast and simple to get this done if you can paste the file to Excel. But it becomes rather difficult to try to do this outside Excel.

You can do this in 2 steps:

1. Start esProc (Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time).

2. Write a script and execute it.

A

B

C

D

E

1

/ Set aside two rows for later   use

2

3

=file("D:\data.csv").import(;,";")

4

=A3(1).array().(eval(char(64+#)   / "1=" /  if(~==null,"null",~)))

/ Process data in the first   row

5

=A3(2).array().(eval(char(64+#)   / "2" /  if(~==null,"=null",~)))

/Process the second row

6

=create(A,B,C,D).record([A1:D2])

/Get the result

7

=file("D:\result.csv").export(A6;";")

In this script, eval() function can identify an Excel-like formula and execute the string as a formula.


excel(99)
 
 •  354 View  •