Data Rename

This topic contains 12 replies, has 3 voices, and was last updated by Profile photo of Bob McCoy Bob McCoy 1 year, 9 months ago.

  • Author
    Posts
  • #27888
    Profile photo of Ernesto Lombardi
    Ernesto Lombardi
    Participant

    Can power shell help me rename a bunch of folders.
    I need to compare a notepad document in each folder
    to a spreadsheet of names with new ids
    then
    rename the folder with the new ids
    some folders do not have a notepad document and I would prefer to skip
    over 5000 folders... really do not wanna do via hand

  • #27894
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    The short answer is yes. But you really need to explain what you mean by "I need to compare a notepad document in each folder to a spreadsheet of names with new ids".

  • #27899
    Profile photo of Ernesto Lombardi
    Ernesto Lombardi
    Participant

    I have a list of folders incrementing from 1-5000

    What I have -> What I require
    001—————–> 41A
    002—————–> 41B (example if first/last was James/Smith)
    ...
    Some folders contain a data text file with values
    FirstName=John
    LastName=Smith
    With other random information
    ——–
    The SpreadSheet or .cvs file has
    41A,John Smith
    42B,James Smith
    ...
    Each folder contains information
    so I would prefer to rename the parent folder that the Data file resides
    Each Data File in each folder has the same name and extension.
    I need to suck out the names from the Data file per folder
    Compare with the CVS file
    Rename the Parent folder

  • #27929
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    Is that data file consistently named in every folder, and if so, what is its name? And what are the field names in your CSV file?

  • #27933
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    Assumption for the code example: Your rename CSV file looks something like this ...

    ID,Name
    41A,John Smith
    42B,James Smith
    45C,Bob McCoy
    

    Assumption for the code example: Your data file sprinkled throughout the directories is called data.ini.

    Given those assumptions, as well as standard caveats (like rename the paths to match your directory structure), this should work. And of course, remove the -WhatIf switch when you are confident the script is doing what you want, nothing more, nothing less.

    # set the pattern for looking at the data fields
    $pattern = [regex]"^(?'item'First|Last)=(?'data'.+$)"
    # set the root and the pattern for the file search
    $dirs = Get-ChildItem -Directory -Path C:\Ephemeral\????
    
    # build lookup table
    $list = Import-Csv -Path C:\Ephemeral\renamelist.csv
    $lookup = $list | Group-Object -AsHashTable -AsString -Property Name
    
    # start looking through the directories
    foreach ($dir in $dirs)
    {
        $dataPath = Join-Path -Path $dir -ChildPath PData.dat
        # did we find the data file?
        if (Test-Path -Path $dataPath)
        {
            Write-Verbose "Found a data file at $dataPath"
            # read the data file
            $data = Get-Content -Path $dataPath
            # look at each line for the fields of interest
            foreach ($line in $data)
            {
                if ($line -match $pattern)
                {
                    Set-Variable -Name $Matches['item'] -Value $Matches['data']
                }
            } # end of looking at all the lines in a file
            # lookup the ID matching the name
            try 
            {
                $newName = ($lookup[$("$first $last")]).ID
                Write-Verbose "$first | $last | $newname"
                # reanme the directory
                Rename-Item -Path $dir -NewName $newName -WhatIf
            }
            catch
            {
                Write-Error -Message "An error occured for $($dir.FullName)"
            }
        }
    }
    
  • #27934
    Profile photo of Ernesto Lombardi
    Ernesto Lombardi
    Participant

    I really appreciate the response!

    The data file remains same PData.Dat (reads in notepad fine)
    My CVS I have narrowed down to 2 columns that simply start with entries
    ID , Name
    I can add Code,Name at the beginning if that will help

    I am curious where do we take
    FirstName=John
    LastName=Smith
    and combine them to make Name. so I can compare the full name to the csv

    Also This is ancient Egyptian to me

    # set the pattern for looking at the data fields
    $pattern = [regex]"^(?First|Last)=(?.+$)"

    I will test this code in the next 48 hours, I have some life to take care of.
    Thanks a lot for the input, it looks very promising.

  • #27936
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    I have edited the code above to take into account the data file name of PData.dat and to change the column heading in the CSV file to ID and Name.

    The "Egyptian bit" is a regular expression or RegEx. It also changed in the edit as the site text editor did not like the brackets for the named captures. Basically the RegEx defined here as $pattern is what is used by the -match operator to look for the "First" and "Last" keywords in your data file. Whenever I find the "First" or "Last" keyword, I use the set-variable command to set $first or $last to whatever data I found on the other side of the "=".

    Early on in the script I built a lookup table based on the data in the CSV file. I built it in such a way as to key it off the Name field. So later when I have the first and last names from the PData.dat file, I put them together with a space in between, and use that as the key to the lookup table which returns the corresponding ID to the $newName variable. Once I have the new name, renaming the directory is simple.

    By the way, regular expressions have been around for years (long before PowerShell) and they have their own language of sorts (it's cryptic on a good day). They are supported on many platforms and in many languages and utilities. PowerShell and RegEx is an unbeatable combination for text manipulation. You by no means need RegEx for every PS script. I used it here because (from my viewpoint) it made it easy to extract the "First" and "Last" data items.

    Another reminder — this script will not rename anything until you remove the -WhatIf switch from the Rename-Item line.

  • #27967
    Profile photo of Ernesto Lombardi
    Ernesto Lombardi
    Participant

    PS C:\> $data = Get-Content -Path $dataPath
    Get-Content : Cannot find path 'C:04\PData.dat' because it does not exist.
    At line:1 char:17
    + $data = Get-Content -Path $dataPath
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (C:04\PData.dat:String) [Get-Content], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand

    PS C:\>

    The file does exists in the folder

  • #27968
    Profile photo of Ernesto Lombardi
    Ernesto Lombardi
    Participant

    >

  • #28118
    Profile photo of Ernesto Lombardi
    Ernesto Lombardi
    Participant

    I think I am very close.

    Set-Variable : Cannot bind argument to parameter 'Name' because it is null.
    At line:5 char:47
    + Set-Variable -Name $Matches['item'] -Value $Matches[' ...
    + ~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidData: (:) [Set-Variable], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.SetVariableCommand

    I am unsure what I am missing here

  • #28119
    Profile photo of Ernesto Lombardi
    Ernesto Lombardi
    Participant

    Here is my Pdata.Dat

    [PatData]
    InternalID=1111
    ID=000
    IsActive=1
    IsPatient=1
    IsRP=0
    Title=
    FirstName=cob
    MidName=
    LastName=Smith
    Name=
    Sex=
    BirthDate=

    I think this line I am having issues with

    $newName = ($lookup[$("$first $last")]).ID

    This is my full code sofar

    $pattern = [regex]"^(?'item'First|Last)=(?'data'.+$)"

    $dirs = Get-ChildItem -Directory -Path C:\Data\
    $list = Import-Csv -Path C:\Data\Name.csv
    $lookup = $list | Group-Object -AsHashTable -AsString -Property Name
    Write-host $pattern

    foreach ($dir in $dirs){

    $dataPath = Join-Path -Path $dir -ChildPath PData.dat
    # did we find the data file?
    if (Test-Path -Path $dataPath)
    {
    Write-host "Found a data file at $dataPath"
    # read the data file
    $data = Get-Content -Path "C:\Data\$dataPath"

    # look at each line for the fields of interest
    foreach($line in $data)
    {
    if ($line -match $pattern)
    {
    Set-Variable -Name $Matches['item'] -Value $Matches['data']

    }

    }
    }
    # end of looking at all the lines in a file
    # lookup the ID matching the name
    try
    {
    $newName = ($lookup[$("$first $last")]).ID
    Write-host "$first | $last | $newname"
    # reanme the directory
    Rename-Item -Path $dir -NewName $newName
    }
    catch
    {
    Write-Error -Message "An error occured for $($dir.FullName)"
    }

    }

  • #28131
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Your .DAT file has "FirstName=" and Bob's regex pattern is looking for "First=", so you need to update that pattern to search the correct names in your file:

    $pattern = [regex]"^(?'item'FirstName|LastName)=(?'data'.+$)"
    
  • #28133
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    Rob is right. I missed the 'Name' portion of the data label.

You must be logged in to reply to this topic.