Hi. I'm wondering if there's a way in JMP to import a csv whose datetime column contains multiple formats? Or alternatively, does anyone have a clever script to do this?
We have vendors that supply us data as csv files. They are not always careful about following a consistent datetime format. The problem occurs when this format changes within a single csv. For instance, rows 1-1546 might appear in the format "m/d/yy hh:mm", and rows 1547-3457 might appear in the format "yyyy/mm/dd hh:mm:ss'.
I know I can import the csv and force the datetime column to remain as text, either using the preview editor, or using Multiple File Import. But ultimately I need to convert this column to numeric, and I can't find smooth way to do this that doesn't result in some rows having missing values.
I've considered splitting the data file into multiple parts based on the format, then manually converting each to the correct format, but this is too time-intensive.
Anyone have any ideas, or a script I can borrow? Thanks!
-Stefan
My Details:
JMP Pro 17.2.0
Mac OS Ventura 13.6.4