cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
drblove
Level III

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions

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

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]));

View solution in original post

2 REPLIES 2

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

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]));
drblove
Level III

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

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