How to Copy Text in a .CSV file using Power Shell

Looking to copy and replicate text in a .CSV file on a server and I found this piece of code which I was able to modify and use. It seems to work on my PC which has office installed but not on the server where it needs to run. My assumption is that it is because the server doesn't have office Installed.

Code:

#Get list of files
$Files = Get-ChildItem \\FilePath\$Store.csv

#Launch Excel
$XL = New-Object -ComObject Excel.Application

ForEach($File in $Files){
    #Open the file and get the Data sheet
    $WB = $XL.Workbooks.Open($File.Fullname)
    $Sheet = $WB.Worksheets.Item($Store)

    #Get the value from B2
    $Branch = $Sheet.Rows.Item(2).Cells.Item(2).Value2

    #Loop through rows, skipping the first 13, and set the branch value
    $($Sheet.UsedRange.Rows) | Select -Skip 13 | ForEach{ $_.Cells.Item(2).Value2 = $Branch }

    #Save and close the file
    $WB.Save()
    $WB.Close($true)
}

#Close Excel
$XL.Quit()

Is there an other way to manipulate text within the same file similar to above?

Solution 1:

Should be relatively easy. PowerShell has the Import-Csv Cmdlet:

The Import-Csv cmdlet creates table-like custom objects from the items in CSV files. Each column in the CSV file becomes a property of the custom object and the items in rows become the property values. Import-Csv works on any CSV file, including files that are generated by the Export-Csv cmdlet.

If the first row of your .csv isn't a header row, you specify them with the -Header parameter.

make your import part of a variable assignmet:

$CsvData = Import-Csv -Path 'c:\Path\To\File'

Your first row of data is $CsvData[0], columns are referenced by the header assigned to the column:

$CsvData[0].Column1HeaderName

I'm rusty on excel methods, if you need help with the data manipulation, edit your question with more details, e.g. is the first column row headers or data?