topic Re: Importing multiple files in Discussions

Importing multiple files

NominalLogNewt5 — Sat, 10 Jun 2023 23:47:27 GMT

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!

Re: Importing multiple files

jthi — Thu, 21 Apr 2022 15:28:57 GMT

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

Re: Importing multiple files

NominalLogNewt5 — Tue, 26 Apr 2022 07:29:12 GMT

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!

Re: Importing multiple files

jthi — Tue, 26 Apr 2022 14:04:31 GMT

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

Re: Importing multiple files

Craige_Hales — Tue, 26 Apr 2022 15:38:45 GMT

Possibly an issue with an international keyboard using the curly braces to make locale-specific characters?

https://stackoverflow.com/questions/2388805/brackets-and-international-keyboards

https://superuser.com/questions/1486481/when-i-press-the-curly-braces-key-on-the-keyboard-i-get-a-backtick-instead

https://www.reddit.com/r/Anki/comments/j1c5o2/curly_braces_dont_work_with_norwegian_keyboard/

https://cs50.stackexchange.com/questions/9603/curly-brackets-on-my-keyboard

https://discussions.apple.com/thread/7418604