Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
NilD
Level II

Importing Data to Jmp

Hi,

 

I am developing a JMP analysis to periodically data. I have a excel macro that is generating a new CSV every week in a particular folder. I am still a very basic player with JMP, so wasnted community's support to help me build script to perform this activity. I did try to search around but didnt find any solution. The CSV file has 19 columns with header. I can eliminate header row from CSV if that presents problem. Can any one help with a code solution for script that helps me import file automaticlally to parent data table in JMP so I can run other scripts that can just evaluate data.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
cwillden
Super User

Re: Importing Data to Jmp

Here's an example of how you might do that.  Let's say the CSV file has 2 columns, X1 and X2 in it.  I create 2 sample csv files ("3-29-18 - My Data.csv" and "4-5-18 - My Data.csv").  I also have a running jmp table I saved in the same directory as the CSV files.

The following script will look in the directory for all files, filter out any non-CSV files and files without "My Data" in the file name.  It then grabs the most recent date of files already imported into the running table.  Then, it loops through the list of files meeting the previous criteria and extracts the dates from the file name.  If the date is greater than the max date from the running table, it puts that file in a list of files to append to the running table.

After that, it loops through the files we determined need to be appended by first opening them, then adding a File Date column, and then concatenating the file to the running table.

 

//Define function to open up your CSV files - will need to be modified for your own CSV file
MyCSVImporter = Function({filepath},
	Open(
		filepath,
		columns(
			New Column( "X1", Numeric, "Continuous", Format( "Best", 12 ) ),
			New Column( "X2", Numeric, "Continuous", Format( "Best", 12 ) )
		),
		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" )
		)
	)
);

//Define path to where csv files are stored
path = "$DOCUMENTS/temp/";

//Open Running Table (RunningTable.jmp) - assuming in same directory as CSV files (could change path to wherever)
dt_running = Open(path||"RunningTable.jmp");

//Get List of Files in Directory
files = Files In Directory(path);

//Remove any files from the list that are not .csv or do not contain "My Data" in file name
for(i=N Items(files),i>=1,i--,
	If(contains(files[i],".csv") == 0 | contains(files[i],"My Data") == 0, Remove From(files,i));
);

//Get max(File Date) in running table to determine which files need to be appended
max_date = max(dt_running:File Date << Get Values);

//Extract Dates from File Names and determine if date is > max_date from running table
dates = {};
update_files = {};
for(i=1, i<=N Items(files),i++,
	word_list = Words(files[i],"-"); //breaks out file name into words delimited by hyphens
	temp_date = informat(trim(word_list[1]||"/"||word_list[2]||"/"||word_list[3]),"m/d/y"); //first 3 words are month, day, year - concatenat them separated by "/"
if(temp_date > max_date, Insert Into(dates,temp_date); Insert Into(update_files,files[i]) ) );
//Import selected files, add File Date column, and concatenate to running table for(i=1,i<=N Items(update_files),i++, dt_temp = MyCSVImporter(path||update_files[i]); dt_temp << New Column("File Date", format("m/d/y"), Set Values(J(N Row(dt_temp),1,dates[i]))); dt_temp << Go To(:File Date) << Move Selected Column(To first); dt_running << Concatenate( dt_temp, Append to First Table(1)); Close(dt_temp,nosave); ); dt_running << Clear Column Selection;

In this example, RunningTable already has the 3-29-18 data in it, so it will only append the 4-5-18 data.

 

-- Cameron Willden

View solution in original post

6 REPLIES 6
Highlighted
uday_guntupalli
Level VIII

Re: Importing Data to Jmp

@NilD
     Will the files always be in the same format ? i.e. same column names and same type of data within them ? 

 

     The easiest way is to make JMP give you the script for importing the file - so you can customize the selections and reuse the script. 

 

    File ==> Open ==> Select the file you want 
    It will provide an interface like so. You might want to chose "Data with Preview" so you are aware of what you are importing and can customize. Once you are done, click on the Green triangle , it will provide an option to "edit" where you can see the script JMP generated 
image.png

Best
Uday
Highlighted
txnelson
Super User

Re: Importing Data to Jmp

Just a point of clarification on @uday_guntupalli response:

 

It will provide an interface like so. You might want to chose "Data with Preview" so you are aware of what you are importing and can customize. Once you have completed the importing, go to the imported data table.  Look in the Table Panel on the left of the data table display. Right click on the Green triangle in front of the "Source" item, it will provide an option to "edit" where you can see the script JMP generated.  You will then be able to cut and paste that script into the beginnings for the development of your weekly script.

