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 files with different columns names

Hi,

I have many csv files that are many columns and most of them have the same column names but some have new column names and so have fewer columns. For example, the first csv has columns X, Y, the second csv has X, Y, Z, and the third csv has X, Z. I like to be able to merge these files together using JMP script. I appreciate any help on this. Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: Merge csv files with different columns names


@AT wrote:

I have many csv files that are many columns and most of them have the same column names but some have new column names and so have fewer columns. For example, the first csv has columns X, Y, the second csv has X, Y, Z, and the third csv has X, Z. I like to be able to merge these files together using JMP script. I appreciate any help on this. Thanks


You'll need to define "merge" a bit more clearly. Do you mean concatenate them into one data table with columns X, Y, and Z? If so, just use Tables->Concatenate to do that, it will combine like named columns automatically. If you want the script, after you use concatenate you'll get a Source script in the resulting data table showing the JSL to repeat that action.


-Jeff

View solution in original post

5 REPLIES 5
gzmorgan0
Super User (Alumni)

Re: Merge csv files with different columns names

If you mean append the files, use JMP Main Menu>Tables>Concatenate. This appends columns according to their column names, The data in the different columns needs to have the same data type and modeling type.

 

So Open the files and then Concatenate them.  Se example 2 in the Help>Scripting Index> Objects > Data Table>Concatenate. It is an example of the data tables you described.

 

If it were more complex use Query Builder.

Jeff_Perkinson
Community Manager Community Manager

Re: Merge csv files with different columns names


@AT wrote:

I have many csv files that are many columns and most of them have the same column names but some have new column names and so have fewer columns. For example, the first csv has columns X, Y, the second csv has X, Y, Z, and the third csv has X, Z. I like to be able to merge these files together using JMP script. I appreciate any help on this. Thanks


You'll need to define "merge" a bit more clearly. Do you mean concatenate them into one data table with columns X, Y, and Z? If so, just use Tables->Concatenate to do that, it will combine like named columns automatically. If you want the script, after you use concatenate you'll get a Source script in the resulting data table showing the JSL to repeat that action.


-Jeff
AT
AT
Level V

Re: Merge csv files with different columns names

Thanks Jeff. This is very helpful. I also like to set the preferences in reading csv file to be different that default. Can one add the preferences for header row and data rows in the script? Thanks

Jeff_Perkinson
Community Manager Community Manager

Re: Merge csv files with different columns names

Well, you can change the defaults in Preferences.

JMPScreenSnapz262.png

But, yes, you can change the import options in JSL for an individual file. The easiest way to get the JSL for a specific file is to import it with Preview. The resulting data table will have a Source script with the JSL for that file.

-Jeff
AT
AT
Level V

Re: Merge csv files with different columns names

Thanks Jeff. I was able to use the open file and set column name start and data starts and workfine.

 

dt = Open( filenow, private,
Column Names Start( 2 ),
Data Starts( 3 ) );

 

 

Thanks.