Best default date format in CSV for SPSS

[jaddington]

I support an application that exports data to CSV of which the clients then import to Excel, SAS, and/or SPSS for analysis.  I'm trying to help support a group using SPSS and determining if there is anything I can do to facilitate their imports.

The application could support exporting the date variables using different string formats but currently it is doing dd-mmm-yyyy.  This is because we have both US and CDN clients and they are use to dd-mm or mm-dd depending on the country.  Some of them have different local PC language formats so it is a benefit to having the short month code in there.

Just another FYI is that all strings are also wrapped in " ".  I was hoping there was a way to let SPSS know if it isn't a number, and it isn't in double quotes, it must be a date or a datetime.  I'm currently just working on date values, the timestamp have been removed by request by there are some true datetimes in other dataf iles.

Anyhow it seems the default CSV import (an I tried the free trial wizard v22) picks it up as a string.  I can select datetime and it then defaults to dd-mmm-yyyy.  This is okay for one date variable in a file but obviously a pain for many.  I realize one can export the import template and then probably fairly quickly copy/paste the Date11 formatting next to each variable and be done.  It maybe that this is the best practice and this is a mute point.

I'm looking/hoping for a couple of CSV best practices (love to see a link) /w respect to prepping for SPSS.  Switching to a fixed-width format is not acceptable as the dataset is allowed some flexibility.

Thanks

jack

[Bruce Weaver]

Hi Jack.  Date variables can indeed be problematic when importing data -- e.g., see the "Importing Data" section here:  https://sites.google.com/a/lakeheadu.ca/bweaver/Home/statistics/spss/my-spss-page.  

One option you might consider is having the DD, MM and YYYY values stored as separate numeric variables in the CSV file.  End users can compute a true date variable if and when necessary.  

HTH.

jaddington wrote

I support an application that exports data to CSV of which the clients then import to Excel, SAS, and/or SPSS for analysis.  I'm trying to help support a group using SPSS and determining if there is anything I can do to facilitate their imports.

The application could support exporting the date variables using different string formats but currently it is doing dd-mmm-yyyy.  This is because we have both US and CDN clients and they are use to dd-mm or mm-dd depending on the country.  Some of them have different local PC language formats so it is a benefit to having the short month code in there.

Just another FYI is that all strings are also wrapped in " ".  I was hoping there was a way to let SPSS know if it isn't a number, and it isn't in double quotes, it must be a date or a datetime.  I'm currently just working on date values, the timestamp have been removed by request by there are some true datetimes in other dataf iles.

Anyhow it seems the default CSV import (an I tried the free trial wizard v22) picks it up as a string.  I can select datetime and it then defaults to dd-mmm-yyyy.  This is okay for one date variable in a file but obviously a pain for many.  I realize one can export the import template and then probably fairly quickly copy/paste the Date11 formatting next to each variable and be done.  It maybe that this is the best practice and this is a mute point.

I'm looking/hoping for a couple of CSV best practices (love to see a link) /w respect to prepping for SPSS.  Switching to a fixed-width format is not acceptable as the dataset is allowed some flexibility.

Thanks

jack