279389_en-US

liliconcom

A loop to import muliple spreadsheets with the same structure

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

Automation and ScriptingContent OrganizationData AccessRe: A loop to import muliple spreadsheets with the same structure

Hi @txnelson, 

 

This worked a treat, had to fiddle with the code in some places but other than that it worked fine, thanks so much for your help and advice.

 

Regards,

Mick.

 

Re: A loop to import muliple spreadsheets with the same structure

Hi @txnelson,

 

Once again thanks very much for your help, apologies, l neglected to leave the excel file, there is a bit here to unpack, will work through it and let you know how i went.

 

Thanks again.

Mick

Re: A loop to import muliple spreadsheets with the same structure

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></MODAL>