cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
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
cwillden
Super User (Alumni)

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

9 REPLIES 9
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
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
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. 

cwillden
Super User (Alumni)

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
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!!!
mightymouse
Level I

Re: Importing Data to Jmp

Hello,

 

I'm looking for the same solution as the original poster!

 

I copied that script and modified it to suit my needs (including a suggestion above to copy and paste the import script from JMP itself instead of what the original solution presents.)

 

...But it does not work for some reason. 

 

The script does open the JMP file "Online Trend Data Aggregated" and then stops and does nothing. What is supposed to happen after that?

 

Debugger runs through the whole thing and the only message I get is in the upper right hand corner and it says "Program execution terminated"

 

Any insight is much appreciated. Script below:

 

// This script will concatenate cleaned online data files from APPLIKON controllers to a master table.

// Define function to open .xlsx files
DataUpdate = Function({filepath},
	Open(
		filepath,
	Worksheets( "Data" ),
	Use for all sheets( 0 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		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( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
)
);

//Define path to where .xlsx files are stored (location where this script is stored on a network drive)
path = get default directory();

//Open Running Table (Online Trend Data Aggregated.jmp) - assuming in same directory as .xlsx files (could change path to wherever)
dt_running = Open(path||"Online Trend Data Aggregated.jmp");

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

//Remove any files from the list that are not .xlsx or do not contain "APPLIKON" in file name
for(i=N Items(files),i>=1,i--,
	If(contains(files[i],".xlsx") == 0 | contains(files[i],"APPLIKON") == 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 = DataUpdate(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;

 

 

txnelson
Super User

Re: Importing Data to Jmp

When you just run the file, not using the Debugger, what statements are you getting in the log?
Jim
mightymouse
Level I

Re: Importing Data to Jmp

Thanks for the replytxnelson. This is what's in the log file for "Online Trend Data Aggregated". (This is the file that opens up when I run the script)

 

/*:
//:*/
// This script will concatenate cleaned online data files from APPLIKON controllers to a master table.

// Define function to open .xlsx files
DataUpdate = Function({filepath},
	Open(
		filepath,
	Worksheets( "Data" ),
	Use for all sheets( 0 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		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( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
)
);

//Define path to where .xlsx files are stored (location where this script is stored)
path = get default directory();

//Open Running Table (Online Trend Data Aggregated.jmp) - assuming in same directory as .xlsx files (could change path to wherever)
dt_running = Open(path||"Online Trend Data Aggregated.jmp");

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

//Remove any files from the list that are not .xlsx or do not contain "APPLIKON" in file name
for(i=N Items(files),i>=1,i--,
	If(contains(files[i],".xlsx") == 0 | contains(files[i],"APPLIKON") == 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 = DataUpdate(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;
/*:

Scriptable[]
cwillden
Super User (Alumni)

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