0

How can I do a conditional change in an .ods document? I have two columns. One of them stores a string and the second a value. I want to search the document with a particular string that I have, say "xyz". If this matches any of the strings that are shown in the first column, I would like a value of 1 to be deducted from the cell in the same row, but from the second column. The data in the .ods document are separated by the different adjoining cells (so a tab?)

As an example, consider the following:

xyz 23
xxy 42
xzz 76

If I have the string "xxy", I would like the bash script to update the .ods file such that it looks as so:

xyz 23
xxy 41
xzz 76

Now, the strings that I am searching for are stored in a seperate .txt file. I would like to iterate over all of the strings in the .txt file and repeatedly perform the described operation in the .ods file. There can be cases where the are multiple occurrences of the same string. Any helps with this?

Sid
  • 266
  • 5
  • 13
  • UNIX tools work on text files, not Excel files. You can export CSV from Excel and then use UNIX tools to manipulate the CSV and then import the result back into Excel, but you cannot manipulate Excel files directly with UNIX tools. – Ed Morton Jul 31 '16 at 01:07

1 Answers1

2

This should be a comment, but its a bit long

am searching for are stored in a text file.

No. A MS Excel files is not a text file. Its not even a file but rather an embedded filesystem where content is encapsuleted in OLE, or more recently as an xml tree. While there are both OLE and XML parsers available on Unix (I assume you want to run this on Linux/Unix/Posix since you've flagged this with bash, awk and sed) that just gets you access to where the data is stored. You still need a detailled understanding of the file format to be able to make changes. While it may be possible to do this in bash, it would be a lot easier in a dedicated programming language. Several do come with libraries for processing Excel files but vary in their support for file formats. Alternatively you could load it up in openoffice using its UNO API.

Community
  • 1
  • 1
symcbean
  • 47,736
  • 6
  • 59
  • 94
  • The strings that will be searching for are in a .txt file. I want to match these strings with those that can be found in the Excel file and perform the operation described. I have 2 seperate documents. One with a list of strings (text file) and another which attributes quantities to the strings (Excel file) – Sid Jul 30 '16 at 17:39