CODEWITHSUNDEEP
powershellcsv
user1681664
user1681664

Reputation: 1811

Powershell find and replace cell value within csv

I am trying to parse a column named price within a csv file.

csv price column

within this column, I need to find the values that have a "-" in them. In the example above, you can see that there are two cases (132-24.5 and 158-25). I then need to replace that value in the csv file using the formula:

(number on the left side of dash + number on the right side of dash/32)

so 132-24.5 would be 132+24.5/32 = 132.765625

I've already written and used the code for a year in matlab, but I need to convert it to PowerShell and I am so lost on the way powershell works. Please guide me.

Matlab code below:

[~,~,data] = xlsread(['C:\Users\Me' filelist(3,:)]);

for x = 2:length(data(:,4))
bondfind = strfind(num2str(cell2mat(data(x,4))),'-');
 if ~isempty(bondfind)
    priceString = num2str(cell2mat(data(x,4)));
    price = str2num(priceString(1:bondfind-1)) + str2num(priceString(bondfind+1:end))/32;
    data(x,4) = num2cell(price);
 end
end

xlswrite(['C:\Users\Me\' filelist(3,:)],data);

Upvotes: 0

Views: 896

Answers (1)

Theo
Theo

Reputation: 61028

So, if your file looks anything like this:

Article,Price
Something,132-24.5
Stuff,1371.8
More Stuff,587.3
Another thing, 158-25

You can do this to do the transformations on the values in column Price:

$data = Import-Csv -Path 'D:\Test\TheFile.csv'
foreach ($item in $data) {
    if ($item.Price -match '-') {
        $left, $right = [double[]]($item.Price -split '-')
        $item.Price = $left + $right / 32
    }
}

# output on screen
$data

# save as (new) CSV file
$data | Export-Csv -Path 'D:\Test\TheNewFile.csv' -NoTypeInformation

Output on screen:

Article            Price
-------            -----
Something     132.765625
Stuff             1371.8
More Stuff         587.3
Another thing  158.78125

Upvotes: 1

Related Questions