Dynamically Parse Expressions That Contain Cell Variables

Title: Python and CSV with formulae

Source: https://stackoverflow.com/questions/64187587/python-and-csv-with-formulae

Question

I have a CSV file with formulae like this:

1;;2.74;0

=A1+C1;=A2;=C1

What’s the best way to convert formulae into numbers? The expected result is as follows:

1;;2.74;0

3.74;3.74;2.74

The only way I know is to read it with csv.reader as a list of lists and then loop through each element. But it seems that there must be a simpler way.

P. S. I think eval is helpful.

 

Answer

Two points about the task: one is the dynamic parsing of string expressions; the other is that there are variables in the expressions that look like Excel cell names. You’ll finish the computation quickly if you paste the csv file to Excel. Or you can do it with VBA. But if you haven’t Excel close at hand, you need another scripting language.

Cells are not unique Excel style. They are used by esProc SPL, too. SPL supports cell variables, as well as computing expressions dynamically. It is suitable for handling such computations. For your question, I have the following SPL script:

A

B

C

D

E

1

/ Set aside the first two   rows

2

3

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

4

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

/ Get values for row 1

5

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

/ For row 2

6

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

/ The result

7

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

SPL is good at handling operations on xls or csv files. Find more examples and learn more about SPL in http://www.raqsoft.com/p/script-over-csv-xls.