Ciao a tutti,
Attualmente sto lavorando a un progetto e mi hanno dato quattro file Excel che devo importare in JMP e stavo usando lo script seguente per importare questi file, tuttavia, sono stato appena informato che potrebbero essere coinvolti fino a 20 file, quindi mi chiedevo se il codice che ho sotto potrebbe essere semplificato dal fatto che non sto copiando e incollando fino a 20 volte, tutti i file Excel avranno la stessa struttura.
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 ),
);Di seguito è una modifica del tuo script. Prende gli elementi richiesti per l'inserimento di ciascun file e lo inserisce in una funzione chiamata per ciascun file di input.
Non è un codice testato, dal momento che non ho i file CSV grezzi che stai utilizzando, tuttavia, se lo esegui e cerchi nel registro eventuali errori che crea, dovresti essere in grado di apportare le semplici correzioni che richiederà.
Inoltre, è necessario utilizzare un metodo per creare l'elenco dei diversi file da inserire. Dai un'occhiata alla sceneggiatura per un paio di idee su come farlo
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>
