Import Master CSV, then save the updated master CSV and an additional CSV

PowerShell Help
1

I have the following Code

I Need to modify the $DB_Master CSV and save it back, and i also need to save the additional CSV file with only the modified records, and one of the fields has different value from the master CSV

What seems to be happening is

Line 14: $DB_Additional_CSV += $DB_Master[$DB_Master_Item_Index]
Up to this point it seems to be fine i got one of the CSV entries i need inside variable $DB_Additional_CSV

Next, in line 16, i do a $DB_Master[$DB_Master_Item_Index].UPN = $null because this is how i need this field to be populated in the $DB_Master csv file

Problem is, when i run line 16, the change to the field is performed in $DB_Master but ALSO in $DB_Additional_CSV
is like $DB_Additional_CSV is merely a link to one of the records in $DB_Master, if i change the master variable, i also change $DB_Additional_CSV

How can i run line 14 in a way that $DB_Additional_CSV becomes a totally independent variable?

ForEach ($NumberToBeRemoved in $NumberToBeRemovedList)
{

$NumberAssignment_Remove_PhoneNumber_Assignment_Tbl_Entry = (Import-Csv -Path ($ScriptPath + "DB-Number-Assignment.csv")) |Where-Object {$_.PhoneNumber -eq ($NumberToBeRemoved -replace '\+','')}
    if ($NumberAssignment_Remove_PhoneNumber_Assignment_Tbl_Entry -ne $null)
    {
    $null = read-host -prompt 'hit enter to continue'
    $NumberAssignment_Remove_PhoneNumber_Assignment_Tbl_Entry.PhoneNumberChangeDateTime = (Get-Date -Format $DateTimeFormat)
    $DB_Master = (Import-Csv -Path ($ScriptPath + "DB-Number-Assignment.csv"))
    $DB_Master_Item = (Import-Csv -Path ($ScriptPath + "DB-Number-Assignment.csv")) |Where-Object {$_.PhoneNumber -eq ($NumberAssignment_Remove_PhoneNumber_Assignment_Tbl_Entry.PhoneNumber)}
    $DB_Master_Item_Index = $DB_Master.PhoneNumber.IndexOf($DB_Master_Item.PhoneNumber)
    $DB_Master[$DB_Master_Item_Index].Notes = $null

    $DB_Additional_CSV += $DB_Master[$DB_Master_Item_Index]    

    $DB_Master[$DB_Master_Item_Index].UPN = $null
    }

    $NumberAssignment_Remove_Path = $ScriptPath + 'Export\' + 'Number-Assignment-RemoveBatch-' + (Get-Date -format s).Replace(":","-") + '.csv'
    
    $DB_Additional_CSV  |Export-Csv -Path $NumberAssignment_Remove_Path -NoTypeInformation
    $DB_Master|Export-Csv -Path ($ScriptPath + "DB-Number-Assignment.csv") -NoTypeInformation

}
2

Without actually digging into your code I’d like to suggest a general tip to make your code easier to read and maintain and much faster:

Since PowerShell is quite slow when it comes to file system operations you should do ALL of the reading and writing of the CSV files OUTSIDE of the loop(loops). This way you’d need instead hundrets or thousands of read and write operations only 3 or 4.

If you need further assistance with this you may share some sample data from before and the state you want to have after the modification (just a few lines formatted as code so we can copy and play around with it) :wink:

3

Hi Olaf
thanks for the advice i know the code is far from optimal but i need to support it, and i am having that issue i described with $DB_Additional_CSV not being an independent variable

let me find how to upload the csv file here one moment
Edit: CSV File here

4

Since you did not share how you populate the variable $NumberToBeRemovedList I picked some random numbers to be able to show what I mean.

$InputData = Import-Csv -Path .\DB-Number-Assignment.csv
$NumberToBeRemovedList = 61893637004, 61893637006, 61893637008, 61893637014, 61893637016, 61893637034

$Result =
foreach ($item in $InputData) {
    if ($item.PhoneNumber -in $NumberToBeRemovedList) {
        $item.PhoneNumber = ''
    }
    $item
}

$Result | 
    Select-Object -First 40 | 
        Format-Table -AutoSize

After running this code you have the modified data in the variable $Result and you can do whatever you need to do with it. The output of the first 40 elements is just to have a visual proof if the code does what it is supposed to do. :wink:

If it’s still unclear for you don’t hesitate to ask further. :wink:

1 Like
5

Thanks for yiour response Olaf

I believe i was not clear in my initial request, let me demonstrate the issue using your code example:

$InputData = Import-Csv -Path .\DB-Number-Assignment.csv
$NumberToBeRemovedList = 61893637004, 61893637006, 61893637008, 61893637014, 61893637016, 61893637034

$Result =
foreach ($item in $InputData) {
    if ($item.PhoneNumber -in $NumberToBeRemovedList) {
        $item
    }
    
}

#If i do any change to the master variable
$InputData[$InputData.IndexOf($Result[0])].UPN = 'test'

#This change is somehow reflected on the child variable
$Result[0]

In my case i need to save 2 different copies of the CSV, so my plan was to populate the 2 different csv variables inside the loop. and after the loop i save the
Master $InputData and also the small subset $Result variable producing 2 separate csv files

The issue i am having is whenever i change the master csv it reflects the child CSV and vice versa, they are somehow linked, i would like to unlink them so i can handle them as individual variables

6

I believe I wasn’t making myself clear … :wink: instead of fighting against PowerShell you should use it to get your task done. Manipulating the original data while you’re iterating over them is a bad idea.

Why? What’s the advantage of that approach? :wink:

With this code you only output the rows from the original input data when the PhoneNumber is contained in the list of $NumberToBeRemovedList and collect them in the variable $Result. If that’s what you’re looking for you can save it to a CSV file and you’re done with this part of the task. But … that does NOT change the input data inside $InputData!!

If the code does do what you want don’t use it. :wink:

7

I was triying to avoid to run through the csv file in 2 separate loops, i guess i was fighting powershell because i believed there was something trivial i could do to detach these variables :smile:

8

Hmmm … that’s actually exactly my point. You read the input CSV file ONCE in advance and work with the data you saved in a variable. :point_up_2:t4: :point_up_2:t4:

But anyway - if the task you want to accomplish makes it necessary to process your data twice you process your data twice. :man_shrugging:t4: :smirk: That’s why we use computers - they do the boring and repetitive tasks for us. :wink:

1 Like