Import .csv and save with new values in powershell

Question

I have a .csv template with an empty column called URL. I would like to update it every time I get a value for its user.

Here is what I have so far:

$file = "c:\Users\user\Documents\users.csv"

Import-Csv $file  -Delimiter ',' | % {

     $_.URL = $generated_url ; $_

} | Export-Csv "c:\Users\user\Documents\users_tmp.csv" -Delimiter ',' -notype

This updates all of the cells with the same value. Is there a workaround? Thank you

Answer 1

I assume you want to update just one row which corresponds to the current user.

To do that you have to select the row that has to be updated using the where-object clause

param(
 $username
)

$file = "c:\Users\user\Documents\users.csv"

Import-Csv $file  -Delimiter ',' | Where {$_.username -like $username} | % {

 $_.URL = $generated_url ; $_

} | Export-Csv "c:\Users\user\Documents\users_tmp.csv" -Delimiter ',' -notype

To save the CSV as excel as asked in the comment below, the best option is to save as CSV as above. Then use Excel COM libraries to convert it.

Example

$Excel = New-Object -comobject Excel.Application
$Excel.Visible = $False
$Excel.displayalerts=$False
$Workbook = $Excel.Workbooks.Open($CSVFileName)
$Workbook.SaveAs($Excelfilename,1)
$Excel.Quit()
If(ps excel){
  kill -name excel
}