"Title: Python and CSV with formulae Source: [url] 【Question】 I have a CSV file with formulae lik .."

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

Dynamically Parse Expressions That Contain Cell Variables

excel(115)

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