topic Re: Include file name in excel import in Discussions

Include file name in excel import

rickr135 — Sat, 10 Jun 2023 23:30:39 GMT

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

Re: Include file name in excel import

jthi — Tue, 25 May 2021 15:27:47 GMT

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

Re: Include file name in excel import

rickr135 — Tue, 25 May 2021 17:34:47 GMT

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, "/")));

Re: Include file name in excel import

mlo1 — Tue, 07 Dec 2021 20:17:04 GMT

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], "/")));

);