0

Is it possible to manipulate data in LibreOffice calc (spreadsheet) using terminal commands. I have a spread sheet with loads of data in csv format and i want to do data manuplation from terminal without opening libreoffice.

the csv contains 5 columns

columns 
A   B   C   D   E

in which i want to delete data in B, C and E column then copy data from D column to the B column.

Then in the empty C column at C5 location I want to add this equation =(B5*($B1/$A1)) and from C6 on wards till the end of data (C6:Cn) I want to run this code =((A6+B6)*C5)

Can these kind of manipulation can be done using only terminal commands?

This is the csv file

  • They can, but it's not going to be easy or clean. Can you use Calc's macro language instead or other tools like loading it into mySQL and exporting it again? – coteyr Jun 19 '15 at 10:39
  • Also have a look at awk/gawk it was basically made for this kind of thing. – coteyr Jun 19 '15 at 10:40
4

The easiest way is to save the file from libreoffice in a standardised CSV format, usually with "," as delimiter and quoting text in ("), and saving formulas as such. Your data should look like this:

7,444,5555,99,"bcdef"
22,444,5555,99,"bcdef"

You can then manipulate this with a simple awk command (for example). You can include formulas in the fields by beginning them with "=". For example,

5,99,"=(B5*(B1/A1))"

You then read this .csv file back in libreoffice. Normally it should preserve the formulas.

Here's an example awk script to manipulate a file example.csv into file new.csv replacing the columns as you specified, with Cn having the formula ((An+Bn)*C5).

awk -F, <example.csv >new.csv '
NR<=4 { printf "%s,%s\n",$1,$4 }
NR==5 { printf "%s,%s,\"%s\"\n",$1,$4,"=(B5*(B1/A1))" }
NR>=6 { printf "%s,%s,\"=((A%d+B%d)*C%d)\"\n",$1,$4,NR,NR,5; }
'

You can convert the new.csv to .ods with:

libreoffice --convert-to ods new.csv

If your data contains "," and (") you need to change the delimiters used everywhere.

(To save cells with formulas as csv do: Save As, File type Text CSV, tick Edit Filter Settings, Save, Field options: tick Save cell formulas, tick Quote all text cells.)


I looked at your example data.csv file and in my discussion copied it to example.csv. I've removed line 1 "A,B,C,D,E" (the column headers) as this makes it harder to explain the correspondance of line numbers in the file to rows in the table. I did this with this sed command:

sed -i '1{/A,B,C/d}' example.csv

You can restore the header at the end if you need it with:

sed -i '1i\A,B,C' new.csv

If you want to actually do the formula arithmetic outside libreoffice you can do this in awk as follows. awk reads the data one row at a time and executes each command in {}. $1 gets set to column 1 and so on (as -F, means columns are separated by ","). We can save these values in an array indexed by the row number, eg A[NR]=$1 saves column 1 (column A) of row NR (current line) in array A. C[NR] can be set to the resulting calculation. We can then use these again in later lines:

awk -F, <example.csv >new.csv '
      { A[NR]=$1; B[NR]=$4 }
NR<=4 { printf "%s,%s\n",A[NR],B[NR] }
NR==5 { C[NR] = B[5]*(B[1]/A[1]); printf "%s,%s,%s\n",A[NR],B[NR],C[NR] }
NR>=6 { C[NR] = (A[NR]+B[NR])*C[5]; printf "%s,%s,%s\n",A[NR],B[NR],C[NR] }
'
  • I have edited the question and added the csv file download link to it – Eka Jun 19 '15 at 17:33
  • Sorry, I cant download the link. Edit your question to describe your problem further. – meuh Jun 19 '15 at 17:46
  • I have re uploaded in a different site. Now you will be able to download – Eka Jun 20 '15 at 4:30
  • 1
    Thanks for the data file. I've fixed and extended my answer. Still not clear on whether =((A6+B6)*C5) should change on each row, and if so how. – meuh Jun 20 '15 at 6:06
  • 1
    I've edited my answer to just leave the 2 answers with Cn=((An+Bn)*C5), both as a formula string, and doing the actual calculation in awk. When I read the resulting .csv file in libreoffice calc the result appears the same, apart from more floating point precision from the formulae. – meuh Jun 20 '15 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.