Add values of array to specific place in csv file

Question

I'm far away from being an expert in PowerShell, so I'll be my best to explain here.

I was able to add a column, but now I want to add stuff in a column (already there) using a separate script.

For example, the following CSV file:

WhenToBuyThings,ThingsToBuy
BuyNow         ,Bed
BuyNow         ,Desk
BuyNow         ,Computer
BuyNow         ,Food
BuyLater       ,
BuyLater       ,
BuyLater       ,

I have the array:

$BuyStuffLater = "Books","Toys","ShinnyStuff"

So the end result of the file should look like this

BuyNow         ,Bed
BuyNow         ,Desk
BuyNow         ,Computer
BuyNow         ,Food
BuyLater       ,Books
BuyLater       ,Toys
BuyLater       ,ShinnyStuff

Any help with how to do this in code would be much appreciated. Also, we can't use delimiter ",". Because in the real script some values will have commas.

Fiddle Freak · Accepted Answer · 2015-12-13 04:26:33Z

1

I got it after a few hours of fiddling...

$myArray = "Books","Toys","ShinnyStuff"

$i = 0
Import-Csv "C:\Temp\test.csv" | 
ForEach-Object {if($_.WhenToBuyThings -eq "BuyLater"){$_.ThingsToBuy = $myArray[$i];$i++}return $_} |
Export-Csv C:\Temp\testtemp.csv -NoTypeInformation

All is well now...

answered Dec 13, 2015 at 4:26

Fiddle Freak

2,1378 gold badges51 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cwahls · Accepted Answer · 2015-12-13 05:51:08Z

0

I am new to powershell, too. Here's what I found. This searches and returns all lines that fit. I'm not sure it can pipe.

$BuyStuffLater = "Books","Toys","ShinnyStuff"
$x = 0
Get-Content -Path .\mydata.txt | select-string -pattern "BuyLater" #searches and displays
# Im not sure about this piping. (| foreach {$_ + $BuyStuffLater[$x];$x++} | Set-Content .\outputfile.csv)

This filter will work, though I still have to work on the piping. The other answer might be better.

edited Dec 13, 2015 at 5:51

answered Dec 13, 2015 at 3:21

cwahls

7537 silver badges22 bronze badges

1 Comment

Fiddle Freak Over a year ago

for some reason, it only adds the first value in the array to the column title, and not under the column where the column value is supposed to match the other column value...

Matt · Accepted Answer · 2015-12-13 14:12:18Z

0

I don't see a point to iterating through each object to see if it is a WhenToBuyThings is "BuyLater". If anything what you are doing could be harmful if you run multiple passes adding to the list. It could remove previous things you wanted to by. If "Kidney Dialysis Machine" was listed as a "BuyLater" under WhenToBuyThings then you would overwrite it with dire consequences.

What we can do is build two lists and merge into new csv file. First list is your original file minus any entry where a "BuyLater" has a blank ThingsToBuy. The second list is an object array built from your $BuyStuffLater. Add these lists together and export.

Also there is zero need to worry about using a comma delimiter when using Export-CSV. The data is quoted so commas in data do not affect the data structure. If this was still a concern you could use -Delimiter ";". I noticed in your answer that you did not attempt to account for commas either (not that it matters based on what I just said).

$path = "C:\Temp\test.csv"
$ListOfItemsToBuy = "Books","Toys","ShinnyStuff: Big, ShinyStuff"
$BuyStuffLater = $ListOfItemsToBuy | ForEach-Object{
    [pscustomobject]@{
        WhenToBuyThings = "BuyLater"
        ThingsToBuy = $_
    }
}
$CurrentList = Import-Csv $path |  Where-Object{$_.WhenToBuyThings -ne "BuyLater" -and ![string]::IsNullOrWhiteSpace($_.ThingsToBuy)}
$CurrentList + $BuyStuffLater | Export-Csv $path -NoTypeInformation

Since you have 3.0 we can use [pscustomobject]@{} to build the new object very easily. Combine both arrays simply by adding them together and export back to the original file.

You should notice I used slightly different input data. One includes a comma. I did that so you can see what the output file looks like.

"BuyLater","Books"
"BuyLater","Toys"
"BuyLater","ShinnyStuff: Big, ShinyStuff"

answered Dec 13, 2015 at 14:12

Matt

47k9 gold badges90 silver badges125 bronze badges

2 Comments

Fiddle Freak Over a year ago

Perhaps the values I used were a bad example for the logic I was going for. The real values I could not list here. I will try better next time...

Matt Over a year ago

@FiddleFreak So this logic did not work for you then or it does not work with your real data?

Collectives™ on Stack Overflow

Add values of array to specific place in csv file

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related