topic Re: probleme import date excel in Discussions

probleme import date excel

Françoise — Fri, 03 Oct 2025 07:05:52 GMT

bonjour,

je n'arrive pas à avoir le bon format dans JMP (d/m/y) comme dans excel et après plusieurs essais, j'ai toujours dans JMP: m/d/y.

merci pour votre aide

cordialement

Re: probleme import date excel

hcarr01 — Fri, 03 Oct 2025 09:08:22 GMT

Maybe you can use the following script :

Names default to here(1);
clear log();
clear symbols();
clear globals();

path = use your correct path... 

// Importer un fichier Excel : essai date.xlsx
// → Data Table( "Feuil1" )
Open(
	path,
	Worksheets( "Feuil1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 4 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 5 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 0 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" ),
		Column Numeric Format(
			Index( 0 ),
			Column Name( "Datectrl + ;"n ),
			Datatype( "Character" )
		),
		Column Numeric Format( Index( 7 ), Format( "Best", 9 ) ),
		Column Numeric Format(
			Index( 11 ),
			Format( "Best", Use thousands separator( 1 ), 9 )
		),
		Column Numeric Format(
			Index( 12 ),
			Format( "Best", Use thousands separator( 1 ), 9 )
		),
		Column Numeric Format( Index( 13 ), Datatype( "Character" ) ),
		Column Numeric Format( Index( 14 ), Format( "d/m/y", 10 ) ),
		Column Numeric Format( Index( 15 ), Datatype( "Character" ) )
	)
);


// Modifier les informations de colonne : Datectrl + ;
Data Table( "Feuil1" ):"Datectrl + ;"n <<
Set Data Type(
	Numeric,
	Format( "Format Pattern", "<DD>/<MM>/<YYYY>", 10 ),
	Input Format( "Format Pattern", "<DD>/<MM>/<YYYY>" )
) << Set Modeling Type( "Continuous" ) << Set Field Width( 12 );

Re: probleme import date excel

hogi — Fri, 03 Oct 2025 10:21:57 GMT

surprising, which version of JMP?

I tried it with JMP18 and JMP19 and without adjusting a setting I got:

Maybe a wrong setting in the (system) preferences?

Re: probleme import date excel

jthi — Fri, 03 Oct 2025 11:11:48 GMT

Disable "Limit column type detection" so you get your data as character to your JMP table in this case (from this you can more easily set the format as you wish).

Then you can remove extra rows (could be done in import just make sure you still have character data). From this you can start then fixing the format

Generally, Format Pattern is the best way of formatting your character data into dates.

After the data has been correctly formatted into JMP number, you can then change the format as you want to

Re: probleme import date excel

Françoise — Fri, 03 Oct 2025 11:30:24 GMT

merci à tous pour vos solutions.

j'ai appliqué le script d'importation hcarr01 et c'est OK !

bon week-end

cordialement