Merge csv files with different columns names

[#1]

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

[#2]

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.

[#3 · Accepted 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.

---

[#4]

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

[#5]

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.

[#6]

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.