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
AT
AT
Level V

Merge CSV in JMP 13

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.

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Merge CSV in JMP 13

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.

Jim

View solution in original post

16 REPLIES 16
txnelson
Super User

Re: Merge CSV in JMP 13

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.

Jim
AT
AT
Level V

Re: Merge CSV in JMP 13

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.

 

txnelson
Super User

Re: Merge CSV in JMP 13

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

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

Jim
AT
AT
Level V

Re: Merge CSV in JMP 13

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.

pmroz
Super User

Re: Merge CSV in JMP 13

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);
);
AT
AT
Level V

Re: Merge CSV in JMP 13

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. 

pmroz
Super User

Re: Merge CSV in JMP 13

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]);
AT
AT
Level V

Re: Merge CSV in JMP 13

Thanks. I tried yours as well and works fine.

Craige_Hales
Super User

Re: Merge CSV in JMP 13

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 directoryChoosing files from a directory

CSV optionsCSV options

Craige