Jacob L
asked on
powershell multiply numbers in a csv file
HELP!!!
Using powershell how can i multiply a number by -1? In attached file that looks like this. I need to multiply the numbers after the 3rd comma by -1. So if its a negative, make it a positive, and positive a negative.
H,20170620230011,1,19551,2 0170620230 011
004,2017-06-20 00:00:00,0001450002100,-1, SupplyReje cts,100003 7,EA
004,2017-06-20 00:00:00,0001500007553,-1, SupplyReje cts,100003 8,EA
004,2017-06-20 00:00:00,0001800000210,-8, SupplyReje cts,100003 8,EA
004,2017-06-20 00:00:00,0001800000216,-1, SupplyReje cts,100003 8,EA
004,2017-06-20 00:00:00,0001800000235,12, SupplyReje cts,100003 8,EA
resulting output should look like this.
H,20170620230011,1,19551,2 0170620230 011
004,2017-06-20 00:00:00,0001450002100,1,S upplyRejec ts,1000037 ,EA
004,2017-06-20 00:00:00,0001500007553,1,S upplyRejec ts,1000038 ,EA
004,2017-06-20 00:00:00,0001800000210,8,S upplyRejec ts,1000038 ,EA
004,2017-06-20 00:00:00,0001800000216,1,S upplyRejec ts,1000038 ,EA
004,2017-06-20 00:00:00,0001800000235,-12 ,SupplyRej ects,10000 38,EA
Using powershell how can i multiply a number by -1? In attached file that looks like this. I need to multiply the numbers after the 3rd comma by -1. So if its a negative, make it a positive, and positive a negative.
H,20170620230011,1,19551,2
004,2017-06-20 00:00:00,0001450002100,-1,
004,2017-06-20 00:00:00,0001500007553,-1,
004,2017-06-20 00:00:00,0001800000210,-8,
004,2017-06-20 00:00:00,0001800000216,-1,
004,2017-06-20 00:00:00,0001800000235,12,
resulting output should look like this.
H,20170620230011,1,19551,2
004,2017-06-20 00:00:00,0001450002100,1,S
004,2017-06-20 00:00:00,0001500007553,1,S
004,2017-06-20 00:00:00,0001800000210,8,S
004,2017-06-20 00:00:00,0001800000216,1,S
004,2017-06-20 00:00:00,0001800000235,-12
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Line 4 starts the output file by reading the first line from the input file, and writing it to the output file.
Line 5 reads the input file, skips the first line, converts each row to a PSObject; the (1..7 | ForEach-Object {"Col$($_)"}) creates an array of strings Col1-Col7.
Line 6 does the job.
Line 7 writes the changed object back to the pipeline.
Line 8 takes each row generated in the ForEach loop, converts it to csv, skips the first (header) line, removes the double quotes that ConvertTo-Csv automatically adds around each field, and adds the result to the output file.
Line 5 reads the input file, skips the first line, converts each row to a PSObject; the (1..7 | ForEach-Object {"Col$($_)"}) creates an array of strings Col1-Col7.
Line 6 does the job.
Line 7 writes the changed object back to the pipeline.
Line 8 takes each row generated in the ForEach loop, converts it to csv, skips the first (header) line, removes the double quotes that ConvertTo-Csv automatically adds around each field, and adds the result to the output file.
ASKER
Again thank you so much.
ASKER