Puck Puck - 4 months ago 18
Bash Question

Scripting libreoffice calc formula into csv

I have a bash script that writes some data extracted from raw log files into a file in csv format. Now I want to apply libreoffice calc formulas on this dataset. My idea is to write "raw" calc formula in the csv file directly from the bash script (using ';' [semicolon] instead of ',' [comma] to separate data to avoid breaking formulas). So I have a script like that (for example):

#!/bin/bash
for (( i=1; i<=5; i++ ))
do
echo "$i; $((i+1)); =SUM(A$i, B$i)" >> sum.csv
done


Executing this script gives this
sum.csv
file:

1; 2; =SUM(A1, B1)
2; 3; =SUM(A2, B2)
3; 4; =SUM(A3, B3)
4; 5; =SUM(A4, B4)
5; 6; =SUM(A5, B5)


When I open it with calc, it gives the expected result with each value separated in single cell. But the problem is that the formulas are not evaluated. Even manually editing the cell doesn't trigger an evaluation. The only thing working is to copy the formulas without '=' and manually writing '=', then pasting the formulas.

I tried using
INDIRECT()
but it didn't help.

Is there a way to force evaluation of formulas ? Or is there some other way to do what I want (without learning a new language...) ?

Answer

It should work after removing the leading space before the equals sign. The third field currently has the content =SUM(A1, B1) (notice the leading space). LO will recognize the formula if the content starts with = instead of =:

1;2;=SUM(A1, B1)
2;3;=SUM(A2, B2)
3;4;=SUM(A3, B3)
4;5;=SUM(A4, B4)
5;6;=SUM(A5, B5)
Comments