psychopy.org | Reference | Downloads | Github

Importconditions() reads formulas not values

I’ve checked the docs and there doesn’t seem to be a way to get importconditions() to read the values rather than the formulas when reading in the loop conditions from the Excel file.

Have I missed something ? ( as it would be useful if this were possible )

I for one would think that importing values and not formulas would be ideal. I’m out and about and can’t test it, but I suspect this could be quickly achieved in the source code by adding an option to the loadWorkbook() call in data.py.

On github on line 2670 of psychopy/psychopy/data.py , in the importConditions() method, we have this line:

wb = load_workbook(filename=fileName)

If the last poster on this question is right, it could just be changed to this:

wb = load_workbook(filename=fileName, data_only=True)

… and openpyxl won’t read in the formulas.

I wonder what the developers think about adding this change to the next version of psychopy if it works?

If you, @jacanterbury wanted to test it, I think you can edit (carefully!, maybe save a copy somewhere) the source code yourself on your machine. I don’t know what your operating system is, and I’m not sure if this varies depending on how you installed psychopy, but if it’s similar to my setup, the psychopy source is in a folder under python2.7/dist-packages/psychopy/data.py . You just have to find out where python2.7 is on your system. I think running “python -v” in the terminal will print out all of the folders that python uses when it runs.

Or you could just export your spreadsheet to csv, or make a duplicate spreadsheet, copy the column with the formulas, and “paste special” them back in with only the values. Of course you would have to do this every time you changed your spreadsheet, so ya know.

Later!

1 Like

Well found @daniel.riggs1

@jacanterbury if it works for you then could you let us know and we’ll add that back into psychopy.data (so you won’t have to do it every time). And you could tick dan’s post as the solution. :slight_smile:

Thanks @daniel.riggs1 & @jon for your replies.

I’ve delved further and preliminary findings are:-

  1. On Windows 8.1 x64, cell formulae in Excel files are (AFAIK always) correctly interpreted
  2. On Mac (with Yosemite 10.10.5) and with Office 2016, formulae are sometimes not correctly handled
    e.g.
    on a simple text component, if the ‘Text’ box is set to ‘set every repeat’ and the formulae =b2&c2 (then dragged down) then =b2&c2… and =b3&c3… causes the formula to be displayed (ie “=B2&C2”) but B4 onwards just display as “=” the equals sign only (never the derived value)

Also, again for a Time component, if the Start/Stop field values are entered as $colname then this works fine only if the cell contains a numeric value but it crashes if its a fomula (eg =E2/F2) (but this works fine on Windows)

So, take care if you’re running on a Mac and don’t stick any formulae in your Excel cells.

So just to be clear, is this the behavior after making the suggested change in the source code, or is this extra information about the behavior without the change to the code?

While we wait for @jacanterbury 's response, I tested the code change on my computer, and it worked. That is, before adding data_only=True to the source code, it displayed the formulas and not the values. After the change, it displayed the values.

Two potentially important notes: I’m on Ubuntu 14.04, and created the xlsx file with LibreOffice. Hopefully the behavior will be the same for different operating systems and different versions of Microsoft Office, hopefully someone will test this on their systems. If it doesn’t work, this would be a problem not with psychopy but with openpyxl, and we would want to ask them to fix it.

@jon, would you prefer I open an issue on GitHub for this change or is leaving this thread here fine?

formulaTest.psyexp (4.8 KB)
formulaTest.xlsx (4.6 KB)
formulaTest_lastrun.py (7.1 KB)

@dan since it sounds like you’ve worked out the fix I’d suggest you simply fix it and supply a pull request. It might have to wait for 1.84.2 (I’ve been building a quick bugfix 1.84.1 today and don’t want to start again for this).

More generally (i.e. where the solution isn’t so quick to implement) I think a github issue is best once we’ve agreed that a change should happen - it will get lost in the deluge of things here.

thanks for your help

Done. Thanks for all your work, @jon .

apols for tardy reply. Those findings were with the shipped latest versions with NO extra mods to the code base.

John