Best way to transfer date/datetime variables to SPSS

Hello,
What is the best way to transfer date and datetime (and time) variables, e.g. start/end or birthday, from KoBo Toolbox to SPSS, preferably using XLSX download?
As KoBo downloads all variables (except some internal ones) as text, standard SPSS import will type them (wrong) as text (A) type.

We only found some previous discussion here https://forum.getodk.org/t/internship-project-odk-briefcase-new-export-types/11754/4. (Flattening the files is not our interest.)

Have you tried this out:

Step 1:

  • Download the data in xls format
  • Open your xls dataset
  • Change the date column from General to Date
  • Similarly change the time column from General to Time
  • Save xls dataset

Step 2:

  • Open SPSS
  • Import xls dataset
  • You will be able to see the date variable as Date Format and time variable as Time Format

Dear @Kal_Lam,
Thanks. Did you try it, please? At least for numeric the Excel format change didn’t work, we had to use convert to text (per column).

We were hopeful to find something less manual (as these are IT tools? :wink:).
And we would need to do manual conversion also for all numeric KoBo variables downloaded (as text).

In addition there is the case of KoBo datetime types, like start/end, submission_time. How to best treat them, please?
Kind regards

@wroos, we have a features request for this (maybe this features request should solve your issue). And we also have a GitHub issue for this.

You could VOTE for this features request if you wish to see this in the upcoming days.

Thanks, @Kal_Lam, for the github link (2017/2018) and your prompt reply. Unfortunately, we start analysis now again, so cannot wait until the request (from 2017/2018) is getting activated.

We are looking for a best workaround, please, and hoping that the community has hints. Other SPSS users should have the same problem.

Hello @Kal_Lam,

Maybe we missed something. This step seems not working in Excel. Could you check it, please. Which (Excel) date format pattern (and locale) do you use?

We attach a typical export example, with 3 different date/time formats as provided by KoBo XLSX download.
Date_Export_01.xlsx (8.0 KB)

OK, maybe you could do a small workaround (it’s a bit manual but it should fulfill your requirement) as outlined below to address your current needs:

  • In your xls data create duplicate variable called today_e for today and submission_e for _submission_time as outlined in the attached xls file. You could keep them in text format.

    Reference dataset:
    Date_Export_01.xlsx (9.4 KB)
  • Now import this xls data to SPSS. You should see something like this in your SPSS:
  • Now run this syntax:
COMPUTE today_edited=number(today_e, DATE10).
VARIABLE LABELS today_edited 'Edited Date'.
VARIABLE LEVEL  today_edited (SCALE).
FORMATS today_edited (DATE9).
VARIABLE WIDTH  today_edited(9).
EXECUTE.

COMPUTE submission_edited=number(submission_e, DATETIME23.2).
VARIABLE LABELS submission_edited 'Submission Date & Time Edited'.
VARIABLE LEVEL  submission_edited (SCALE).
FORMATS submission_edited (DATETIME23.2).
VARIABLE WIDTH  submission_edited(22).
EXECUTE.

Thanks again, @Kal_Lam,
As far as we know this would only work BEFORE data submission, would need to do the same for each project and adapt at two places (KoBo and SPSS) for each date/time variable.
Therefore, if there is no other solution, we would go for similar adaption/recodes directly in SPSS (by script, or in Excel) only.
But we still hope for hints from the community.

1 Like