Include file name in excel import

rickr135

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

jthi

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().