topic Re: Merge csv files with different columns names in Discussions

Merge csv files with different columns names

AT — Thu, 09 Aug 2018 16:55:27 GMT

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

Re: Merge csv files with different columns names

gzmorgan0 — Thu, 09 Aug 2018 17:20:17 GMT

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.

Re: Merge csv files with different columns names

Jeff_Perkinson — Thu, 09 Aug 2018 22:09:52 GMT

@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.

Re: Merge csv files with different columns names

AT — Thu, 09 Aug 2018 20:50:51 GMT

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

Re: Merge csv files with different columns names

Jeff_Perkinson — Thu, 09 Aug 2018 22:16:33 GMT

Well, you can change the defaults in Preferences.

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.

Re: Merge csv files with different columns names

AT — Mon, 13 Aug 2018 14:20:37 GMT

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.