279389_zh-TW

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>