Change value in A2 from one value to another

PowerShell Help
1

Good Morning! I have a CSV file which has the value 2380 in cell A2. I need to change this to a different value so that my file will load to a supplementary system. I am attempting to do this with Powershell but nothing seems to work. Any advice is greatly appreciated!

Thanks!
Melissa

2

Please share your code.

3

I would, but I got discouraged and deleted it all. I had imported the CSV, made range a2 = 3680 (which is the new value) and then exported CSV, didnt get an error but value wasnt updated in the file.

I’ll try to redo the code and post again.

4

Here is the code that I got to work, EXCEPT it changes the value of 2380 to 3680 in the WHOLE csv, not just the first row, or cell A2. The issue is, the file has a header, but it has a row above the header that tells the file where to load in my system. This row is required. the number I want to change is the company ID so the file knows where to put the data in the csv in my tables. How do I get this to code to change only the value 2380 to 3680 in the first row of the file or specifically cell A2.

[io.file]::readalltext(“D:\autoloader\Geosouthern_Production\Geosouthern_Production.csv”).replace(“2380”,”3680”) | Out-File D:autoloader\Geosouthern_Production\GeosouthernProd.csv -Encoding ascii –Force
5

If you have a valid CSV file you should treat it as such. It would have been nice if you shared a little more details and maybe some sample data to play with. Especially the first line of you source file. If it’s a certain format PowerShell may ignore it anyway.

I assumed that you CSV file uses a comma as the delimiter.

$Source = 'D:\autoloader\Geosouthern_Production\Geosouthern_Production.csv'
$Output = 'D:autoloader\Geosouthern_Production\GeosouthernProd.csv'

$Content = Get-Content -Path $Source
$NewCSV =
$Content | 
    Select-Object -Skip 1 |
        ConvertFrom-Csv -Delimiter ',' |
            ForEach-Object {
                $_.'company ID' = 3680
            } |
                Out-String
$Content[0],$NewCSV | Out-File -FilePath $Output

… untested … try with test data first!

6

Thanks for the info Olaf! I’m not sure how to send you the sample CSV, so here is a screenshot. they highlighted cell is what I am trying to change. The code you provided, sadly doesn’t work.

Thanks, Melissa

image
image1887×253 21.4 KB

7

… that description doesn’t help at all. Try to keep in mind - I cannot see your screen and I cannot read your mind. :wink:

That’s worst way of sharing sample data. How should I use it? :wink:

You simply copy a few lines of the plain text content of the file and post it here formatted as code. This way we can copy and play with it. :wink:

8

Your CSV is a bit wonky in relation to header rows … This worked for me using this sample data:

COMPANY,2380,1
File1,Field2,Field3,Field4,Field5
1,2,3,4,5
6,7,8,9,0
1,2,3,4,5
6,7,8,9,0

[io.file]::readalltext('c:\temp\test.csv').Replace('2380','3680') | Out-File C:\temp\Test-Out.CSV -Encoding ASCII -Force

PS C:\Users\XXXXXX> type c:\temp\test-out.csv
COMPANY,3680,1
File1,Field2,Field3,Field4,Field5
1,2,3,4,5
6,7,8,9,0
1,2,3,4,5
6,7,8,9,0
9

Amen. :smirk: :stuck_out_tongue_winking_eye: :smiley:

If you have a value of “2380” in any other cell of the csv you will mess it up. :wink:

Assumed the CSV file ALWAYS has the values shown in the first row in the first row and is comma separated you could do it like this:

$Source = 'D:\autoloader\Geosouthern_Production\Geosouthern_Production.csv'
$Output = 'D:autoloader\Geosouthern_Production\GeosouthernProd.csv'

$Content = Get-Content -Path $Source
"COMPANY,3680,1", $Content[1..($Content.count)] | Out-File -FilePath $Output
10

Exactly this! Thank you!! That worked awesomely! And thank you for the tips on how to share the information on here. I’m still new to the forum but truly appreciate your help!