How to automatically update rows with file name from a dynamic data import?

[#1]

I'm working on a script that imports multiple files from a folder into a single data table. I have created a column listed USP_ID, in which I would like the filename to appear in each row. Although the data imports correctly, only the name of the final imported excel file is displayed in this column, whereas I would like each file name to be displayed in the rows corresponding to its respective data.

I have listed my script below, with the line I believe is causing me issues, although I don't have a clue to get it sorted. Any suggestions will be greatly appreciated!

Names Default To Here( 1 );
Clear Symbols();
filelist = {};   
filepath = Munger( Pick Directory( "Select the desired file directory" ), 1, "/", "" );
prefilelist = Files In Directory( filepath );
n2 = N Items( prefilelist );
For( i2 = 1, n2 >= i2, i2++,
	file = (prefilelist[i2]);
	If( Item( -1, prefilelist[i2], "." ) == "xlsm" | Item( -1, prefilelist[i2], "." ) == "xlsx",
		Insert Into( filelist, file ),
		Show( file )
	);
);
nf = N Items( filelist ); 
cc_dt = New Table( "Combined data " );
cc_dt << New Column( "USP_ID", character, nominal );
For( iii = 1, iii <= nf, iii++, 
	filenow = (filelist[iii]);
	fileopen = (filepath || filenow);
	dt = Open( fileopen );
	dt << Run Formulas();
	cc_dt << Concatenate( Data Table( dt ), append to first table );
	cc_dt:USP_ID << set each value( filenow ); // having issues with this I think?
	Close( dt, nosave );
);
colList = cc_dt << get column names( character, string );
For( i = 1, i <= N Items( colList ), i++,
	selRows = cc_dt << get rows where(
		Column( cc_dt, colList[i] )[Row()] == "NaN" | Column( cc_dt, colList[i] )[Row()] == "0"
	);
	If( N Rows( selRows ) > 0,
		Column( cc_dt, colList[i] )[selRows] = "0";
		Column( cc_dt, colList[i] ) << data type( "numeric" ) << modeling type( "continuous" );
	);
);

[#2]

cc_dt:USP_ID << set each value( filenow );

Set Each Value set all the values in a column to a constant. So with every loop, you're setting every cell in that column to the current filename. You'll need to set only the "new" cells from the table that was just concatenated to the filename. Maybe something like this?

cc_dt << Select Where( cc_dt:USP_ID == "" ); // select empty cells, assuming previous cells all have their filename values

selrows = cc_dt << Get Selected Rows(); // get the row numbers that are selected

cc_dt:USP_ID[selrows] = filenow; // set just those rows in that column to the filename

HTH,

Melanie

[#3 · Accepted solution]

Hi Melanie, 

Thanks for the help, although I couldn't get your reccomendation to work the way I wanted, I found that by assigning the Source columns to the data tables before concatenating them would do: 

nf = N Items( filelist ); //number of items in the working list
cc_dt = New Table( "Combined fermenter data " );
cc_dt << New Column( "Source", character, nominal );
cc_dt << New Column( "USP_ID", character, formula( Substr( :Source, 1, 7 ) ) );
For( iii = 1, iii <= nf, iii++, //this starts the first loop
	filenow = (filelist[iii]);
	fileopen = (filepath || filenow);
	dt = Open( fileopen );
	dt << Run formulas();
	dt << new column("Source", character, set initial data(filenow));
	cc_dt << Concatenate( Data Table( dt ), append to first table );
	close(dt, nosave);
);