Adding a column, and adding a different string for each loop

drblove — Wed, 02 Nov 2016 21:32:12 GMT

I am putting together some code where I'm trying to loop through openning files and adding the file to the end of the file for a table. So if openning three files basically it appends it to the end of the first file for the table, but I'm having two issues, but will just talk about the one here.

I add a column to the output table path, so I could keep track of where it was going. So here is the code, where it works if nitems(selected_files) = 1, but if it is greater than 1 the result is blank, what am I doing wrong? Here is the code:

default_directory = "C:";
// Open Dialog box to select input files
selected_files = Pick File("Select Input File",default_directory,{"Excel Files|xls;xlsx","All Files|*"},1,0,"",multiple);
file_name = selected_files;
all_data_table = Empty();
For(i=1, i <= nitems(selected_files), i++,
	num = Char(i);
	file_name[i] = Concat Items({"File",num});
	out_here = Open(
		selected_files[i],
		Worksheets( "Sheet1" ),
		Use for all sheets( 1 ),
		Concatenate Worksheets( 0 ),
		Invisible,
		Create Concatenation Column( 0 ),
		Worksheet Settings(
			1,
			Has Column Headers( 1 ),
			Number of Rows in Headers( 1 ),
			Headers Start on Row( 2 ),
			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( 0 ),
			Suppress Hidden Columns( 0 ),
			Suppress Empty Columns( 0 ),
			Treat as Hierarchy( 0 ),
			Multiple Series Stack( 0 ),
			Import Cell Colors( 0 ),
			Limit Column Detect( 0 ),
			Column Separator String( "-" )
		)
	);
	out_here << New Column("Path",Numeric,"Continuous",Formula(selected_files[i]));
	out_here << New Column("File",Numeric,"Continuous",Formula(file_name[i]));
	If( Is Empty( all_data_table ),
		all_data_table = out_here;
		,
		all_data_table << Concatenate( out_here, Append to first table );
	);
);

all_data_table << New Data View;

If you run this code you will see with one file selected it appears to do what I am looking for, but 2 or more it does not. Any help would be greatly appreciated!

Re: Adding a column, and adding a different string for each loop

Melanie_J_Drake — Thu, 03 Nov 2016 19:14:30 GMT

The problem is that you're setting the two columns to formulas that use the variables selected_files[i] and file_name[i]. So when you continue through the script, those values change every time i changes. Instead of using a formula, set the values directly to the values you want. Also, since you're placing character values into these two columns, I recommend creating them as character columns instead of numeric columns. Change the two New Column lines to this instead and I think you'll get what you're looking for:

out_here << New Column("Path","Character","Nominal",Set Each Value(selected_files[i]));
out_here << New Column("File","Character","Nominal",Set Each Value(file_name[i]));

Re: Adding a column, and adding a different string for each loop

drblove — Wed, 09 Nov 2016 15:58:33 GMT

Awesome, this totatly does what I wanted, thank you so much this had me banging my head!

topic Re: Adding a column, and adding a different string for each loop in Discussions

Adding a column, and adding a different string for each loop

Re: Adding a column, and adding a different string for each loop

Re: Adding a column, and adding a different string for each loop