BookmarkSubscribeSubscribe to RSS Feed
Highlighted
AT

Regular Contributor

Joined:

Dec 2, 2017

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

Joined:

Jun 22, 2012

Solution

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
16 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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

Regular Contributor

Joined:

Dec 2, 2017

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

Joined:

Jun 22, 2012

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

Regular Contributor

Joined:

Dec 2, 2017

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

Joined:

Jun 23, 2011

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

Regular Contributor

Joined:

Dec 2, 2017

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

Joined:

Jun 23, 2011

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

Regular Contributor

Joined:

Dec 2, 2017

Re: Merge CSV in JMP 13

Thanks. I tried yours as well and works fine.

Craige_Hales

Staff

Joined:

Mar 21, 2013

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. 

Capture.PNGChoosing files from a directory

Capture1.PNGCSV options

Craige