cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
nikles
Level VI

Import csv with multiple time formats?

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

1 ACCEPTED SOLUTION

Accepted Solutions
mmarchandTSI
Level V

Re: Import csv with multiple time formats?

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.

 

mmarchandTSI_0-1710435734167.png

 

View solution in original post

3 REPLIES 3
mmarchandTSI
Level V

Re: Import csv with multiple time formats?

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.

 

mmarchandTSI_0-1710435734167.png

 

nikles
Level VI

Re: Import csv with multiple time formats?

Thanks @mmarchandTSI .  Elegant and easy solution.

jthi
Super User

Re: Import csv with multiple time formats?

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.

-Jarmo