Read CSV filepath Contents

This topic contains 8 replies, has 3 voices, and was last updated by Profile photo of Tom Carnahan Tom Carnahan 2 years, 2 months ago.

  • Author
    Posts
  • #19262
    Profile photo of Tom Carnahan
    Tom Carnahan
    Participant

    I am a newbie having difficulties.

    I have a CSV file that I need to read. It has 3 fields. Field1 is a descriptor. Fields 2 and 3 are file paths

    to other files whose content I need to read (with filtering). The CSV file format is as follows:

    Contents of C:\PowerShell_INPUT\FileList.csv:

    Descriptor, RemotePort , FileDigit
    Test0, "\\vavt-neb-pds1\d$\Pds\Test0\ddmscpic.ini", "\\vavt-neb-pds1\d$\Pds\Test0\omniweb.ini"
    Test1, "\\vavt-neb-pds1\d$\Pds\Test1\ddmscpic.ini", "\\vavt-neb-pds1\d$\Pds\Test1\omniweb.ini"
    etc., etc

    I need to read the 3 values of each record in the CSV, retrieve the contents of the remote file paths

    represented by Field_2 and Field_3 (while filtering the content with a search string). Once I have the data , I

    need to write my results to my output text file.

    Here is what I have so far:

    Clear-Host
    $inputFilePath = "C:\PowerShell_INPUT\FileList.csv"
    $outputFilePath = "C:\PowerShell_INPUT\OutputList.txt"
    $csv = Import-csv -path $inputFilePath |

    foreach ($line in $csv)
    {
    Get-Content $line |
    ForEach-Object { $descriptor = $_.Descriptor; $RemotePortFilePath = $_.RemotePort ; $FileDigitFilePath =

    $_.FileDigit |

    $RemotePort = (Get-Content -path $RemotePortFilePath | ?($_ -match "RemotePort=*") ; `
    $FileDigit = (Get-Content -path $FileDigitFilePath) |
    Outfile -path outputFilePath – append
    }

    I am in the Admin group for the server where the files to be read reside, but I keep getting errors like:

    ———————————————
    Get-Content : Cannot find path '\\vavt-neb-pds1\d$\Pds\Application\Test1\omniweb.ini' because it does not exist.
    At line:3 char:1
    + Get-Content $inputFile | ForEach-Object { $_ | where { $_ -match "FileDigit=*" }
    + ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (\\vavt-neb-pds1...st1\omniweb.ini:String) [Get-Content],

    ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand
    ———————————————

    Does anyone see where I am going astray?

    Thanks ahead of time for any help you can provide,

    Tom

  • #19264
    Profile photo of adi dumitras
    adi dumitras
    Participant

    Hi. I spotted a few mistakes in the code.
    Firstly remove the pipeline character in the following line: $csv = Import-csv -path $inputFilePath |

    After that the foreach should be something like this:

    foreach ($line in $csv)
    {
        $descriptor = $line.Descriptor
        $RemotePortFilePath = $line.RemotePort 
        $FileDigitFilePath =$line.FileDigit 
    
        $RemotePort = (Get-Content -path $RemotePortFilePath | ?($_ -match "RemotePort=*")
        $FileDigit = Get-Content -path $FileDigitFilePath 
        add-content -value "$RemotePort $FileDigit" -path $outputFilePath 
    }
    

    I hope it works.

    • #19280
      Profile photo of Tom Carnahan
      Tom Carnahan
      Participant

      Thanks Ali,

      I tried your suggestion but I get the following error:

      Get-Content : Cannot bind argument to parameter 'Path' because it is null.
      
  • #19273
    Profile photo of Simon Wåhlin
    Simon Wåhlin
    Participant

    Hi Tom,

    The "path does not exist" error will probably be resolved by using "FileSystem::"

    Here is an example with a lot of comments:

    $inputFilePath = 'C:\PowerShell_INPUT\FileList.csv'
    $outputFilePath = 'C:\PowerShell_INPUT\OutputList.txt'
    
    # Read first CSV
    $csv = Import-Csv -Path $inputFilePath
    
    foreach ($line in $csv)
    # Do this for each line in the CSV
    {
      # Access a field on the current line using $line.
      $RemotePortFilePath = $line.RemotePort 
      $FileDigitFilePath = $line.FileDigit
    
      # Read all lines from the file path in RemotePort field
      # Using "FileSystem::" to tell PowerShell that the
      # path is accessed using the filesystem provider.
      # Without the FileSystem:: part we risk getting an error stating
      # Cannot find path because it does not exist.
      $RemotePort = Get-Content -Path "FileSystem::$RemotePortFilePath" |
      Where-Object  -FilterScript {
        $_ -match 'RemotePort=*'
      } |
      # Write al matching lines to the output file
      Out-File -Path $outputFilePath -Append
       
      # Read all lines from the file path in FileDigit field
      # and append each one to the output file
      Get-Content -Path "FileSystem::$FileDigitFilePath" | Out-File -Path $outputFilePath -Append
    }
    
    • #19278
      Profile photo of Tom Carnahan
      Tom Carnahan
      Participant

      Simon,

      Thanks for responding to my post.

      I tried your suggestion, however, the following part does not work:

      $RemotePort = Get-Content -Path "FileSystem::$RemotePortFilePath" |
        Where-Object  -FilterScript {
          $_ -match 'RemotePort=*'
        }
      

      The value returned by $RemotePort is the original filepath, [u]not[/u] the content of the file it is pointing to. Please see the attached image.

      Thanks,

      Tom

  • #19292
    Profile photo of Simon Wåhlin
    Simon Wåhlin
    Participant

    Here is a slightly revised version that will output some Verbose messages (blue text)

    Try it and get back to us with the output.

    $inputFilePath = 'C:\PowerShell_INPUT\FileList.csv'
    $outputFilePath = 'C:\PowerShell_INPUT\OutputList.txt'
     
    # Read first CSV
    $csv = Import-Csv -Path $inputFilePath
    
    $VerbosePreference = 'Continue'
    
    foreach ($line in $csv)
    # Do this for each line in the CSV
    {
      # Access a field on the current line using $line.
      $RemotePortFilePath = $line.RemotePort 
      Write-Verbose -Message "RemotePortFilePath set to: $RemotePortFilePath"
      $FileDigitFilePath = $line.FileDigit
      Write-Verbose -Message "FileDigitFilePath set to: $FileDigitFilePath"
     
      # Read all lines from the file path in RemotePort field
      # Using "FileSystem::" to tell PowerShell that the
      # path is accessed using the filesystem provider.
      # Without the FileSystem:: part we risk getting an error stating
      # Cannot find path because it does not exist.
      # At last, write all matching lines to the output file
      Write-Verbose "Retrieving content from path: FileSystem::$RemotePortFilePath"
      Get-Content -Path "FileSystem::$RemotePortFilePath" -ErrorAction Stop |
      Where-Object  -FilterScript {
        $_ -match 'RemotePort=*'
      } | Out-File -FilePath $outputFilePath -Append
    
      # Write a row of '*' to the outputfile
      ('*'*50) | Out-File -FilePath $outputFilePath -Append
    
      # Read all lines from the file path in FileDigit field
      # and append each one to the output file
      Write-Verbose "Retrieving content from path: FileSystem::$FileDigitFilePath"
      Get-Content -Path "FileSystem::$FileDigitFilePath" -ErrorAction Stop | Out-File -FilePath $outputFilePath -Append
    }
    
    • #19350
      Profile photo of Tom Carnahan
      Tom Carnahan
      Participant

      Hi Simon,

      I tried the revised script and did not have any success. Here is where I think the problem is:

      $RemotePortFilePath = $line.RemotePort
      Write-Verbose -Message "RemotePortFilePath set to: $RemotePortFilePath"
      

      The output of the message is:

      VERBOSE:  RemotePortFilePath set to:  
      

      The error message is:

      Get_Content : Cannot find Path '' because it does not exist.
      

      For some reason, it is not picking up the field value in the line "$RemotePortFilePath = $line.RemotePort"

      The .CSV field value it was supposed to read was: "D:\Pds\Application\Test1\ddmscpic.ini"

      After the first error, I changed that field value to: "\\vavt-neb-pds1\d$\Pds\Application\Test0\ddmscpic.ini"
      but got the same results.

  • #19351
    Profile photo of adi dumitras
    adi dumitras
    Participant

    Try changing the 2 lines that get the remoteport file path and the file digit file path to:

    $RemotePortFilePath ="$($line.RemotePort)" 
    
    $FileDigitFilePath = "$($line.FileDigit)"
    
    

    This should make absolutely sure that you will get a string in the 2 variables and if the $line data is correct you shuold get the correct strings.

  • #19364
    Profile photo of Tom Carnahan
    Tom Carnahan
    Participant

    Mystery solved ... the headers in the CSV file did not match the values being called by the code.

    In Simon's script

    $RemotePortFilePath = $line.RemotePort
    

    I did not have a header named "RemotePort" ... it was named something else.

    Thanks Simon and Adi ... you were a great help!

You must be logged in to reply to this topic.