279389_zh-CN

liliconcom

用于导入多个具有相同结构的电子表格的循环

大家好，

 

我目前正在开发一个项目，并获得了四个需要导入到 JMP 中的 excel 文件，并且正在使用以下脚本导入这些文件，但是，我刚刚被告知可能涉及最多 20 个文件，所以我想知道是否我下面的代码可以简化，我不会复制和粘贴多达 20 次，所有 Excel 文件都将具有相同的结构。

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

自动化和脚本编写内容组织数据存取回复：导入具有相同结构的多个电子表格的循环

你好@tx尼尔森,

 

这很有效，不得不在某些地方摆弄代码，但除此之外它工作得很好，非常感谢您的帮助和建议。

 

问候，

米克.

 

回复：导入具有相同结构的多个电子表格的循环

你好@tx尼尔森,

 

再次非常感谢您的帮助，抱歉，我忘记留下 Excel 文件，这里有一些需要解压的内容，我将完成它并让您知道我是如何进行的。

 

再次感谢。

米克

回复：导入具有相同结构的多个电子表格的循环

以下是对您的脚本的修改。它获取每个文件输入所需的元素，并将其放入为每个输入文件调用的函数中。

它不是经过测试的代码，因为我没有您正在使用的原始 csv 文件，但是，如果您运行它并查看日志以查找它创建的任何错误，您应该能够进行所需的简单修复。

此外，它还要求您使用某种方法来创建要输入的不同文件的列表。查看脚本，了解如何做到这一点的一些想法

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>