Error reading the .xlsx file

gerhono

Hello,

This problem is close to the previous one (see "Refresh the source of a loop" - june 30). In this case, I am sure that the .xlsx file is the one that is present in the pool and I took all precautions that I learned from the previous case (delete and replace the Excel fil in the pool and re-select it in the loop item).
This experiment is a copy of another one that runs fine. I saved it under another name and made all the changes needed for a new version. Other parts of the program are OK, but the flow is stuck on the main loop with the following message, which is the same when I execute the program or when I just test the preview of the loop:

Preview de la table loop

Impossible de générer le preview.
Failed to read .xlsx file: c:\users\grard~1\appdata\local\temp\tmppd_eqf.opensesame_pool\Alcool - perception 2.xlsx
item-stack: 
exception type: ValueError
exception message: Not all columns have a name on the first row
time: Tue Jul 24 07:12:30 2018
Traceback:
  File "C:\Program Files (x86)\OpenSesame\lib\site-packages\libopensesame\loop.py", line 370, in _read_file
    return io.readxlsx(src)
  File "C:\Program Files (x86)\OpenSesame\lib\site-packages\datamatrix\io\_excel.py", line 64, in readxlsx
    raise ValueError(u'Not all columns have a name on the first row')
ValueError: Not all columns have a name on the first row

This message is inconsistent with the contents of the file, because the first row of each column actually contains a name. And the file is the good one. So what ?

Have a nice day and thank you in advance for your advise.
Gérard

sebastiaan

Hi Gérard,

An Excel file can have multiple sheets, and OpenSesame tries to read the one that is active, which is not necessarily the first one. Could that be the issue? If you switch to the sheet that you want to use, and then save the Excel file again, does the issue go away?

If not, could you upload the .xlsx here so that I can take a look?

Cheers!
Sebastiaan

gerhono

Hi Sebastiaan,
There is only one sheet in the file, that I have attached.

sebastiaan

Ah, I see. Columns D, E, and F don't contain any real values, but they do count as empty columns because they have formatting. And therefore OpenSesame complains that they don't have a column name. Do you see what I mean?

Clearing the formatting of all columns except the ones with actual data should resolve the issue.

gerhono

You are right ! And it looks like resetting the cell format to 'standard', which is the default value, is not sufficient. When the 'format' parameter has been modified once, the cell is considered 'formatted'. Hence, it is safer to copy the columns into a brand new sheet.
Thanks a lot.