自动化过程，将制表符分隔文件中的值乘以-1，以对其进行否定 [英] Automating process to multiply values in tab delimited file by -1 to negate them

问题描述

我已经在Excel中手动处理了大量的文件。我做了一些搜索，但没有找到一个最终的最佳做法，我如何以自动的方式实现这个过程。

我的手动过程如下：

我有一个.tab（制表符分隔）文件。每行总共有8个列。我需要取消每行最后5列的数值。

我一直在做的

• 在Excel中打开文件


• 在任何空白单元格中键入-1。复制


• 突出显示最后5列中的数据和rarr;右键单击→粘贴特别倍增输入


• 然后保存文件。

不知道如果最好的方法将是PowerShell或替代脚本，所以我想联系社区，看看别人可能有什么建议。谢谢你的时间。

解决方案

在PowerShell中，您可以使用 Import-Csv 用于阅读和 Export-Csv 用于编写字符分隔的文件。两个cmdlet提供了一个参数 -Delimiter ，允许您指定分隔符字符：

  $ csv = Import-Csv'C：\path\to\input.csv'-Delimiter`t
 $ csv | Export-Csv'C：\path\to\output.csv'-Delimiter`t-NoType 
  

通过 Import-Csv 导入CSV可以给出自定义对象的列表，其中CSV中的每个字段都表示为对象的属性。您可以通过在将数据写回文件之前修改属性的值来修改字段。为此，您可以采取以下两种方法之一：

• 将CSV完全读入变量，更新字段想要循环修改，然后将数据导出回到一个文件：

    $ csv = Import-Csv ... 
   foreach（$ row in $ csv）{
   [int] $ row.B * = -1 
   [int] $ row.F * = -1 
  } 
   $ csv | Export-Csv ... 
    




• 将CSV读取到管道中，并替换所需的字段通过计算的属性进行修改：

    Import-Csv ... | 
   Select-Object -Include *，@ {n ='B'; e = { -  $ _。B}}，@ {n ='F'; e = { -  $ _。F}} B，F | 
   Export-Csv ... 
    

  请注意，为了工作，您必须单独使用输入和输出文件，或将 Import-Csv 语句放在括号中。否则 Export-Csv 将失败，因为它不能写入CSV，而 Import-Csv 仍在读取

I've been manually processing a large amount of files in Excel. I've done some searching but haven't found a definitive best practice as to how I can achieve this process in an automated fashion.

My manual process is as follows:

I have a .tab (Tab-delimited) file. There are a total of 8 "columns" for each row. I need to negate the numerical values in the last 5 columns of every row.

What I've been doing

• Open the file in Excel
• Type a -1 in any blank cell. Copy it
• Highlight the data in the last 5 columns → right-click → Paste Special → Multiply → Enter
• Then save the file.

I'm not sure if the best approach for this is going to be PowerShell or an alternative script, so I wanted to reach out to the community to see what advice others may have for this. Thank you for your time.

解决方案

In PowerShell you can use Import-Csv for reading and Export-Csv for writing character-delimited files. Both cmdlets provide a parameter -Delimiter that allows you to specify the delimiter character:

$csv = Import-Csv 'C:\path\to\input.csv' -Delimiter "`t"
$csv | Export-Csv 'C:\path\to\output.csv' -Delimiter "`t" -NoType

Importing a CSV via Import-Csv gives you a list of custom objects where each field from the CSV is represented as a property of the object. You modify the fields by modifying the values of the properties before writing the data back to a file. To do this you can take either of the following two approaches:

• Read the CSV completely into a variable, update the fields you want to modify in a loop, then export the data back to a file:

  $csv = Import-Csv ...
  foreach ($row in $csv) {
    [int]$row.B *= -1
    [int]$row.F *= -1
  }
  $csv | Export-Csv ...
  

• Read the CSV into a pipeline and replace the fields you want to modify via calculated properties:

  Import-Csv ... |
    Select-Object -Include *,@{n='B';e={-$_.B}},@{n='F';e={-$_.F}} -Exclude B,F |
    Export-Csv ...
  

  Note that for this to work you must either use separate input and output files, or put the Import-Csv statement in parentheses. Otherwise Export-Csv would fail, because it can't write to the CSV while Import-Csv is still reading from it.

这篇关于自动化过程，将制表符分隔文件中的值乘以-1，以对其进行否定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！