按行連接多個表你好，
我一直在用應該非常簡單的代碼敲打我的頭。
我想從多個 excel 文件和工作表中打開表格，並將它們加入一個寬表中，其中列的名稱與它們的 excel 工作表相對應。
就我而言，列應如下所示。
| TestWB1Sheet1 的第 1 列 |
TestWB1Sheet1 的第 2 列 |
TestWB1Sheet1 的第 3 欄 |
TestWB1Sheet2 的第 1 列 |
... |
TestWB2Sheet4 的第 3 欄 |
一個問題是我不想從每個 excel 文件的最後一個工作表（在我的示例中為 sheet5）上傳數據。
我已嘗試使用此討論中的代碼，但似乎無法正確命名。 https://community.jmp.com/t5/Discussions/Joining-Multiple-Data-Table-using-a-loop/mp/9451
我相信答案可能在這個討論中，但由於無關代碼，我很難消化解決方案。
https://community.jmp.com/t5/Discussions/Joining-multiple-DataTables-and-retaining-column-name/mp/18874#M17198
這是我沒有正確命名列的簡化代碼：
Names Default to Here(1);
//Close All is a convienience script to use while writing the script
//Close All(Data Tables, NoSave);
//Create list of files to open
file = Pick File("Pick file/files", "$Desktop", {"All Files|*"}, 1,0, "", multiple);
//create blank list of data tables
dtList = {};
//Open list of files to dtList
for (i=1, i<=nitems(file), i++,
ws = Get Excel Worksheets(file[i]);
sws = ws[1::(N Items(ws) - 1)];
show(i);
for (s=1, s<=nitems(sws), s++,
show(s);
dt = Open(file[i],
Worksheets( sws[s] ),
use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
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( "-" )
)
);
dt << Set Name(char(i)||char(s));
insert into (dtList, dt);
)
);
//Designate dt_old to hold the joined data
dt_old = data table(dtList[1]);
//Join all tables in dtList to dt_old by row number
for ( i = 2, i <= N Items( dtList ), i++,
dt_new = data table(dtList[i]);
join_name = "Limits" || char(i);
show(i);
show(dt_new);
dt_old << Join( With (dt_new),
By Row Number,
Output Table( join_name )
);
show(dt_old);
close(dt_old, nosave);
dt_old = data table(join_name);
);
在此先感謝您的幫助。
腳本編寫回复：按行連接多個表
Names Default To Here(1);
//directory with files
xlsx_files = Files In Directory("./WB");
//dt collection list
dt_list = {};
For Each({file}, xlsx_files,
//open datatables one by one
dt = Open(
"WB/" || file,
Worksheets({"Sheet1", "Sheet2", "Sheet3", "Sheet4"}),
Use for all sheets(1),
Concatenate Worksheets(1),
Create Concatenation Column(1),
Worksheet Settings(
1,
Has Column Headers(1),
Number of Rows in Headers(1),
Headers Start on Row(1),
Data Starts on Row(2),
Data Starts on Column(1),
Data Ends on Row(0),
Data Ends on Column(0),
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("-")
)
);
//add table name to SourceTable column
Column(dt, "Source Table") << Set Each Value(Word(1, file, ".") || :"Source Table");
//add to dtlist
Insert Into(dt_list, dt);
);
//concatenate
For(i = 2, i <= N Items(dt_list), i++,
dt_list[1] << Concatenate(dt_list[i], "Append to first table");
Close(dt_list[i], no save);
);
//split
dt = dt_list[1] << Split(
Split By(:Source Table),
Split(:Col1, :Col2, :Col3),
Sort by Column Property
);
//Close(dt_list[1], no save);
- 獲取目錄中的所有文件（如果有，應刪除不需要的文件）
- 循環遍歷 xlsx 文件，同時將它們插入列表
- 將所有文件連接到一個 jmp 數據表
- 拆分數據表
連接前的表：
連接後：
拆分後：
回复：按行連接多個表這是我對您的代碼的修改以創建所有新列，並將它們連接在一起
我只需要添加一段簡單的 JSL 來重命名列。您的所有其餘代碼都運行良好。
Names Default To Here( 1 );
//Close All is a convienience script to use while writing the script
//Close All(Data Tables, NoSave);
//Create list of files to open
file = Pick File( "Pick file/files", "$Desktop", {"All Files|*"}, 1, 0, "", multiple );
//create blank list of data tables
dtList = {};
//Open list of files to dtList
For( i = 1, i <= N Items( file ), i++,
ws = Get Excel Worksheets( file[i] );
sws = ws[1 :: (N Items( ws ) - 1)];
Show( i );
For( s = 1, s <= N Items( sws ), s++,
Show( s );
dt = Open(
file[i],
Worksheets( sws[s] ),
use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
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( "-" )
)
);
dt << Set Name( Char( i ) || Char( s ) );
Insert Into( dtList, dt );
// set new column names
currFileName = Word( -2, File[i], ".\/" );
For( k = 1, k <= N Cols( dt ), k++,
Column( dt, k ) << set name(
Column( dt, k ) << get name || " of " || currFileName || sws[s]
)
);
);
);
//Designate dt_old to hold the joined data
dt_old = Data Table( dtList[1] );
//Join all tables in dtList to dt_old by row number
For( i = 2, i <= N Items( dtList ), i++,
dt_new = Data Table( dtList[i] );
join_name = "Limits" || Char( i );
Show( i );
Show( dt_new );
dt_old << Join( With( dt_new ), By Row Number, Output Table( join_name ) );
Show( dt_old );
Close( dt_old, nosave );
dt_old = Data Table( join_name );
);
