Include file name in excel import

[#1]

Is there a way to include a column with the file name on an imported excel table? Using the multiple file import tool there is an option to include this, but I haven't found the equivalent when importing a singular file. 

I've tried using get path/get name and then writing that to a column but that just gives me "Sheet1.jmp". 

The Open command I used was copied from the wizard: 

dtt.file= Pick File(); // Opens File dialogue

Open(
	dtt.file,
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 2 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 3 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	);
);

[#2 · Accepted solution]

Not sure if there are any direct solutions like in Multiple File Import but you can create new column after you have loaded the data.

Below are two ways to do it, one using the picked file path and other using table name (this might be affected by the sheet name):

Names Default To Here(1);

dtt.file= Pick File(); // Opens File dialogue

dt = Open(
	dtt.file,
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 2 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 3 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	);
);
dt << New Column("Filename", Character, "Nominal", << Set Each Value(Word(-1, dtt.file, "/")));
dt << New Column("Filename", Character, "Nominal", << Set Each Value(dt << Get Name()));

See Scripting Index for more information on the functions, especially Word().

[#3]

Thank you! Using the following worked for what I was trying to accomplish: 

dt << New Column("Filename", Character, "Nominal", << Set Each Value(Word(-1, dtt.file, "/")));

[#4]

Thank you for this solution.

I had a similar issue and needed to put it in a loop to open multiple excel files.

Names Default To Here( 1 );
path = Pick Directory( "Select a directory" );
file_list = Files In Directory(path, recursive);

for(i = 1, i <= N Items(file_list), i++,
filename = path||file_list[i];

dt = Open(
	filename,
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 41 ),
		Data Starts on Row( 42 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 54 ),
		Data Ends on Column( 20 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);
dt << New Column ("Filename", Character, "Nominal", << Set Each Value(Word(-1, file_list[i], "/")));

);