Read CSV filepath Contents

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

  • Author
    Posts
  • #19262

    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

    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

      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

    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

      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

    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

      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

    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

    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.