Forum Discussion

RangerZ's avatar
RangerZ
Copper Contributor
Dec 19, 2023

Copy Files Based on CSV input file (Import-CSV, Copy-Item)

I am admittedly unskilled in Powershell, I hail from the old DOS copy *.* era. I have a number of hours into this but struggling.

 

I have a mapped drive with a deep file explorer hierarchy from which I wish to retrieve PDF files to a local folder, modify in batch and return, overwriting the original.

 

The structure looks similar to this:  Y:\Company\Clients\A-D\[client]\[YYYY]\Folder\*organizer.pdf

YYYY is a year

 

I already have a CSV file which contains the folder path and file name in separate columns.

 

I will have about 500 lines and will expect to perform this task annually. Some manipulation to the CSV is acceptable (concatenate, etc) if this simplifies the process.

 

I have been able to Copy-Item "path\file' -Destination "path\file" for a single line.

 

I seem to be able to import the CSV (Import-CSV) and can (Format-Table) to verify the imported fields, though it's truncated in the display (I hope).

 

I am failing attempting to For-Each my way through the Copy-Item process. I suspect I am missing something in the syntax.

 

 

=========================
Code for FILE and PATH
=========================
#Import the CSV
$CSVData = Import-CSV -Path "D:\Organizer-2023\Organizer2022test2.csv"

#Get the data in Table-like format
$CSVData | Format-Table

# Select specific columns from the pipeline
$SpecificData = $CSVData | Select-Object PATH, FILE

# Display the data in Table format
$SpecificData | Format-Table

# Display the file to the local folder
ForEach ($Row in $CSVData) {
    Copy-Item "($Row.Path)($Row.file)" -Destination "D:\Organizer-2023\($Row.file)"
}

 

 

 

================
RETURNED OUTPUT
================

Copy-Item : Cannot find drive. A drive with the name '(@{File=2022 Individual ALB2047 (Albert, Michael S.)
Organizer.pdf; Path=Y' does not exist.
At line:2 char:5
+     Copy-Item "($Row.Path),($Row.file)" -Destination "D:\Organizer-20 ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: ((@{File=2022 In...zer.pdf; Path=Y:String) [Copy-Item], DriveNotFoundExc
   eption
    + FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.CopyItemCommand

 

Thank you!

Windows PowerShell
  • Harm_Veenstra's avatar
    Harm_Veenstra
    MVP
    Dec 19, 2023

    RangerZ Does this work?

     

    =========================
Code for FILE and PATH
=========================
#Import the CSV
$CSVData = Import-CSV -Path "D:\Organizer-2023\Organizer2022test2.csv"

#Get the data in Table-like format
$CSVData | Format-Table

# Select specific columns from the pipeline
$SpecificData = $CSVData | Select-Object PATH, FILE

# Display the data in Table format
$SpecificData | Format-Table

# Display the file to the local folder
ForEach ($Row in $CSVData) {
    Copy-Item "$($Row.Path)$($Row.file)" -Destination "D:\Organizer-2023\$($Row.file)"
}
    • RangerZ's avatar
      RangerZ
      Copper Contributor
      Dec 20, 2023

      Harm_VeenstraThank you!

       

      The code changes work great! 

       

      It failed the first time, but highlighted an error in the CSV that I did not see with the original code.  My PATH values did not include a trailing "\".  The path just ended in FOLDER as opposed to FOLDER\, so when joined with the file name the path was not valid.  After clean up, I tried it with the old code and it still failed.  With yours I copied about 530 files in under 5 minutes!!  Thank you!!

       

      I have 2 additional questions please. 

       

      As noted above, I will reverse the source and destination to restore the modified file to it's original location.  I think I want to append -Force to the end.  I tested this by rerunning the above with -Force expecting to see the Created Time change to a more current time but did not.  So I am unclear if this is correct or not, and is there an easy way to make the process verbose, for example list the file name's as it runs just so I can see it's doing something real. 

       

      Can you offer a reference or would you speak to the use of the additional $ in the syntax please.

       

      • LainRobertson's avatar
        LainRobertson
        Silver Contributor
        Dec 20, 2023

        RangerZ 

         

        Hey, Steve.

         

        If you're asking about the additional $ in line 18:

         

         

        Copy-Item "$($Row.Path)$($Row.file)" -Destination "D:\Organizer-2023\$($Row.file)"

         

         

        Then it's to symbolise enclosed code.

         

        So, where you had "()", that did not symbolise that the content within the ellipses was to be interpreted as code.

         

        Conversely, "$()" says to PowerShell, "treat the stuff within the ellipses as code".

         

        This is important as if you look at your original format of:

         

        "($Row.Path)"

         

        Then what you get is the value of the $Row object with the string literal of ".Path" appended to it, which is not what you had in mind.

         

        If you use Harm's format of:

         

        "$($Row.Path)"

         

        Then what you get is the value of the $Row.Path property within the $Row object, which is what you had intended.

         

        On another note, if you're looking to make your "Copy" command more resilient to the presence or omission of leading/trailing backslashes, you can use to the built-in Join-Path commandlet. Taking this approach means you do not have to worry so much about the format of your CSV file.

         

         

        Copy-Item -Path (Join-Path -Path $Row.Path -ChildPath $Row.file) -Destination (Join-Path -Path "D:\Organizer-2023\" -ChildPath $Row.file);

         

         

        Join-Path examples

         

         

        Cheers,

        Lain

Resources