topic Re: change the date format during import in Discussions

change the date format during import

AnthonyS — Wed, 16 May 2018 11:32:44 GMT

hello, people!!

I have files with a date in european format (dd-mm-yyyy) and during the file import jmp converted automatically in american format (m/d/yy) .

is it possible to change the jmp data format?

Best regards,

AnthonyS

Re: change the date format during import

briancorcoran — Wed, 16 May 2018 18:09:49 GMT

Hi Anthony,

I think we will need more information on what file format you are importing, what version of JMP you are using and what operating system you are running on. You might have better luck contacting JMP Tech Support at support@jmp.com.

Regards,

Brian Corcoran

JMP Development

Re: change the date format during import

AnthonyS — Thu, 17 May 2018 18:09:01 GMT

My files are in format .txt , and I'm using JMP 14

This is a part of my script.

names default to here (1);
Clear Symbols();

filelist={}; //empty list

//ouvre une boite de dialogue retournant le chemin du fichier

prefilepath = Pick Directory( "Pick the Top Level Directory" );
If( Host is( Mac ),
filepath="/"||prefilepath,
filepath = Convert File Path( prefilepath, Windows )
);
prefilelist = Files In Directory( filepath, "recursive");
n2=nitems(prefilelist);


//Filtre sur les caractères spécifiées
For( i2 = 1, n2 >= i2, i2++,
file=(prefilelist[i2]);
If( Item( 2, prefilelist[i2], "." ) == "txt" | Item( 2, prefilelist[i2], "." ) == "csv" | Item( 2, prefilelist[i2], "." ) == "dat" & contains(prefilelist[i2],"LVL2") != 0,
Insert Into( filelist,file),
show(file)
)
);


//nombre d'objet dans filelist
nf=nitems(filelist);



For( iii = 1 , iii <= nf, iii++, //this starts the first loop
filenow = ( filelist[iii] );
fileopen=(filepath||filenow);
//dt=open(fileopen,private);
dt=open(fileopen, Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Tab, Other( ";" ), CSV( 0 ) ),//use ";" as a delimiter too
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels( 1 ),
Column Names Start(56),//starting on second row
Data Starts(57),//data starts on 3rd row
Lines To Read( "All" ),
Year Rule( "20xx" )
), private);//Import settings used in the open argument

New Column( "Source", Character, Nominal );
:Source << set each value( filenow );
//dt<<new column("Source", character, nominal)<<set each value(9999);
dt << Run Formulas();
//add the current table to the bottom of the combined data table
cctable << Concatenate( Data Table( dt ), Append to first table );
//don't use "Create Source Column" argument
Close( dt, NoSave );//after concatenating the table, close it and move on
);//end of the first for loop

Regards,
Anthony

Re: change the date format during import

Craige_Hales — Thu, 17 May 2018 17:49:56 GMT

JMP 14 can do this. Use the Preview Wizard to open the file the first time and specify the format you need.

Specify a d-m-y format for a column

After the file is opened, right-click the source script and edit it to see the script you need, something like this:

Open(
  "C:\Users\chales\Desktop\2col.txt",
  columns(
    New Column( "Col 2", Character, "Nominal" ),
    New Column( "Col 3", Numeric, "Continuous", Format( "d/m/y", 10 ), Input Format( "d/m/y" ) )
  ),
  Import Settings(
    End Of Line( CRLF, CR, LF ),
    End Of Field( Comma, CSV( 0 ) ),
    Strip Quotes( 1 ),
    Use Apostrophe as Quotation Mark( 0 ),
    Use Regional Settings( 0 ),
    Scan Whole File( 1 ),
    Treat empty columns as numeric( 0 ),
    CompressNumericColumns( 0 ),
    CompressCharacterColumns( 0 ),
    CompressAllowListCheck( 0 ),
    Labels( 1 ),
    Column Names Start( 1 ),
    Data Starts( 2 ),
    Lines To Read( "All" ),
    Year Rule( "20xx" )
  )
)

Text data imported with format