"The content of data.csv contains Excel-like formulas: 1;;2.74;0 =A1+C1;=A2;=C1 The comput .."

blackduckie RaqForum 28 No.
1 Reply • 491 View • 3 Years ago

Execute Excel-like Formulas outside Excel

excel(115)

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;";")