topic Re: Import csv with multiple time formats? in Discussions

Import csv with multiple time formats?

nikles — Thu, 14 Mar 2024 16:39:50 GMT

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

Re: Import csv with multiple time formats?

mmarchandTSI — Thu, 14 Mar 2024 17:02:20 GMT

Try this. Import :Date as a character column and create a new column :Standardized Date.

New Column( "Standardized Date",
	Numeric,
	"Continuous",
	Format( "m/d/y h:m:s", 23, 0 ),
	Formula( Num( :Date ) )
);

As long as the :Date values are all valid, this works great.

Re: Import csv with multiple time formats?

jthi — Thu, 14 Mar 2024 17:03:25 GMT

I would import that column as text and then write a formula which is able to convert all the different date formats to datenum. You can then change the format of that formula column to present date/time.

Re: Import csv with multiple time formats?

nikles — Thu, 14 Mar 2024 18:02:07 GMT

Thanks @mmarchandTSI . Elegant and easy solution.