I have date data that I'm separating into three separate fields (all fields are string type, including the input field) by day, month, and year. Unfortunately, the date field is raw data and isn't consistent; there are eight variations for how the data in this field is organized (d/m/yy; d/m/yyyy; d/mm/yy; d/mm/yyyy; dd/m/yy; dd/m/yyyy; dd/mm/yy; dd/mm/yyyy). I figured out how to separate the day and the year into separate columns, but I'm having difficulty separating the month. I've tried the VB Script Mid() function by initially separating the different formats and performing the function, but that was extremely time-consuming, and I have close to 100,000 data points that I need to go through. Is there a python code that I can use, or a combination of different VB scripts, that can select the numbers between the slashes (/) for all of these? Or am I doomed?
In field calculator, you'll have to run 3 separate
Calculate Field operations:
In pseudo code:
day_field = !str_field!.split("/") month_field = !str_field!.split("/") year_field = !str_field!.split("/")
With cursors, this is a little simpler (and it should be faster) as it can be reduced to only iterating over
fields = [str_field, day_field, month_field, year_field] with arcpy.da.UpdateCursor(FC, fields) as rows: for row in rows: #only use the first 3 in case there is a 4th slash for some reason row[1:] = row.split("/")[0:3] rows.updateRow(row)
Here's what it looks like in Field Calculator: