003 Dynamically Parse Expressions That Contain Cell Variables
Title: Python and CSV with formulae
Source: https://stackoverflow.com/questions/64187587/pythonandcsvwithformulae
【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/scriptovercsvxls.