Start a new topic

DateTime Conversion to Date

I've got a .CSV file with dozens of columns for dates but they are in DateTime format: YYYY-MM-DD HH:MM:SS.  Has anyone come across this before and knows if IOM will convert to a date format during the import?  If not, does anyone have a regular expression that will do this?

Art,

Here you go.

Replacement value: $1/1/$2
Value to match on: (^\d+)/(\d{4}$)
Does anyone have a suggestion for a regex to convert MM/YYYY to MM/1/YYYY?
I made a slight improvement to this in order to be able to amend all dates regardless of year.

(?
This replaces anything after a 4 digit number (i.e. yyyy) with a blank. This helped me change a column of birthdays with a date/time format without having to reformat in excel each time.
This is perfect - thanks!
And editing the Excel document was a pain (even though I still had to open in order to save as .CSV) because there were around 15 columns that needed to be converted.
because it is very bad practice to manually go into your data file and make modifications to it. Especially if this is a file format you are exporting from another system and importing into yours and you have to do this daily. Much better to let IOM take care of the switch.
Login or Signup to post a comment