按行连接多个表你好，
我一直在用应该非常简单的代码敲打我的头。
我想从多个 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 );
);
