Format of the response times

gerhono

Hello,

What is the precise format of the timestamps that are logged into the Excel csv file of an experiment for a given subject ? For instance, this value : 13566083000041500 is automatically formated by Excel into the following : 13.566.083.000.041.500.

Thank you in advance for your help.

Gérard

cvanbuck

Hello @gerhono,

Do you happen to know which variable is generating this number? Normally, OpenSesame logs all timestamps in milliseconds, so this value looks unusually large. Are all of your timestamps that size, or just this one?

Regarding the Excel formatting: yes, Excel can sometimes automatically reformat large numbers in odd ways. You might want to adjust the import settings or cell formatting to ensure the value is treated as a plain number and not altered.

Claire

gerhono

Hello Claire,

Based on your remarks, I confirm that the problem originates from the formatting that is made by Excel on the value that is passed by OpenSesame. The value that appears in the variable inspector is correct, with only one dot after the milliseconds value. So, no confusion about how to interpret that value in the inspector.

But the Excel file itself is generated by OpenSesame, and so it is not possible to fix a given format for the columns in advance. Is it possible to force OpenSesame to generate a flat text file in csv format ?

Best regards,

Gérard

gerhono

Hello Claire,

I have been struggling during days and days with the question of treating the data generated by OpenSesame in VBA inside of an Excel Workbook, thinking that it would be a convenient way to process the data generated by OpenSesame into an Excel file with a .csv extension. It is not a good idea because Excel hidden operations interfere in a very obscure way with the code. What is the justification of exporting the data into an Excel file that is given a .csv extension ? Is it supposed to facilitate additional operations on the data ? From my experience, it is just the converse because that format is very confusing.

In my search for a better control of each step of operation on the data generated by OpenSesame, my first concern is to control the format of this data from the inside, that's to say how it is produced by OpenSesame. So I come back to my initial question: is it possible to change the output format of the data in a way or another ? For instance print it out into a real .csv file from an inline script ? Or force OpenSesame to do so ?

I am myself surprised by the fact that I am facing this problem because it seems so fundamental.

Thank you in advance for shedding some light on this question and have a nice day.

Gérard

cvanbuck

Hi @gerhono

To clarify: OpenSesame does not export Excel files disguised as .csv files... The logger creates a standard, plain-text CSV file (comma-separated, UTF-8 encoded, double-quoted; see documentation). Excel might misinterpret the format when you open the file, but the file itself is correct, it’s not an .xlsx file or formatted in any special "Excel way".

To avoid Excel making unwanted changes (like reformatting large numbers, timestamps, or trimming zeros), I recommend:

1. Open Excel first (don’t double-click the .csv file),
2. Import your .csv file from the menu,
3. Select UTF-8 as encoding, choose comma as separator,
4. Set all columns to "Text" during the import process to prevent Excel from auto-formatting values.

I personally use LibreOffice, so I’m not entirely sure how Excel behaves, but you should be able to access a configuration window like this:

If that window doesn’t appear when you open your file, a common workaround is to change the file extension from .csv to .txt, as this usually forces Excel to display the import options dialog, where you can set the encoding and column formats manually.

If you want full control over the output format, yes, it’s also possible to skip OpenSesame’s built-in logger and write your own .csv via an inline_script using Python’s csv module.

But again, the .csv file produced by OpenSesame is likely fine; the problem is probably how Excel is reading and reinterpreting it.

Hope this helps!

Claire

gerhono

Hello Claire,

Thank you for your reply. The fact is that after executing an experiment with OpenSesame in Windows, the only file that is visible is a subject-xxx.csv file IN EXCEL FORMAT ! It means that Excel automatically "captures" the csv file generated by OS. This is the cause of the misunderstanding between you and me, from the beginning. So my concern now is to find a way to prevent Excel from behaving that way. I will let you know.

Cheers,

Gérard

gerhono

Hello again,

The options of conversion available in Excel have no effect in this specific cas of a csv file generated by another program. Modifying those options have unexpected effects on the csv files generated by OS. For instance, with the default options in Excel, no data is captured when OS is executed in "fast execution mode", but with other options in Excel, the data appear in the Excel.csv file. In "full screen" execution of OS, with a named subject, the data is captured automatically. So there is a mismatch during that phase of importation of the data from OS to Excel and it is not possible to sort it out without having access to the system level of Excel.

In my opinion, a simple solution would be in OS to have an option for generating a data text file with a specific extension unknown by Excel and a standard format of csv file. The extension of the file is probably the key that allows Excel to make the automatic conversion. I think that a .txt extension would not be convenient as well, because Excel also imports this kind of file in its own way. It would be easy for me to convert the file generated by OS with whatever extension into a standard csv file and import it in a controlled way into Excel.

What is your opinion ? Should Sebastiaan be informed of this problem ?

Thank you in advance,

Gérard

cvanbuck

Hi @gerhono,

Yes, I think there’s a bit of confusion here: .csv is the file format. It’s not a .csv file "in Excel format", but rather a comma-separated values text file format i.e., it is already a "standard csv file". The reason it might look like an Excel file on your computer is likely due to your system settings (or "Preferences").

You can change that behavior by right-clicking the file, choosing "Open with", and selecting a different application (like Notepad or WordPad). If you check "Always open with this app", it should stop opening automatically in Excel.

Now if you do want to open it in Excel after doing this, it’s better to open Excel first, then go to Data → Get Data → From Text/CSV (or use the Text Import Wizard) so you can control how the file is interpreted; especially the encoding and delimiters. Or you can try with LibreOffice Calc.

I’ll tag @sebastiaan too, in case there’s another angle or clarification he can provide.

Claire

gerhono

Hello Claire,

You are absolutely right, I should have thought of it !

I am sorry for my lack of insight and the time you had to spend on this matter ...

You deserve a big coffee, how can I send it to you ?

Gérard