Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
UserID16644
Level III

Combining array into file directory & excel files into one master file

Hi,

I'm trying to store column values from a data table (which are my folder's name) into an associative array then use it to open and access the excel files inside it. I have this code but I cannot combine the directories with the array which should open each folder to have an access on the excel files.

 

How can I concatenate the file path + array in order for me to access the excel files inside each folder and combine every excel file into one?

 

Array list of folder name:

//open data table
j= associative array ( Column(dt, "JOB_NAME") 
	<<get values)
		<<get keys;

Combining all the excel file from each folder into one master file:

files = Files In Directory( "C:\Users\Sample Data\"); // Should be files + array to access every folder
X = N Items( files ); Y = "C:\Users\Sample Data\"; dt = New Table( "My data file"); For( i = 1, i < X, i++, //open jmp files for each xls files. dt1 = Open( Y || files[i], Worksheets( "my xls worksheet" ), Use for all sheets( 0 ), Concatenate Worksheets( 0 ), Create Concatenation Column( 0 ), Worksheet Settings( 1, Has Column Headers( 0 ), Number of Rows in Headers( 1 ), Headers Start on Row( 1 ), Data Starts on Row( 1 ), Data Starts on Column( 1 ), Data Ends on Row( 0 ), Data Ends on Column( 0 ), Replicated Spanned Rows( 1 ), Suppress Hidden Rows( 1 ), Suppress Hidden Columns( 1 ), Suppress Empty Columns( 1 ), Treat as Hierarchy( 0 ) ) ); dt1 << Set Name( files[i] ); dt << Concatenate( Data Table( dt1 ), "Append to first table" ); Close( dt1 );   );

 

1 REPLY 1
Highlighted
Byron_JMP
Staff

Re: Combining array into file directory & excel files into one master file

Not entirely sure, but are you trying to do something like this? JMP14 How can i import multi excel files with defined sheet?

 

JMP Systems Engineer, Pharm and BioPharm Sciences