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!
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]));
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]));
Awesome, this totatly does what I wanted, thank you so much this had me banging my head!