Jim
Highlighted
NilD
Level II

Re: Importing Data to Jmp

Thanks! 

 

@uday_guntupalli yes data will always be in same format, same number of columns and same headers. 

 

I already have imported data into JMP once. The question is to get this data automatically updated weekly from new CSV file that will be generated. Every week, there will be a new CSV file generated with file name  "<date> - My Data". I am looking to have JMP import this data to my parent file every time. How can write command for JMP to automatically go to a folder directory and select latest file from the list and import that data and ammend it to running table. 

Highlighted
cwillden
Super User

Re: Importing Data to Jmp

Here's an example of how you might do that.  Let's say the CSV file has 2 columns, X1 and X2 in it.  I create 2 sample csv files ("3-29-18 - My Data.csv" and "4-5-18 - My Data.csv").  I also have a running jmp table I saved in the same directory as the CSV files.

The following script will look in the directory for all files, filter out any non-CSV files and files without "My Data" in the file name.  It then grabs the most recent date of files already imported into the running table.  Then, it loops through the list of files meeting the previous criteria and extracts the dates from the file name.  If the date is greater than the max date from the running table, it puts that file in a list of files to append to the running table.

After that, it loops through the files we determined need to be appended by first opening them, then adding a File Date column, and then concatenating the file to the running table.

 

//Define function to open up your CSV files - will need to be modified for your own CSV file
MyCSVImporter = Function({filepath},
	Open(
		filepath,
		columns(
			New Column( "X1", Numeric, "Continuous", Format( "Best", 12 ) ),
			New Column( "X2", Numeric, "Continuous", Format( "Best", 12 ) )
		),
		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" )
		)
	)
);

//Define path to where csv files are stored
path = "$DOCUMENTS/temp/";

//Open Running Table (RunningTable.jmp) - assuming in same directory as CSV files (could change path to wherever)
dt_running = Open(path||"RunningTable.jmp");

//Get List of Files in Directory
files = Files In Directory(path);

//Remove any files from the list that are not .csv or do not contain "My Data" in file name
for(i=N Items(files),i>=1,i--,
	If(contains(files[i],".csv") == 0 | contains(files[i],"My Data") == 0, Remove From(files,i));
);

//Get max(File Date) in running table to determine which files need to be appended
max_date = max(dt_running:File Date << Get Values);

//Extract Dates from File Names and determine if date is > max_date from running table
dates = {};
update_files = {};
for(i=1, i<=N Items(files),i++,
	word_list = Words(files[i],"-"); //breaks out file name into words delimited by hyphens
	temp_date = informat(trim(word_list[1]||"/"||word_list[2]||"/"||word_list[3]),"m/d/y"); //first 3 words are month, day, year - concatenat them separated by "/"
if(temp_date > max_date, Insert Into(dates,temp_date); Insert Into(update_files,files[i]) ) );
//Import selected files, add File Date column, and concatenate to running table for(i=1,i<=N Items(update_files),i++, dt_temp = MyCSVImporter(path||update_files[i]); dt_temp << New Column("File Date", format("m/d/y"), Set Values(J(N Row(dt_temp),1,dates[i]))); dt_temp << Go To(:File Date) << Move Selected Column(To first); dt_running << Concatenate( dt_temp, Append to First Table(1)); Close(dt_temp,nosave); ); dt_running << Clear Column Selection;

In this example, RunningTable already has the 3-29-18 data in it, so it will only append the 4-5-18 data.

 

-- Cameron Willden

View solution in original post

NilD
Level II

Re: Importing Data to Jmp

@cwillden

This worked like a charm, I really appreciate your support. This was able to handle hundreds of thousands of rows I have in unique CSV files within seconds, Thanks!!!
Highlighted
cwillden
Super User

Re: Importing Data to Jmp

Hi @NilD,

JMP will generate the script for you if you can import it manually the first time.  The resulting table will have a script "Source" that shows exactly how to import the data using JSL.

If JMP doesn't import the .csv file correctly, then do the following.  Do File > Open (or Ctrl + O) as usual (changing the file types in the Open Data File window to "Text Files" if needed) , navigate to the .csv file and select it.  Before opening the .csv, you should see some radio buttons giving you different options for opening the file.  One is "Data with Preview."  Selecting that will give you the opportunity to alter the import settings before bringing the data into JMP.

OpenCSV.PNG

 

-- Cameron Willden
Article Labels

    There are no labels assigned to this post.