480922_en-US

liliconcom

Importing multiple files

Dear all,

 

my problem has probably been brought up before as it is quite basic but I couldn't find the solution to my exact problem.

 

I want to import multiple files with the same structure,  manipulate them in the identical way and then glue a subset of them together. I have done the importing and data manipulation part in the following generic way:

dt = Open(
 "PATH/File1.xlsx",
 Worksheets( "Table1" ),
 XXX
);

// New operation e.g. New column: Column 2
dt << New Column( "Column 2",
 Numeric,
 "Continuous",
 Format( "Best", 12 )
);<BR /><BR />File1 = dt;<BR />File1 << Set Name("File1");<BR />

In order to import more files, I would copy the same code to the script and only exchange "File1" by "File2", "File3" and so on. It works but the script length gets quite big since I have a lot of files to import.

 

I also tried the IMF option. When I chose "Open multiple tables" (I don't want it stacked) I have the data tables already linked to specific names and couldn't get them manipulated with a the generic code described above.

 

Any ideas for a more elegant solutions?

 

Thanks a lot!

 

Data TableRe: Importing multiple files

You could try using function() (jmp.com)  or expr(). Here are few options using  function():

Names Default To Here(1);

open_and_modify = function({file_path}, {Default Local},
 dt = Open(file_path, Worksheets("Table1"), XXX);

 // New operation e.g. New column: Column 2
 dt << New Column("Column 2", Numeric, "Continuous", Format("Best", 12));
 dt << Set Name("File1");
 return(dt);
);

dt1 = open_and_modify("PATH/File1.xlsx");
dt2 = open_and_modify("PATH/File2.xlsx");
dt3 = open_and_modify("PATH/File3.xlsx");


//or have a list of paths
file_paths = {"PATH/File1.xlsx", "PATH/File2.xlsx", "PATH/File3.xlsx"};
//create first concatenate before 
table_list = {};
For Each({new_file_path}, files,
 Insert Into(table_list, open_and_modify(new_file_path));
);