topic Re: Merge CSV in JMP 13 in Discussions

Merge CSV in JMP 13

AT — Tue, 15 May 2018 16:49:46 GMT

Hi,

I like to merge few CSV files in a given directory. The files are similar format but also like to skip few lines after the headers and keep just the header for the first file. Once the files are merged, I can do the analysis on the merged file. I checked the JMP community, and did not find the best solution. I appreciate your help in providing me a script that do this. Thanks.

Re: Merge CSV in JMP 13

txnelson — Tue, 15 May 2018 17:29:28 GMT

The only way that I see to be able to do this is the following:

1. Read in each of the CSV files.

2. Delete the records after each of the headers that you do not want.

3. Change the names in data table 2 thru N to match the names in the the first data table you want to match to.

4. Concatinate the tables together.

5. Save the concatinated file back as a CSV file.

A pretty simple, but laborious, given the data table changes you may need to do.

Re: Merge CSV in JMP 13

AT — Tue, 15 May 2018 18:56:39 GMT

Thanks. I was able to merge csv now. Now I need to delete rows where for instance "Coulmn 2" is blank. I used the following and did not work:

dt = Current data table():

dt << select rows(:Column 2 == "");

It only selects one row not all of them that are blank. I am using JMP 13 for this. Please help. Thanks.

Re: Merge CSV in JMP 13

txnelson — Tue, 15 May 2018 19:06:29 GMT

Select Rows selects the rows by row number. What you need to use is

dt << Select Where( :Column 2 == "" );

Re: Merge CSV in JMP 13

AT — Tue, 15 May 2018 19:40:30 GMT

Thanks. That works. Now I wanted to go through each row of the table and delete where Column 2 = "ABC" except the first row.

I did something like this:

dt = Current data table();

N = N Rows (dt);

For (i= 2, i <= N, i++,
dt << select Where (:Column 2 == "ABC");
);

It selected all the rows where Column 2 = "ABC". I wanted to keep the first row not to be selected. Thanks for your continuous suppport.

Re: Merge CSV in JMP 13

pmroz — Tue, 15 May 2018 19:57:10 GMT

This code should do the trick. Uses GET ROWS WHERE instead of SELECT WHERE.

dt = New Table( "Test", Add Rows( 5 ),
	New Column( "Column 2", Character, "Nominal",
		Set Values( {"ABC", "ABC", "DEF", "DEF", "ABC"} )
	)
);
abc_rows = dt << get rows where(:Column 2 == "ABC");
nr = nrows(abc_rows); 
if (nr > 1,
	abc_new = abc_rows[2::nr];
	dt << delete rows(abc_new);
);

Re: Merge CSV in JMP 13

AT — Tue, 15 May 2018 20:26:28 GMT

Thanks. It works perfectly. The last thing I wanted to do is to set column names the same as first row value and then delete the first row.

Re: Merge CSV in JMP 13

pmroz — Wed, 16 May 2018 11:23:25 GMT

dt = New Table( "Untitled", Add Rows( 4 ),
	New Column( "Column 1", Character, "Nominal",
		Set Values( {"abc", "1", "2", "3"} ) ),
	New Column( "Column 2", Character, "Nominal",
		Set Values( {"def", "6", "5", "4"} ) ),
	New Column( "Column 3", Character, "Nominal",
		Set Values( {"ghi", "7", "6", "5"} ) ),
	New Column( "Column 4", Character, "Nominal",
		Set Values( {"jkl", "2", "3", "4"} ) )
);
// Rename each column to the first row
for (i = 1, i <= ncols(dt), i++,
	column(i) << set name(column(i)[1]);
);
// Remove the first row
dt << delete rows([1]);

Re: Merge CSV in JMP 13

Craige_Hales — Wed, 16 May 2018 11:52:12 GMT

JMP 14 has Multiple File Import. It is designed to load files from a directory and combine similar files (same columns, names). It can load text files into a data table for text explorer, or CSV files into a table using CSV options for headers and skipping some rows.

Choosing files from a directory

CSV options

Re: Merge CSV in JMP 13

AT — Wed, 16 May 2018 16:56:19 GMT

Thanks. I tried yours as well and works fine.

Re: Merge CSV in JMP 13

AT — Wed, 16 May 2018 16:57:47 GMT

Thanks. I am trying to make csv merge for JMP13 and it is great to hear that JMP14 can handle this. It is time to upgrade!!

Re: Merge CSV in JMP 13

AT — Wed, 16 May 2018 18:41:37 GMT

Thanks all for your great support. I also like to be able to pick the directory and select individual csv files instead of slecting all csv files in the directory. I am using the script written by Byron (JMP) and have attached here. It works perfectly but if someone can help me to be able to select individual csv files, that would be very helpful. Thanks

Re: Merge CSV in JMP 13

txnelson — Wed, 16 May 2018 18:46:27 GMT

In the Scripting Index you will find the definition and an example of using Pick File(). This will allow you to select files.

Re: Merge CSV in JMP 13

AT — Wed, 16 May 2018 21:59:59 GMT

Thanks Jim. I used Pick File() but I need the file names only not the fullpath/file name information. Is there a way to get file name instead?

prefilelist = Pick File(
"Select JMP File",
"",
{"JMP Files|jmp;jsl;csv", "All Files|*"},
1,
0,
"",
multiple // save flag is 0, allows multiple file selection
);

show(prefilelist);

Thanks for continuous support.

Re: Merge CSV in JMP 13

txnelson — Wed, 16 May 2018 22:06:05 GMT

1. I very strongly suggest that you read the Scripting Guide......you need to get a review of the different items available in JSL.......

The solution to what you requested is to simply strip off the filename from the end of the complete path. In JSL, there are all kinds of character handling functions. See:

Help==>Scripting Index==>Functions==>Character

Specifically, you need to use the Word() function

completefilename = "/C:/Users/txjim/Parameter Ranking/Parameter Ranking_v8 with private.jsl";
filename = Word( -1, completefilename, "/" );
show( filename );

filename has the value, "Parameter Ranking_v8 with private.jsl"

Re: Merge CSV in JMP 13

AT — Wed, 16 May 2018 22:22:10 GMT

Thanks Jim. I was reading mostly on Pick File() and thought there is a way to do that. I will try your suggestion and let you know. Thanks again.

Re: Merge CSV in JMP 13

AT — Wed, 16 May 2018 23:05:16 GMT

Thanks Jim. I was able to get it to work using Word() function. Thanks for all your support.