Text from conditions file converted into date format in output file

Builder
1

Hi,
I am having some problems with text contained in a conditions file being converted into date format in PsychoPy’s output csv file. I have tried finding solutions online, but haven’t found one yet that solved the problem. Any help here would be much appreciated!

OS: Win 10
PsychoPy version: 2022.2.5
Standard Standalone? (y/n) : y
What are you trying to achieve?:
I am using an Excel spreadsheet as a conditions file to define some text that should be displayed on screen during the experiment using a text component, as this changes trial by trial. This works all fine. But when I look at the output csv file that is automatically saved, the text from the original Excel file is converted into date format in the output file.

What did you try to make it work?:
In the conditions file itself, I have defined the column type as text and I have also added an apostrophe at the beginning of each cell to indicate that Excel should treat this as text. For example, one cell contains '2 - 3, or another one contains '7 - 9 - 1. During the experiment PsychoPy correctly displays that text as 2 - 3 or 7 - 9 - 1 on screen.
I have also tried converting the Excel file into a csv file beforehand that is then used during the experiment, but the result is the same (i.e. no difference in the output file whether the original conditions file is used as .xlsx or as a .csv in the experiment).

What specifically went wrong when you tried that?:
The csv output file converts the original 2 - 3 into date format, so it now reads 02-Mar (or 02/03/2023) and the 7 - 9 - 1 is turned into 07/09/2001. I can’t just convert it into text as the actual underlying value was changed (i.e. 44987 for 02/03 and 37141 for 07/09). The problem only exists when the cell contains two or three numbers, not when it contains four plus numbers (e.g., 2 - 5 - 1 - 9 - 6 is not converted).

Any advice on how to fix this would be really helpful!

Thank you,
Hanna

2

Hi @Hanna273 , I think this is related to how Excel interprets your CSV. You can change how Excel interprets this information by importing the CSV file into Excel, for example using these steps:

  1. Click on the “Data” tab in the Excel ribbon.
  2. Select “From Text/CSV” in the “Get & Transform Data” section.
  3. Select the CSV file you want to import and click “Import”.
  4. In the “Text Import Wizard”, select “Delimited” and click “Next”.
  5. Select the delimiter used in the CSV file (e.g. comma, tab) and click “Next”.
  6. For each column, select the data type you want to use (e.g. “Text”, “Date”) and click “Finish”.
3

Hi dvbridges, thanks so much for this!
I have always been doing steps 1-5, but never knew what to do at step six, but that’s done the trick!

I know this isn’t directly PsychoPy related anymore, but I hope you don’t mind a follow-up question. In some cells in column A, I have a whole list of values, mostly just commas (e.g. , return,17.482407200150192,0.0,0.0, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , LT_003,B,1.0,1.0,2023-11-08_10h33.31.830,TestRetest_V16,2022.2.5,60.00254407135899, ,) so it hasn’t separated it out properly would be my guess. I can figure out as to which columns some of those values should actually be in and could manually move them there, but my spreadsheet is rather large (it goes to column KA), so a neater way would be good. I have tried playing around with the different options in the text import wizard (i.e., whether it should treat consecutive delimiters as one etc), but that affects a lot of other columns then too and I couldn’t find any way to fix that. If you have any suggestion of how to go about it, that would be great!
Thanks!