BookmarkSubscribeSubscribe to RSS Feed

Re: Merge csv files with different columns names

AT

Regular Contributor

Joined:

Dec 2, 2017

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

Joined:

Jun 23, 2011

Solution

@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
5 REPLIES
gzmorgan0

Community Trekker

Joined:

Jul 25, 2016

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

Joined:

Jun 23, 2011

Solution

@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

Regular Contributor

Joined:

Dec 2, 2017

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

Highlighted
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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

Regular Contributor

Joined:

Dec 2, 2017

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.