none
Add a value to a column in an existing csv file RRS feed

  • Question

  • I have a CSV file with some columns/cells that are populated and others that aren't.  Similar to the following where NULL indicates a blank cell.

    Col1 Col2 Col3 Col4
    ValueA1  NULL  ValueA3 ValueA4
    ValueB1  NULL  ValueB3 ValueB4

    Is there a way I can use Powershell to directly add a value to a Col2 cell, or overwrite a value in another cell in the file?

    Tuesday, March 8, 2016 10:01 PM

Answers

  • $csv = @"
    Col1,Col2,Col3,Col4
    ValueA1,,ValueA3,ValueA4
    ValueB1,,ValueB3,ValueB4
    "@
    
    $data = ConvertFrom-Csv $csv
    
    $data[0].Col2 = 'ValueA2'
    
    $data | where Col1 -eq 'ValueB1' | foreach {
        $_.Col2 = 'ValueB2'
    }
    
    $data | ConvertTo-Csv -NoTypeInformation
    "Col1","Col2","Col3","Col4"
    "ValueA1","ValueA2","ValueA3","ValueA4"
    "ValueB1","ValueB2","ValueB3","ValueB4"
    

    Tuesday, March 8, 2016 10:29 PM

All replies

  • Import-Csv Yourfile|%{$_.Col2='new data'}


    \_(ツ)_/

    • Proposed as answer by BASATI Wednesday, March 9, 2016 3:42 AM
    • Unproposed as answer by Matt Bromberger - MSFT Wednesday, March 9, 2016 3:00 PM
    Tuesday, March 8, 2016 10:18 PM
  • $csv = @"
    Col1,Col2,Col3,Col4
    ValueA1,,ValueA3,ValueA4
    ValueB1,,ValueB3,ValueB4
    "@
    
    $data = ConvertFrom-Csv $csv
    
    $data[0].Col2 = 'ValueA2'
    
    $data | where Col1 -eq 'ValueB1' | foreach {
        $_.Col2 = 'ValueB2'
    }
    
    $data | ConvertTo-Csv -NoTypeInformation
    "Col1","Col2","Col3","Col4"
    "ValueA1","ValueA2","ValueA3","ValueA4"
    "ValueB1","ValueB2","ValueB3","ValueB4"
    

    Tuesday, March 8, 2016 10:29 PM
  • Thank you.  That gives me a way to change data in a row based on one of the values in that row.  Can I directly edit a cell in the same way, check if column1/row2 is of valueB1 and then add a value to column2/row2 directly? It seems writing back to the file would end up appending a line, or just overwriting the file rather than editing a particular line.
    Wednesday, March 9, 2016 3:08 PM
  • With computer files that are of type text we cannot directly edit a column.  There are no columns there is only text.

    Somewhere there are some books on basic computer use  that you should probably read to get an understanding of what files are and how they work in a computer system.

    In programs that allow direct editing the whole file is still replaced.  It is only a database that can be edited in pieces.

    Even in Excel when you close a file it is saved to disk and the backup copy (renamed original) is deleted. 

    Some programs do directly edit files but they are specially designed to do that.  PowerShell does not do that.

    We can use the ACE drivers to open a CSV as a datatable and directly edit columns and then just quit as DB type updates are immediate and the ACE driers know how to edit the file.


    \_(ツ)_/

    Wednesday, March 9, 2016 3:18 PM
  • Here is an example of a direct edit to a file using the ACE drivers:

    <#	
    	.NOTES
    	===========================================================================
    	 Created with: 	SAPIEN Technologies, Inc., PowerShell Studio 2014 v4.1.75
    	 Created on:   	3/25/2015 3:59 PM
    	 Created by:   	jrv
    	 Organization: 	Designed Systems & Services
    	 Filename:     	
    	===========================================================================
    	.DESCRIPTION
            This uses an OleDb connection through teithe rJet or ACE to update items in an Excel sheet
    
    		A description of the file.
            CSV FILE for test:
            Name, Address, Phone
            John Smith, 333 Safe Rd, 999-999-9999
            etc....
    
            Excel Sheet1
            Name, Address, Phone
            ...
        
    #>
    Param(
        $excelFile='update_tester.xlsx',
        $csvFile='names.csv'
    )
    
    $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
    
    # we are going to update phones
    foreach ($item in $items) {
        $cmd.CommandText = 'UPDATE [Sheet1$] SET Phone=[{0}] where Name=[{1}]' -f $item.Phone, $item.Name
        $cmd.ExecuteNonQuery()
    }
    
    
    

    It is an Excel file but just change the connect stri ng and it will edit CSV files.


    \_(ツ)_/

    Wednesday, March 9, 2016 3:27 PM