Hola a todos,
Actualmente estoy trabajando en un proyecto y recibí cuatro archivos de Excel que necesito importar en JMP y estaba usando el siguiente script para importar estos archivos, sin embargo, solo me informaron que podrían estar involucrados hasta 20 archivos, así que me preguntaba si el código que tengo a continuación podría simplificarse de que no estoy copiando y pegando hasta 20 veces, todos los archivos de Excel tendrán la misma estructura.
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 ),
);A continuación se muestra una modificación de su script. Toma los elementos necesarios para la entrada de cada archivo y lo coloca en una función que se llama para cada archivo de entrada.
No es un código probado, ya que no tengo los archivos csv sin procesar que está utilizando, sin embargo, si lo ejecuta y mira en el registro cualquier error que cree, debería poder hacer las soluciones simples que requerirá.
Además, requiere que utilice algún método para crear la lista de los diferentes archivos a ingresar. Eche un vistazo en el guión para ver un par de ideas sobre cómo hacerlo
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>
