Copy files from CSV to existing Excel in Powershell

I have a CSV file with some data. For example:

         CSV                ------>            EXCEL
    Name,Address,Phone                  Name,Address,Phone
    Nick,aaa,9990008989                 Nick,aaa,NULL
    Josh,bbb,8889990909                 Josh,bbb,NULL

I would like to copy each phone number to that specific name from my csv file to the excel one. How would that be possible in powershell? Thank you

March 24th, 2015 4:40pm

What have you tried?  You can use an update query.  You could use the Excel object model.  Yui could use VBScript, VBA, PowerSHell or many other methods.

Look in the repository for numerous examples of updating Excel.

Free Windows Admin Tool Kit Click here and download it now
March 25th, 2015 10:54am

Here is an example of how to update a spreadsheet based on a CSV:

$fileName = 'C:\temp2\update_tester.xlsx'
$conBase='Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0;HDR=YES";Persist Security Info=False'
$connectionString=$conBase -f $filename
$conn = new-object System.Data.OleDb.OleDbConnection($connectionString)
$conn.Open()
$cmd = $conn.CreateCommand()
$items=Import-Csv items.csv
foreach ($item in $items) {
    $cmd.CommandText = 'UPDATE [Sheet1$] SET phone=[{0}] where Name=[{1}]' -f $item.Phone, $item.Name
    $cmd.ExecuteNonQuery()
}
March 25th, 2015 4:16pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics