How to split multiple values from string text and calculate them
For text files with mixed alphanumeric values, they can be copied into excel and then disassembled, but the formula is too complex for ordinary people to master. This problem can also be solved with VBA, Java, Python and other development languages, but the code is tedious, and it is more difficult to use the hard to understand regular expression.
It would have been much simpler if you had used esProc. For example, one line of the text file is "Jeff spent 13.5$ for cola, 5.2$ for coat, and 21$ for toys". To sum all the values in the file, the esProc code can be written as follows:
A |
B |
|
1 |
=file("data.txt").read() |
/ Read the text to form a large string |
2 |
=A1.word@d() |
/ Disassemble all values, including decimals |
3 |
=A2.sum() |
/Sum |
If we do not sum the whole file, but sum the lines, the code is also very simple:
A |
B |
|
1 |
=file("data.txt").import@i() |
/ Read text, one string per line |
2 |
=A1.new(~.words@s()(1):name,~.words@d():numbers) |
/ Split by row to form two columns. The name column stores the first (name) of the word in the row, and the numbers column stores the value |
3 |
=A2.derive(numbers.sum()) |
/ Add a new column to sum the numbers |
esProc is a scripting language, which can be executed repeatedly in IDE, and can be called in command line mode.
Other similar file calculation examples: CSV File Comparison CSV File Comparison ]
It’s simple to deal with Excel： An Easy Way to Merge and Summarize Excel Files An Easy Way to Merge and Summarize Excel Files ]
Here are more examples of text calculation, covering the common operations of structured data: Structured Text Computing with esProc
esProc is a professional file processing script. It is easy to install and use, easy to debug, and rich in class libraries. For details, please refer to: http://www.raqsoft.com/html/file-processor.html
Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
