0

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.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
UnladenSwallow
  • 23
  • 3
  • For the sake of clarity, I'm looking for a process to not only to negate the values, but to do the task from end to end wherein the process will open a file, negate the values, and save the file. – UnladenSwallow Oct 11 '16 at 16:06

2 Answers2

1

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.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
0

You can credit a button that's on your sheet anywhere you want that can run this macro. Assuming you know the basics of a macro/VBA (if you don't let me know) and A1 = -1 (can change in the below script).

Range("A1").Select
ActiveCell.FormulaR1C1 = "-1"
Range("B1:F1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select
Selection.Copy
Range("B1:F1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
    SkipBlanks:=False, Transpose:=False
Joshua
  • 31
  • 4
  • I'm not immediately familiar with Macro/VBA, but I fathom that I can do some googling to figure that bit out. I might have a snag, though. Is there a way to dynamically declare the range? That is to say....the number of rows varies from file to file. Sometimes there may only be ten rows, while other times there may be tens of thousands. – UnladenSwallow Oct 11 '16 at 15:50
  • So, a quick update. I've successfully recorded a macro, and added it to the ribbon. While that will cut down on the time to manually process files, it's still a manual process. Is there an ideal way to systematically do something like the following: Define folder with files to negate Automatically open every file, apply the macro (or VBA script) Save the modified files – UnladenSwallow Oct 11 '16 at 16:03