Importing Data to Jmp

NilD

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.

cwillden

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 "/"<BR />
	if(temp_date > max_date,
		Insert Into(dates,temp_date);
		Insert Into(update_files,files[i])
	)
);
<BR />//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.

 

ExampleCSV&RunningTableFiles.zip