Importing multiple files

Report Inappropriate Content · Jun 10, 2023 4:47 PM

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 )
);File1 = dt;File1 << Set Name("File1");

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!

jthi · Apr 21, 2022 11:28 AM

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));
);

-Jarmo

NominalLogNewt5 · Apr 26, 2022 03:29 AM

Thanks for the quick answer. I tried to implement the code but always get the error message:

"argument should be list in access or evaluation of 'Function'" refering to the line 3

open_and_modify = function({file_path}, {Default Local},

Can you tell me what mistake I made? I always run into this issue.

Thanks again!

jthi · Apr 26, 2022 10:04 AM

Only thing that quickly comes to my mind is that you are missing {} around the arguments of 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);
);

when it should be

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);
);