Merge CSV in JMP 13

[#1]

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.

[#2 · Accepted solution]

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.

[#3]

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.

[#4]

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

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

[#5]

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.

[#6]

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);
);

[#7]

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. 

[#8]

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]);

[#9]

Thanks. I tried yours as well and works fine.

[#10]

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