335442_zh-CN

liliconcom

如何通过多个csv文件循环脚本

在前一篇文章之后，我得到了一个脚本，该脚本可以堆叠并格式化笨拙的csv文件。

https://community.jmp.com/t5/Discussions/Help-doing-a-table-transform-and-converting-a-timestamp/td-p/303091

 

我下一个需要帮助的问题是，如何才能在文件夹中的一组csv上重复该操作，将文件名捕获为列之一，最好也将它们堆叠起来。又名step1，step2，step3，step4文件。

堆叠单个csv可创建400k行输出。 如果我将这些csv的4 ot 8或10合并，是否会有崩溃的风险？

也许额外的需要是然后再次循环遍历父文件夹中的下一个文件夹，从而创建一个新的组合步骤文件？

 

names default to here(1);

//Prompt to select and open an MS Excel file
dt = Open( Pick File( "Select Excel File", "C:\Users\folder\", {"Excel Files|csv;xlsx;xls", "All Files|*"}, 1, 0 ), Invisible );

// Get the column names from the data table
colNames = dt << get column names;
// Remove the 1st col from the list
remove from(colNames,1,1);

// Stack the data
dtStack = dt << Stack(
 columns(
  colNames
 ),
 Source Label Column( "Label" ),
 Stacked Data Column( "Data" ),
 Output Table( "Stacked_Data" )
);

//label the 1st column to Lamda
:Column 1 << Set Name("Lamda");
:Lamda << Modeling Type(nominal);

// Create a new JMP DateTime column
dtStack << New Column( "Timestamp",
 Numeric,
 "Continuous",
 Format( "d/m/y h:m:s", 26, 3 ),
 Input Format( "d/m/y h:m:s", 0 ),
 Formula( Informat( Word( 1, :Label, "Z" ), "d/m/y h:m:s" ) )
);
// Remove formula to convert column to static values
dtStack:Timestamp << delete formula;

// Create the Time variable column
dtstack << New Column( "Time",
 Numeric,
 "Continuous",
 Format( "hr:m:s", 17, 3 ),
 Input Format( "hr:m:s", 0 ),
 Formula( Time Of Day( :Timestamp ) )
);

//Create Absolute Time column, uses if>then error checking for Lamda group changing
dtstack << New Column( "AbsTime",
 Numeric,
 "Continuous",
 Format( "Fixed Dec", 12, 1 ),
 Formula(
  If( Row() == 1 | :Lamda != Lag( :Lamda ),
   0,
   Lag( :AbsTime ) + (:Timestamp - Lag( :Timestamp ))
  )
 ),
);
// Remove formula to convert column to static values
dtStack:AbsTime << delete formula;

// Get rid of no longer needed "Label" column
dtStack << delete columns( "Label" );

 

数据表脚本编写回复：如何通过多个csv文件循环脚本

基本上，您要做的就是

1. 使用“ Files In Directory（）获取要处理的文件列表
2. 设置要用于最终数据表的数据表（dtFinal）
3. 使用For（）循环遍历创建deStack表的目录中找到的所有文件
   1. 将Source列添加到dtStack
   2. 将dtStack连接到dtFinal表
   3. 关闭dtStack和dt数据表

以下是使用上述项目修改的脚本。该代码未经测试，您必须添加目录等才能从中读取文件。它应该为您提供最终产品的真正好的起点

names default to here(1);

//Prompt to select and open an MS Excel file
//dt = Open( Pick File( "Select Excel File", "C:\Users\folder\", {"Excel Files|csv;xlsx;xls", "All Files|*"}, 1, 0 ), Invisible );

// Get a list of files from the directory
fileList = Files In Directory( <PATH to="" your="" directory=""> || fileList[i] );

// Get the column names from the data table
colNames = dt << get column names;
// Remove the 1st col from the list
remove from(colNames,1,1);

// Stack the data
dtStack = dt << Stack(
 columns(
  colNames
 ),
 Source Label Column( "Label" ),
 Stacked Data Column( "Data" ),
 Output Table( "Stacked_Data" )
);

//label the 1st column to Lamda
:Column 1 << Set Name("Lamda");
:Lamda << Modeling Type(nominal);

// Create a new JMP DateTime column
dtStack << New Column( "Timestamp",
 Numeric,
 "Continuous",
 Format( "d/m/y h:m:s", 26, 3 ),
 Input Format( "d/m/y h:m:s", 0 ),
 Formula( Informat( Word( 1, :Label, "Z" ), "d/m/y h:m:s" ) )
);
// Remove formula to convert column to static values
dtStack:Timestamp << delete formula;

// Create the Time variable column
dtstack << New Column( "Time",
 Numeric,
 "Continuous",
 Format( "hr:m:s", 17, 3 ),
 Input Format( "hr:m:s", 0 ),
 Formula( Time Of Day( :Timestamp ) )
);

//Create Absolute Time column, uses if>then error checking for Lamda group changing
dtstack << New Column( "AbsTime",
 Numeric,
 "Continuous",
 Format( "Fixed Dec", 12, 1 ),
 Formula(
  If( Row() == 1 | :Lamda != Lag( :Lamda ),
   0,
   Lag( :AbsTime ) + (:Timestamp - Lag( :Timestamp ))
  )
 ),
);
// Remove formula to convert column to static values
dtStack:AbsTime << delete formula;

// Get rid of no longer needed "Label" column
dtStack << delete columns( "Label" );

// Add filename as a column to the data table
dtStack << new column( "Source", character, set each value( filename[i] ) );

// Concatenate to final table
dtFinal << concatenate( dtStack, append to first table(1) );

// Clean up
close( dtStack, nosave );
close( dt, nosave );

// End For() Loop
);</PATH>

回复：如何通过多个csv文件循环脚本

还要检查“多文件导入”。 它会加载CSV文件，如果它们具有相同的列，则将它们串联起来，并且可以选择添加文件名列。

回复：如何通过多个csv文件循环脚本

太棒了 它似乎正在运行，但是在第一个文件后出现错误

“名称未解析：filenam} 1}正在访问或评估'filename'，filename / * ### * /”

 

有什么想法吗？

我还调整了脚本以执行选择文件夹弹出窗口，我认为这不是问题。

// Get a list of files from the directory
dir = Pick Directory( "Select a directory", "C:\Users\folder\");
fileList = Files In Directory( dir );

// Create a skeleton data table to end up being the combined data table
dtFinal = New Table("Final");

// Loop across all of the files found in the directory and read them in
For(i = 1, i <= N Items( fileList ), i++,

dt = open( dir || fileList[i] );