62192_zh-CN

liliconcom

如何从动态数据导入中自动更新带文件名的行？

我正在编写一个脚本，将文件夹中的多个文件导入到单个数据表中。我创建了一个列为USP_ID的列，其中我希望文件名出现在每一行中。尽管数据导入正确，但此列中仅显示最终导入的Excel文件的名称，而我希望每个文件名显示在与其各自数据对应的行中。

 

我已经在下面列出了我的脚本，我相信它会引起我的问题，虽然我没有把它排序的线索。任何建议将不胜感激！

Names Default To Here( 1 );
Clear Symbols();
filelist = {};   
filepath = Munger( Pick Directory( "Select the desired file directory" ), 1, "/", "" );
prefilelist = Files In Directory( filepath );
n2 = N Items( prefilelist );
For( i2 = 1, n2 >= i2, i2++,
 file = (prefilelist[i2]);
 If( Item( -1, prefilelist[i2], "." ) == "xlsm" | Item( -1, prefilelist[i2], "." ) == "xlsx",
  Insert Into( filelist, file ),
  Show( file )
 );
);
nf = N Items( filelist ); 
cc_dt = New Table( "Combined data " );
cc_dt << New Column( "USP_ID", character, nominal );
For( iii = 1, iii <= nf, iii++, 
 filenow = (filelist[iii]);
 fileopen = (filepath || filenow);
 dt = Open( fileopen );
 dt << Run Formulas();
 cc_dt << Concatenate( Data Table( dt ), append to first table );
 cc_dt:USP_ID << set each value( filenow ); // having issues with this I think?
 Close( dt, nosave );
);
colList = cc_dt << get column names( character, string );
For( i = 1, i <= N Items( colList ), i++,
 selRows = cc_dt << get rows where(
  Column( cc_dt, colList[i] )[Row()] == "NaN" | Column( cc_dt, colList[i] )[Row()] == "0"
 );
 If( N Rows( selRows ) > 0,
  Column( cc_dt, colList[i] )[selRows] = "0";
  Column( cc_dt, colList[i] ) << data type( "numeric" ) << modeling type( "continuous" );
 );
);

Re：如何从动态数据导入中自动更新带文件名的行？

嗨Melanie， 

感谢您的帮助，虽然我无法按照我想要的方式进行推荐，但我发现通过在连接它们之前将Source列分配给数据表会： 

nf = N Items( filelist ); //number of items in the working list
cc_dt = New Table( "Combined fermenter data " );
cc_dt << New Column( "Source", character, nominal );
cc_dt << New Column( "USP_ID", character, formula( Substr( :Source, 1, 7 ) ) );
For( iii = 1, iii <= nf, iii++, //this starts the first loop
 filenow = (filelist[iii]);
 fileopen = (filepath || filenow);
 dt = Open( fileopen );
 dt << Run formulas();
 dt << new column("Source", character, set initial data(filenow));
 cc_dt << Concatenate( Data Table( dt ), append to first table );
 close(dt, nosave);
);

Re：如何从动态数据导入中自动更新带文件名的行？

cc_dt<SPAN class="token punctuation">:</SPAN>USP_ID <SPAN class="token operator"><</SPAN><SPAN class="token operator"><</SPAN><SPAN class="token messages"> set each value</SPAN><SPAN class="token punctuation">(</SPAN> filenow <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>

设置每个值将列中的所有值设置为常量。因此，对于每个循环，您将该列中的每个单元格设置为当前文件名。您只需要设置刚刚连接到文件名的表中的“新”单元格。也许是这样的？

 

cc_dt <<选择位置（cc_dt：USP_ID ==“”）; //选择空单元格，假设前面的单元格都有其文件名值

selrows = cc_dt << Get Selected Rows（）; //获取所选的行号

cc_dt：USP_ID [selrows] = filenow; //将该列中的那些行设置为文件名

 

HTH，

梅兰妮