A loop to import muliple spreadsheets with the same structure

Mickyboy

Hi all,

 

l am currently working on a project and was given four excel files that l need to import into JMP and was using the below script to import these files, however, l was just informed that up to 20 files might be involved so i was wondering if the code i have below could be simplified that i am not copying and pasting up to 20 times, all the excel files will have the same structure.

Names Default To Here( 1 );
Clear Symbols();

pb = Page Break Box();

fileopen= "\\prusntyfil01\home$\sv001241\Desktop\Project Rodger\File 1.xlsx";

//dt=open(fileopen,private);
	dt = Open(
		 fileopen, 
		 Worksheets( "Sheet1" ),
		          
		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( 18 ),
				Data Ends on Column( 39 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "" )
			),
		); 
Datasource = fileopen;
	
	
	dtvn = Open(
		 fileopen, 
		 Worksheets( "Sheet1" ),
		Worksheet Settings(
				1,          
				Has Column Headers( 0 ),
				Number of Rows in Headers( 0 ),
				Headers Start on Row( 0 ),
				Data Starts on Row( 1 ),
				Data Starts on Column( 1 ),
				Data Ends on Row( 1 ),
				Data Ends on Column( 1 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "-" )
			),
		);
col=Column(1);
col << Set Name("plate");
	
dt1=dt << Join(
        With( dtvn ),
        Copy second table formula( 0 ),
		Suppress main table formula evaluation( 0 ),
		Cartesian Join
);	

dt1 << New Column( "Scheme", Character, Nominal, Formula(Left(Right(:plate, 15), 2) ) );

fileopen= "\\prusntyfil01\home$\sv001241\Desktop\Project Rodger\File 2.xlsx";

//dt=open(fileopen,private);
	dt2 = Open(
		 fileopen, 
		 Worksheets( "Sheet1" ),
		          
		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( 18 ),
				Data Ends on Column( 39 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "" )
			),
		); 
Datasource = fileopen;
	
	
	dtvn1 = Open(
		 fileopen, 
		 Worksheets( "Sheet1" ),
		Worksheet Settings(
				1,          
				Has Column Headers( 0 ),
				Number of Rows in Headers( 0 ),
				Headers Start on Row( 0 ),
				Data Starts on Row( 1 ),
				Data Starts on Column( 1 ),
				Data Ends on Row( 1 ),
				Data Ends on Column( 1 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "-" )
			),
		);
col=Column(1);
col << Set Name("plate");
	
dt3=dt << Join(
        With( dtvn1 ),
        Copy second table formula( 0 ),
		Suppress main table formula evaluation( 0 ),
		Cartesian Join
);	

dt3 << New Column( "Scheme", Character, Nominal, Formula(Left(Right(:plate, 15), 2) ) );

fileopen= "\\prusntyfil01\home$\sv001241\Desktop\Project Rodger\File 3.xlsx";

//dt=open(fileopen,private);
	dt4 = Open(
		 fileopen, 
		 Worksheets( "Sheet1" ),
		          
		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( 18 ),
				Data Ends on Column( 39 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "" )
			),
		); 
Datasource = fileopen;
	
	
	dtvn2 = Open(
		 fileopen, 
		 Worksheets( "Sheet1" ),
		Worksheet Settings(
				1,          
				Has Column Headers( 0 ),
				Number of Rows in Headers( 0 ),
				Headers Start on Row( 0 ),
				Data Starts on Row( 1 ),
				Data Starts on Column( 1 ),
				Data Ends on Row( 1 ),
				Data Ends on Column( 1 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "-" )
			),
		);
col=Column(1);
col << Set Name("plate");
	
dt5=dt4 << Join(
        With( dtvn2 ),
        Copy second table formula( 0 ),
		Suppress main table formula evaluation( 0 ),
		Cartesian Join
);	

dt5 << New Column( "Scheme", Character, Nominal, Formula(Left(Right(:plate, 15), 2) ) );

fileopen= "\\prusntyfil01\home$\sv001241\Desktop\Project Rodger\File 4.xlsx";

//dt=open(fileopen,private);
	dt6 = Open(
		 fileopen, 
		 Worksheets( "Sheet1" ),
		          
		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( 18 ),
				Data Ends on Column( 39 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "" )
			),
		); 
Datasource = fileopen;
	
	
	dtvn3 = Open(
		 fileopen, 
		 Worksheets( "Sheet1" ),
		Worksheet Settings(
				1,          
				Has Column Headers( 0 ),
				Number of Rows in Headers( 0 ),
				Headers Start on Row( 0 ),
				Data Starts on Row( 1 ),
				Data Starts on Column( 1 ),
				Data Ends on Row( 1 ),
				Data Ends on Column( 1 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "-" )
			),
		);
col=Column(1);
col << Set Name("plate");
	
