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!