dt7=dt6 << Join(
        With( dtvn3 ),
        Copy second table formula( 0 ),
		Suppress main table formula evaluation( 0 ),
		Cartesian Join
);	

dt7 << New Column( "Scheme", Character, Nominal, Formula(Left(Right(:plate, 15), 2) ) );

dt8=dt1 << Concatenate(
			Data Table ( dt3 ),
			Data Table ( dt5 ),
			Data Table ( dt7 ),
	);

txnelson

Below is a modification of your script.  It takes the elements required for the inputting of each file and places it into a function which is called for each input file.

It is not tested code, since I do not have the raw csv files that you are using, however, if you run it and look through the log for any errors it creates, you should be able to make the simple fixes it will require.

Also, it requires that you use some method for creating the list of the different files to be input.  Take a look in the script for a couple of ideas of how to do that

Names Default To Here( 1 );
Clear Symbols();

// Create the start of the final data table
dtFinal = New Table( "Final Table" );

// Build or Create a list with all of the paths to the files to be read in
// This could be a manually typed in list such as
fileList = {
	"\\prusntyfil01\home$\sv001241\Desktop\Project Rodger\File 1.xlsx",
	"\\prusntyfil01\home$\sv001241\Desktop\Project Rodger\File 2.xlsx",
	"\\prusntyfil01\home$\sv001241\Desktop\Project Rodger\File 3.xlsx",
	"\\prusntyfil01\home$\sv001241\Desktop\Project Rodger\File 4.xlsx"	
};
// or it could be that all files to be read in are placed into a separate
// folder, and then that folder is read and the list of files is created
// from there
fileList = Files In Directory("Path to folder");
// or some other interactive way of selecting the files
fileList = {};
nw = New Window("Pick Files to Process", <<Modal,
	H List Box(
		panel box("Files to Read",
			theFiles = List Box()
		),
		Border Box(left(5),right(5),top(5),bottom(5),sides(15),
			v list box(
				button box("Add a File",
					myFile = PickFile("Select a file");
					if(myFile != "", insert into(fileList,myfile); theFiles<<append(fileList) )
				),
				button Box("OK"),
				button Box("Cancel",throw())
			)
		)
	)
);

pb = Page Break Box();

// Create a function that processes each file
openFile = Function( {fileopen, dtFinal},
	{defaultlocal}, 
//dt=open(fileopen,private);
	dt = Open(
		fileopen,
		Worksheets( "Sheet1" ), 
		          
		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( 18 ),
			Data Ends on Column( 39 ),
			Replicated Spanned Rows( 1 ),
			Replicated Spanned Headers( 0 ),
			Suppress Hidden Rows( 1 ),
			Suppress Hidden Columns( 1 ),
			Suppress Empty Columns( 1 ),
			Treat as Hierarchy( 0 ),
			Multiple Series Stack( 0 ),
			Import Cell Colors( 0 ),
			Limit Column Detect( 0 ),
			Column Separator String( "" )
		), 

	);
	Datasource = fileopen;
	
	
	dtvn = Open(
		fileopen,
		Worksheets( "Sheet1" ),
		Worksheet Settings(
			1,
			Has Column Headers( 0 ),
			Number of Rows in Headers( 0 ),
			Headers Start on Row( 0 ),
			Data Starts on Row( 1 ),
			Data Starts on Column( 1 ),
			Data Ends on Row( 1 ),
			Data Ends on Column( 1 ),
			Replicated Spanned Rows( 1 ),
			Replicated Spanned Headers( 0 ),
			Suppress Hidden Rows( 1 ),
			Suppress Hidden Columns( 1 ),
			Suppress Empty Columns( 1 ),
			Treat as Hierarchy( 0 ),
			Multiple Series Stack( 0 ),
			Import Cell Colors( 0 ),
			Limit Column Detect( 0 ),
			Column Separator String( "-" )
		), 

	);
	col = Column( 1 );
	col << Set Name( "plate" );
	
	dt1 = dt << Join(
		With( dtvn ),
		Copy second table formula( 0 ),
		Suppress main table formula evaluation( 0 ),
		Cartesian Join
	);	

	dt1 << New Column( "Scheme",
		Character,
		Nominal,
		Formula( Left( Right( :plate, 15 ), 2 ) )
	);

	// Concatenate the newly input/joined file to the final data table
	dtFinal = dtFinal << concatenate( dt1, Append to First Table( 1 ) );

	// Close the interum files that are no longer needed
	Close( dt, nosave );
	Close( dtvn, nosave );
	Close( dtFinal, nosave );

	dtFinal;

);

// Loop through the fileList and process each file
For(fileNum = 1, fileNum <= N Items( fileList ), fileNum++,
	dtFinal = openFile( fileList[fileNum], dtFinal );
);